SELECT句のサブクエリ
一覧の隣に、あと 1 列だけ足したい
社員一覧を出しながら、その横に「社内で一番高い年収」も並べておきたい、という注文が来たとします。結合する相手のテーブルはありません。欲しいのは 1 つの値だけで、それを全行に付けたいだけです。
こういうときは、SELECT に並べる列の位置に、そのまま丸括弧の SELECT を置けます。
SQL クエリ
SELECT
name,
salary,
(SELECT MAX(salary) FROM employees) AS top_salary
FROM employees
ORDER BY salary DESC;top_salary の列には、全行に同じ値が入ります。ここに置けるのは 1 行 1 列だけの値で、これをスカラー値と呼びます。AS で名前を付けるのを忘れると、列見出しがサブクエリの文面そのものになって読めなくなります。
行ごとに違う値を入れる
丸括弧の中から、外側の行を参照することもできます。こうすると、行ごとに違う値が入ります。
SQL クエリ
SELECT
e.name,
(SELECT COUNT(*) FROM trainings t WHERE t.employee_id = e.id) AS training_count
FROM employees e
ORDER BY e.id;t.employee_id = e.id の部分が、今処理している外側の 1 行と内側を結び付けています。これが相関サブクエリです。外側の行の数だけ内側が動くので、数万行を返すクエリでは LEFT JOIN と GROUP BY に書き換えたほうが速いことが多い、とだけ覚えておいてください。
2 行返った瞬間に落ちる
スカラーサブクエリは、名前のとおり 1 行 1 列しか返せません。条件の書き方によって 2 行以上返る可能性があると、その行に当たった瞬間にエラーで止まります。
SQL クエリ
-- 落ちる 部署に 2 人以上いれば 2 行返ってしまう
SELECT
d.name,
(SELECT e.name FROM employees e WHERE e.dept_id = d.id) AS member
FROM departments d;
-- 通る 集計で 1 行に畳んでから返す
SELECT
d.name,
(SELECT MAX(e.salary) FROM employees e WHERE e.dept_id = d.id) AS top_salary
FROM departments d;厄介なのは、データが少ないうちは通ってしまうことです。「開発中は動いていたのに、本番でだけ落ちる」の典型がこれなので、書いた時点で「この内側は絶対に 1 行か」を確かめてください。1 行だと言い切れないなら、MAX や SUM などの集計関数で畳むか、LIMIT 1 で明示的に 1 行にします。
テーブル構造
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, '高橋');
INSERT INTO orders VALUES
(101, 1, 3000),
(102, 1, 1500),
(103, 2, 8000),
(104, 4, 5000),
(105, 4, 2500);期待される出力
| id | name | total_amount |
|---|---|---|
| 1 | 佐藤 | 4500 |
| 2 | 鈴木 | 8000 |
| 3 | 田中 | 0 |
| 4 | 高橋 | 7500 |