SQL JOIN vs IN performance?

Question

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: