SELECT句のサブクエリ

一覧の隣に、あと 1 列だけ足したい

社員一覧を出しながら、その横に「社内で一番高い年収」も並べておきたい、という注文が来たとします。結合する相手のテーブルはありません。欲しいのは 1 つの値だけで、それを全行に付けたいだけです。

こういうときは、SELECT に並べる列の位置に、そのまま丸括弧の SELECT を置けます。

SELECT
  name,
  salary,
  (SELECT MAX(salary) FROM employees) AS top_salary
FROM employees
ORDER BY salary DESC;

top_salary の列には、全行に同じ値が入ります。ここに置けるのは 1 行 1 列だけの値で、これをスカラー値と呼びます。AS で名前を付けるのを忘れると、列見出しがサブクエリの文面そのものになって読めなくなります。

行ごとに違う値を入れる

丸括弧の中から、外側の行を参照することもできます。こうすると、行ごとに違う値が入ります。

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 行以上返る可能性があると、その行に当たった瞬間にエラーで止まります。

-- 落ちる  部署に 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 行にします。