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`**][1]
[1]:
[To see links please register here]