IN vs. JOIN with large rowsets

by

Update:

This article in my blog summarizes both my answer and my comments to another answers, and shows actual execution plans:

SELECT  *
FROM    a
WHERE   a.c IN (SELECT d FROM b)

SELECT  a.*
FROM    a
JOIN    b
ON      a.c = b.d

These queries are not equivalent. They can yield different results if your table b is not key preserved (i. e. the values of b.d are not unique).

The equivalent of the first query is the following:

SELECT  a.*
FROM    a
JOIN    (
        SELECT  DISTINCT d
        FROM    b
        ) bo
ON      a.c = bo.d

If b.d is UNIQUE and marked as such (with a UNIQUE INDEX or UNIQUE CONSTRAINT), then these queries are identical and most probably will use identical plans, since SQL Server is smart enough to take this into account.

SQL Server can employ one of the following methods to run this query:

  • If there is an index on a.c, d is UNIQUE and b is relatively small compared to a, then the condition is propagated into the subquery and the plain INNER JOIN is used (with b leading)

  • If there is an index on b.d and d is not UNIQUE, then the condition is also propagated and LEFT SEMI JOIN is used. It can also be used for the condition above.

  • If there is an index on both b.d and a.c and they are large, then MERGE SEMI JOIN is used

  • If there is no index on any table, then a hash table is built on b and HASH SEMI JOIN is used.

Neither of these methods reevaluates the whole subquery each time.

See this entry in my blog for more detail on how this works:

There are links for all RDBMS‘s of the big four.

More Related Contents:

  1. SQL JOIN: is there a difference between USING, ON or WHERE?
  2. Subqueries vs joins
  3. Difference between Oracle’s plus (+) notation and ansi JOIN notation?
  4. SQL Joins Vs SQL Subqueries (Performance)?
  5. Is a JOIN faster than a WHERE?
  6. What’s the best to check if item exist or not: Select Count(ID)OR Exist(…)?
  7. Can an INNER JOIN offer better performance than EXISTS
  8. How to check which locks are held on a table
  9. When to use EXCEPT as opposed to NOT EXISTS in Transact SQL?
  10. sql query joins multiple tables – too slow (8 tables)
  11. Count(*) vs Count(1) – SQL Server
  12. SQL update from one Table to another based on a ID match
  13. SQL JOIN vs IN performance?
  14. Is a view faster than a simple query?
  15. SQL left join vs multiple tables on FROM line?
  16. Can we use join for two different database tables?
  17. Drop all the tables, stored procedures, triggers, constraints and all the dependencies in one sql statement
  18. Insert Picture into SQL Server 2005 Image Field using only SQL
  19. Convert varchar to uniqueidentifier in SQL Server
  20. Execute Stored Procedure from a Function
  21. How to count occurrences of a column value efficiently in SQL?
  22. MySQL – Selecting data from multiple tables all with same structure but different data
  23. Paging SQL Server 2005 Results
  24. How to compare two dates to find time difference in SQL Server 2005, date manipulation
  25. Restrict an SQL Server connection to a specific IP address
  26. SQL query: Simulating an “AND” over several rows instead of sub-querying
  27. SQL Server 2008 paging methods?
  28. SQL SELECT from multiple tables
  29. Join a count query on generate_series() and retrieve Null values as ‘0’
  30. SQL Query for Parent Child Relationship

Leave a Comment