SQL JOIN vs IN performance

Question

I have a case where using a JOIN or an IN will give me the correct results     Which typically has better performance and why   How much does it depend on what database server you are running    FYI I am using MSSQL

User · Answer

This Thread is pretty old but still mentioned often. For my personal taste it is a bit incomplete, because there is another way to ask the database with the EXISTS keyword which I found to be faster more often than not.

So if you are only interested in values from table a you can use this query:

SELECT  a.*
FROM    a
WHERE   EXISTS (
    SELECT  *
    FROM    b
    WHERE   b.col = a.col
    )

The difference might be huge if col is not indexed, because the db does not have to find all records in b which have the same value in col, it only has to find the very first one. If there is no index on b.col and a lot of records in b a table scan might be the consequence. With IN or a JOIN this would be a full table scan, with EXISTS this would be only a partial table scan (until the first matching record is found).

If there a lots of records in b which have the same col value you will also waste a lot of memory for reading all these records into a temporary space just to find that your condition is satisfied. With exists this can be usually avoided.

I have often found EXISTS faster then IN even if there is an index. It depends on the database system (the optimizer), the data and last not least on the type of index which is used.

User · Answer

Each database s implementation but you can probably guess that they all solve common problems in more or less the same way  If you are using MSSQL have a look at the execution plan that is generated  You can do this by turning on the profiler and executions plans  This will give you a text version when you run the command   I am not sure what version of MSSQL you are using but you can get a graphical one in SQL Server 2000 in the query analyzer  I am sure that this functionality is lurking some where in SQL Server Studio Manager in later versions    Have a look at the exeuction plan  As far as possible avoid table scans unless of course your table is small in which case a table scan is faster than using an index  Read up on the different join operations that each different scenario produces

User · Answer

Funny you mention that  I did a blog post on this very subject  See Oracle vs MySQL vs SQL Server  Aggregation vs Joins Short answer  you have to test it and individual databases vary a lot

User · Answer

The optimizer should be smart enough to give you the same result either way for normal queries   Check the execution plan and they should give you the same thing   If they don t  I would normally consider the JOIN to be faster   All systems are different  though  so you should profile the code on your system to be sure

User · Answer

That s rather hard to say - in order to really find out which one works better  you d need to actually profile the execution times   As a general rule of thumb  I think if you have indices on your foreign key columns  and if you re using only  or mostly  INNER JOIN conditions  then the JOIN will be slightly faster   But as soon as you start using OUTER JOIN  or if you re lacking foreign key indexes  the IN might be quicker   Marc

User · Answer

A interesting writeup on the logical differences  SQL Server  JOIN vs IN vs EXISTS - the logical difference  I am pretty sure that assuming that the relations and indexes are maintained a Join will perform better overall  more effort goes into working with that operation then others   If you think about it conceptually then its the difference between 2 queries and 1 query   You need to hook it up to the Query Analyzer and try it and see the difference  Also look at the Query Execution Plan and try to minimize steps

User · Answer

Generally speaking  IN and JOIN are different queries that can yield different results   SELECT  a   FROM    a JOIN    b ON      a col   b col   is not the same as  SELECT  a   FROM    a WHERE   col IN                   SELECT  col         FROM    b               unless b col is unique   However  this is the synonym for the first query   SELECT  a   FROM    a JOIN              SELECT  DISTINCT col         FROM    b           ON      b col   a col   If the joining column is UNIQUE and marked as such  both these queries yield the same plan in SQL Server   If it s not  then IN is faster than JOIN on DISTINCT   See this article in my blog for performance details    IN vs  JOIN vs  EXISTS

[sql] SQL JOIN vs IN performance?

Examples related to sql

Examples related to sql-server

Examples related to performance

Examples related to tsql