WHERE句のサブクエリ
該当する ID を、手で並べている
employees (社員) と trainings (研修の受講記録) の 2 つのテーブルがあるとします。「研修を修了した社員だけ」を名簿から出したい。まず受講記録から社員 ID を調べます。
SQL クエリ
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 の丸括弧の中には、値を並べる代わりにサブクエリを置けます。
SQL クエリ
SELECT id, name
FROM employees
WHERE id IN (
SELECT employee_id
FROM trainings
WHERE completed_at IS NOT NULL
)
ORDER BY name;内側が「修了者の社員 ID の一覧」を作り、外側がその一覧に含まれる社員だけを返します。受講記録が増えても減っても、このクエリは直さなくて済みます。
何行返るかで、演算子が決まる
サブクエリを WHERE のどこに書くかより先に、「内側は何行返すのか」を数えてください。ここを間違えると必ず落ちます。
SQL クエリ
-- 内側が 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 を使うと、思わぬ結果になることがあります。
SQL クエリ
-- 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 に置き換えます。
SQL クエリ
SELECT id, name
FROM employees e
WHERE NOT EXISTS (
SELECT 1 FROM trainings t WHERE t.employee_id = e.id
);EXISTS は「内側が 1 行でも返るか」だけを見る書き方で、内側から外側の行 (e.id) を参照しています。こうした書き方を相関サブクエリと呼びます。
テーブル構造
CREATE TABLE customers (
id INT PRIMARY KEY,
name VARCHAR(40)
);
CREATE TABLE orders (
id INT PRIMARY KEY,
customer_id INT,
amount INT
);
INSERT INTO customers VALUES
(1, '佐藤'),
(2, '鈴木'),
(3, '田中'),
(4, '高橋'),
(5, '伊藤');
INSERT INTO orders VALUES
(101, 1, 3000),
(102, 1, 1500),
(103, 2, 8000),
(104, 4, 5000);期待される出力
| id | name |
|---|---|
| 1 | 佐藤 |
| 2 | 鈴木 |
| 4 | 高橋 |