SQL練習室
← SQLガイド

SQLのサブクエリとは？相関・IN・EXISTS・NOT EXISTSの使い分け

サブクエリは、SQL文の中に入れる別のSELECT文です。全体平均との比較や、関連する注文の有無を表せます。「1つの値」「値の一覧」「表」のどれを返すかを見分けると、置く場所と使う演算子を選びやすくなります。

サブクエリの基本構文

sql 
SELECT *
FROM employees
WHERE salary > (
  SELECT AVG(salary)
  FROM employees
);

スカラーサブクエリ：1行1列を比較値にする

平均給与を1つの値として求め、各社員の給与と比べます。スカラーサブクエリは1列かつ最大1行が条件です。0行ならNULL、2行以上ならエラーです。GROUP BYなしのAVGは1行になるため、この比較に使えます。

sql 
SELECT *
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

INのサブクエリ：1列の候補一覧と比較する

INはサブクエリが返す候補に外側の値が含まれるかを判定します。複数行返ってもよく、同じ顧客IDが何度現れても外側の顧客行が増えることはありません。JOINで明細列を付ける場合との違いです。

sql 
SELECT *
FROM customers
WHERE id IN (
  SELECT customer_id
  FROM orders
);

FROM句のサブクエリ：集計した表をさらに検索する

FROMに置くと、サブクエリの結果を表として扱えます。部署別人数を作ってから人数を条件にする例です。この処理はHAVINGでも書けますが、複数段階の計算や出力別名を条件に使いたいときに便利です。表に別名を付けると参照先が明確になります。

sql 
SELECT *
FROM (
  SELECT department, COUNT(*) AS people
  FROM employees
  GROUP BY department
) totals
WHERE people >= 2;

相関サブクエリ：自分の部署の平均と比較する

内側で外側のe.departmentを参照すると、部署ごとの基準値になります。全社平均との比較とは結果が違います。内側のteamと外側のeを明示し、team.department = team.departmentという自己比較にしないよう注意します。

sql 
SELECT *
FROM employees e
WHERE salary > (
  SELECT AVG(team.salary)
  FROM employees team
  WHERE team.department = e.department
);

EXISTS：関連する行が1件でもあるかを調べる

EXISTSは内側が1行以上返せばtrueです。注文のある顧客を一覧にするような存在確認に向き、注文の内容を結果へ付けるものではありません。顧客と注文を結ぶ相関条件を忘れると、全顧客に対して同じ存在判定をしてしまいます。

sql 
SELECT *
FROM customers c
WHERE EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.id
);

NOT EXISTS：関連する行がないものを探す

NOT EXISTSはサブクエリが0行のときtrueです。未注文顧客を探すなら、注文側を顧客IDで関連付けてから否定します。注文側のcustomer_idにNULLがあっても、それだけで他の未注文顧客が消えることはありません。

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

サブクエリでよくある間違い

NOT INの候補にNULLが混ざる

候補にNULLがあると、一致する値がない場合でもNOT INがNULLになり、WHEREを通りません。関連行の不在を判定したいならNOT EXISTSが意図を表しやすくなります。外側の値もNULLの場合、両者は同じ意味とは限らないので、NULLを含める要件は別に決めましょう。

sql 
-- trueではなくNULLになる
SELECT 3 NOT IN (1, 2, NULL);

実際に1問解いてみる

スカラーサブクエリ

全社員の平均給与を超える社員を取得してください。行順は問いません。

テーブル

読み込み中…

実行結果
サブクエリの本格演習へ →

関連構文

HAVINGINNER JOINウィンドウ関数

サブクエリについてよくある質問

サブクエリとJOINはどちらが速いですか？
構文だけでは決まりません。データ量、索引、条件、実行計画によって変わります。関連列が必要ならJOIN、存在だけならEXISTSのように意図を先に明確にし、EXPLAINで計画を確認します。
相関サブクエリは必ず1行ずつ実行されますか？
外側の行ごとに考えると意味を理解しやすいですが、実際の実行方法はプランナーが決めます。構文の見た目だけで実行回数や遅さを断定しないようにします。
サブクエリの結果が複数行だとエラーになるのはなぜですか？
=などの比較の右側をスカラー値として使っている可能性があります。1値に集約するのか、INで一覧として比較するのか、意図に合わせて選びます。

参考文献

内容はPostgreSQL公式ドキュメントを参照し、初めて学ぶ人向けに日本語で整理しています。