【SQL】相関サブクエリの仕組みと使いどころ｜非相関サブクエリとの違いを理解する

SQL
2021.12.22

こんにちは、かつコーチです。

前回の記事「サブクエリの基本」では、外側のクエリと独立して実行できる非相関サブクエリを扱いました。

今回は、外側のクエリの各行を参照しながら繰り返し実行される相関サブクエリ（correlated subquery）を扱います。

EXISTSやNOT EXISTSと組み合わせた実践的な書き方、そしてJOIN・非相関サブクエリとのパフォーマンス比較まで、実務で判断に迷いやすいポイントを一次情報ベースで整理します。

相関サブクエリの仕組み

外側のクエリの行を参照して繰り返し実行される

相関サブクエリの最大の特徴は、内側のサブクエリが外側のクエリの列を参照する点です。

SELECT c.id, c.name
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.id
      AND o.total_amount >= 5000
);

このクエリのサブクエリ部分（SELECT 1 FROM orders o WHERE o.customer_id = c.id ...）は、customersテーブルの外側1行が処理されるたびに、c.idの値を受け取って毎回実行されます。

非相関サブクエリが「先に1回だけ計算されて値やリストに置き換わる」のに対し、相関サブクエリは「外側の行数ぶんだけ繰り返し評価される」という実行モデルの違いを押さえておくことが、この後のパフォーマンス判断の前提になります。

相関サブクエリでしか表現しにくいロジック

相関サブクエリが真価を発揮するのは、「グループごとの最大値を持つ行」や「他のテーブルに1件も存在しない行」を抽出したいときです。

たとえば「顧客ごとの最新注文だけを取得したい」というクエリは、相関サブクエリなら次のように書けます。

SELECT o.*
FROM orders o
WHERE o.order_date = (
    SELECT MAX(o2.order_date)
    FROM orders o2
    WHERE o2.customer_id = o.customer_id
);

内側のサブクエリは「同じ顧客の注文の中での最新日付」を、外側の行ごとに動的に計算しています。

GROUP BYとHAVINGだけでこの要件を満たそうとすると、いったん最新日付のテーブルを作ってから再度JOINするなど、遠回りな書き方になりがちです。

EXISTS・NOT EXISTSとの実践的な組み合わせ

「1件でも存在するか」を判定するEXISTS

EXISTSは相関サブクエリと組み合わせて使われることがほとんどです。

サブクエリの中身（SELECTでどの列を取るか）は評価に影響しないため、慣習的にSELECT 1と書きます。

「注文が1件以上ある顧客」を取得する先ほどの例が典型パターンです。

「1件も存在しないか」を判定するNOT EXISTS

反対に「注文が1件もない顧客」を抽出したい場合はNOT EXISTSを使います。

SELECT c.id, c.name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

同じ要件はNOT INでも書けますが、NOT INにはNULLが絡むと結果が空になるという既知の落とし穴があります。

❌ Before：orders.customer_idにNULLが1件でも含まれる状態でNOT INを使う

SELECT c.id, c.name
FROM customers c
WHERE c.id NOT IN (SELECT customer_id FROM orders);

orders.customer_idにNULLの行が1件でも存在すると、NOT INの比較結果がUNKNOWN扱いになり、このクエリは1行も返さなくなります。

私が実際にこのバグを踏んだのは、外部システムからの一括インポートでcustomer_idがNULLの不正データが数件混入していたケースでした。

「対象顧客が0件です」というバグ報告を受けて原因を追うのに数時間かかり、原因がNOT INとNULLの組み合わせだと分かったときは拍子抜けしたのを覚えています。

✅ After：NULLの影響を受けないNOT EXISTSに置き換える

SELECT c.id, c.name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

NOT EXISTSは行ごとの存在判定であり、NULLの混入による全件消失というリスクがありません。

「存在しないことを確認したい」ケースでは、NOT INよりNOT EXISTSを優先するのがベストプラクティスです。

パフォーマンス比較：相関サブクエリ・JOIN・非相関サブクエリ

実行計画で見る違い

相関サブクエリは「外側の行数 × 内側のサブクエリの実行コスト」に近いコストがかかりやすく、外側のテーブルが大きくなるほど不利になりがちです。

一方、LEFT JOINとNULL判定を使えば、同じ「注文がない顧客」の抽出をJOINベースで書き換えられます。

SELECT c.id, c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

MySQLのオプティマイザは、相関サブクエリを内部的にセミジョイン（semi-join）へ書き換える最適化を行うことがありますが、これはMySQLのバージョンやクエリの形によって挙動が変わります。

「相関サブクエリだから遅い」と決めつけるのではなく、EXPLAINで実行計画を確認した上で判断するのが実務的な姿勢です。

実行計画の読み方は、この後のシリーズ「EXPLAINで実行計画を読む基本」で扱います。

使い分けの判断軸

観点相関サブクエリ（EXISTS/NOT EXISTS）JOIN非相関サブクエリ
得意な処理存在判定・行ごとの動的な比較複数テーブルの列を横に並べたい場合固定の基準値・条件リストを先に計算したい場合
NULLの安全性高い（NOT EXISTSはNULLに影響されない）JOIN列にNULLがあると結合漏れに注意INの場合はNULL混入でNOT INが破綻するリスクあり
可読性存在判定の意図が明確結合条件が明示的で追いやすい段階的なロジックとして読みやすい
大規模データでの傾向インデックスが効けば実用的、効かないと重くなりやすい一般的に最適化されやすい実行回数が少なければ有利

「不存在チェックが目的ならNOT EXISTS」「複数テーブルの列を一覧化したいならJOIN」「先に基準値を1回だけ計算したいなら非相関サブクエリ」という基準で選べば、まず大きく外すことはありません。

まとめ

この記事のポイント

  • 相関サブクエリは外側のクエリの各行を参照しながら繰り返し評価される
  • 「グループごとの最新行」や「存在判定」など、非相関サブクエリだけでは書きにくいロジックに向いている
  • NOT INはNULL混入で結果が全件消える危険があるため、存在しないことの判定にはNOT EXISTSを使う
  • 相関サブクエリ・JOIN・非相関サブクエリはそれぞれ得意分野が異なり、EXPLAINで実行計画を確認しながら選ぶのが実務的

次に読むべき記事

グループ化・サブクエリの範囲を一通り学んだら、次は「INSERT文でデータを追加する」からデータ操作（DML）の分野に進んでください。

タグ: SQL, 上級者向け, サブクエリ

タイトルとURLをコピーしました