in＋サブクエリ、exists＋サブクエリ、cte＋JOINを比較

共通シナリオ

orders テーブルに注文記録が一つでもある users の情報を取得する。
ordersテーブルから「注文したユーザーIDのリスト」を先に作成し、usersテーブル側で「そのリストに含まれているユーザー」を絞り込む。

in＋サブクエリ

SELECT
    u.user_id,
    u.name
FROM
    users u
WHERE
    u.user_id IN (
        SELECT o.user_id FROM orders o
        WHERE o.user_id = u.user_id
    );

exists＋サブクエリ

SELECT
    u.user_id,
    u.name
FROM
    users u
WHERE
    EXISTS (
        SELECT 1  -- 存在チェックが目的なので、'1' や '*' など何でも良い
        FROM orders o
        WHERE o.user_id = u.user_id
    );

with＋join

WITH OrderedUserIDs AS (
    SELECT DISTINCT user_id
    FROM orders
)
SELECT
    u.user_id,
    u.name
FROM
    users u
INNER JOIN OrderedUserIDs o_ids 
ON u.user_id = o_ids.user_id;

結局、どれを使うべきか？

参考資料

IN, EXISTS, JOIN のうち、絶対的に常に速いクエリはない。
データベースのオプティマイザ(Optimizer)が、テーブルのサイズ、データ分布、インデックスの有無を考慮し、内部的に最も効率的な実行計画(実行計画)を選択するからだ。

なので一応、

クエリの実行計画を確認する。

ことが必須だ。
しかし、大量のデータを扱う際の一般的な傾向と、各構文の明確な用途は存在する。

case1：存在有無だけを確認すればよい場合、EXISTSを採用。

EXISTSは、条件を満たす最初の行を見つけた直後にサブクエリの実行を「停止」する。
一方、INやCTEはサブクエリのすべての結果セットを作成する必要がある。
usersが100万件、ordersが1億件であっても、orders.user_idにインデックスがあればEXISTSは非常に高速に動作する。

case2：INやEXISTSを何度もネストして使用する場合なら、with＋joinに

inの中にinがあったりする複雑なクエリ（例：「AリストにもBリストにも存在するユーザーの照会」）では、WITH句（CTE: Common Table Expressions）の真価が発揮される。
例えば、
　1. 'Electronics' カテゴリの商品IDを探し (一番内側のIN)
　2. 1の商品IDに紐づく注文をした user_id を探し (二番目のIN)
　3. 2の user_id に一致する users を取得する

SELECT
    u.user_id,
    u.name
FROM
    users u
WHERE
    u.user_id IN (
        SELECT o.user_id
        FROM orders o
        WHERE o.product_id IN (
            SELECT p.product_id
            FROM products p
            WHERE p.category_name = 'Electronics'
        )
    );

ネストされたINクエリは、内側から読み解く必要があり、3〜4階層ネストするだけでも、人間の脳で解釈するのは非常に困難である!!

WITH TargetProducts AS (
    SELECT p.product_id
    FROM products p
    WHERE p.category_name = 'Electronics'
),
TargetUserIDs AS (
    SELECT DISTINCT o.user_id
    FROM orders o
    JOIN TargetProducts tp ON o.product_id = tp.product_id
)
SELECT
    u.user_id,
    u.name
FROM
    users u
JOIN
    TargetUserIDs tu ON u.user_id = tu.user_id;

変更後のクエリは可読性もいいし、
常にそうとは限らないが、（必ず、直接実行計画を読むこと）サブクエリのデータ量が非常に多い場合、ネストしたINよりもWITH句 (CTE) を使った方が、はるかに速いことが多い。

case3：サブクエリの結果が数十〜数百件程度と小さいことが確実であれば、IN

IN (1, 2, 3)のように比較するリストが固定されているか、サブクエリの結果が数十〜数百件程度と非常に小さいことが確実であれば、INは高速。

結論

上記以外にもサブクエリを再活用する場合とか再帰クエリの場合とかCTEが良いなど、いろんなケースがあるので、クエリの実行計画を確認することが一番だ。