WHERE句のサブクエリ

該当する ID を、手で並べている

employees (社員) と trainings (研修の受講記録) の 2 つのテーブルがあるとします。「研修を修了した社員だけ」を名簿から出したい。まず受講記録から社員 ID を調べます。

SELECT DISTINCT employee_id
FROM trainings
WHERE completed_at IS NOT NULL;
-- 3, 7, 12, 18

SELECT id, name FROM employees WHERE id IN (3, 7, 12, 18);

動きますが、この (3, 7, 12, 18) は受講者が 1 人増えるたびに書き直しになります。しかも、書き直し忘れても SQL はエラーを出しません。黙って古い名簿を返すだけです。

括弧の中で、条件になる集合を作る

IN の丸括弧の中には、値を並べる代わりにサブクエリを置けます。

SELECT id, name
FROM employees
WHERE id IN (
  SELECT employee_id
  FROM trainings
  WHERE completed_at IS NOT NULL
)
ORDER BY name;

内側が「修了者の社員 ID の一覧」を作り、外側がその一覧に含まれる社員だけを返します。受講記録が増えても減っても、このクエリは直さなくて済みます。

何行返るかで、演算子が決まる

サブクエリを WHERE のどこに書くかより先に、「内側は何行返すのか」を数えてください。ここを間違えると必ず落ちます。

-- 内側が 1 行だけ返る  比較演算子が使える
WHERE dept_id = (SELECT id FROM departments WHERE name = '営業')

-- 内側が複数行返る  IN を使う
WHERE dept_id IN (SELECT id FROM departments WHERE floor = 5)

複数行返るサブクエリに = を使うと、実行した瞬間に more than one row returned by a subquery used as an expression で止まります。逆に言えば、このエラーが出たときは「内側が思ったより多く返っている」というサインです。

NOT IN は、NULL が 1 つ混じると全滅する

「研修を 1 度も受けていない社員」を出そうとして NOT IN を使うと、思わぬ結果になることがあります。

-- trainings.employee_id に NULL が 1 行でもあると、0 件になる
SELECT id, name
FROM employees
WHERE id NOT IN (SELECT employee_id FROM trainings);

NULL との比較は真でも偽でもなく「不明」になります。すると「どれとも一致しない」と言い切れる行が 1 つも無くなり、全行が条件から外れます。相手の列が NULL を許しているなら、NOT EXISTS に置き換えます。

SELECT id, name
FROM employees e
WHERE NOT EXISTS (
  SELECT 1 FROM trainings t WHERE t.employee_id = e.id
);

EXISTS は「内側が 1 行でも返るか」だけを見る書き方で、内側から外側の行 (e.id) を参照しています。こうした書き方を相関サブクエリと呼びます。