JOIN vs. in

もし、結合のカラムがuniqueならば JOIN(①)とIN(②)は同じ結果になる

SELECT  a.*
FROM    a
JOIN    b
ON      a.col = b.col

SELECT  a.*
FROM    a
WHERE   col IN
        (
        SELECT  col
        FROM    b
        )

uniqueでなくても、JOINの時にditrictすると(③)、inと同じ意味。

SELECT  a.*
FROM    a
JOIN    (
        SELECT  DISTINCT col
        FROM    b
        )
ON      b.col = a.col

パフォーマンスは in > join on ditinct

REF

  • https://stackoverflow.com/questions/1200295/sql-join-vs-in-performance