SQL JOIN vs IN performance?

by

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:

More Related Contents:

  1. How do I obtain a Query Execution Plan in SQL Server?
  2. When should I use CROSS APPLY over INNER JOIN?
  3. Multiple INSERT statements vs. single INSERT with multiple VALUES
  4. Counting DISTINCT over multiple columns
  5. Can an INNER JOIN offer better performance than EXISTS
  6. When using GETDATE() in many places, is it better to use a variable?
  7. When to use EXCEPT as opposed to NOT EXISTS in Transact SQL?
  8. Is it possible to write single query on two table which are not connected to each other?
  9. How to create a specific SQL Server stored procedure? [closed]
  10. is there an advantage to varchar(500) over varchar(8000)?
  11. T-SQL: Deleting all duplicate rows but keeping one [duplicate]
  12. Selecting COUNT(*) with DISTINCT
  13. SQL: IF clause within WHERE clause
  14. Where value in column containing comma delimited values
  15. What is the difference between using a cross join and putting a comma between the two tables?
  16. Bulk DELETE on SQL Server 2008 (Is there anything like Bulk Copy (bcp) for delete data?)
  17. Pivot using SQL Server 2000
  18. Equivalent of Oracle’s RowID in SQL Server
  19. Sequence vs identity
  20. How to update and order by using ms sql
  21. How to store image in SQL Server database tables column [duplicate]
  22. SQL “between” not inclusive
  23. SQL performance: WHERE vs WHERE(ROW_NUMBER)
  24. Can I set ignore_dup_key on for a primary key?
  25. SQL Server replace, remove all after certain character
  26. How to find column names for all tables in all databases in SQL Server
  27. Inserting rows into a table with one IDENTITY column only [duplicate]
  28. SQL Row_Number() function in Where Clause
  29. Why is RAND() not producing random numbers?
  30. Should every SQL Server foreign key have a matching index? [closed]

Leave a Comment