FROM句のサブクエリ

集計してできた列は、WHERE では使えない

部署ごとの人数を数えて、そのうち 10 人以上の部署だけを出したいとします。素直に書くと、こうなります。

SELECT dept, COUNT(*) AS headcount
FROM employees
WHERE headcount >= 10   -- ここで止まる
GROUP BY dept;

column "headcount" does not exist というエラーで落ちます。WHERE は 1 行ずつを見て残すか捨てるかを決める段階で、まだグループにまとまっていません。headcount という列は、この時点ではどこにも存在しないのです。

集計した結果を、テーブルとして置き直す

集計が終わった後の状態で絞りたいなら、集計するクエリを丸括弧で囲んで、FROM に置きます。

SELECT dept, headcount
FROM (
  SELECT dept, COUNT(*) AS headcount
  FROM employees
  GROUP BY dept
) AS d
WHERE headcount >= 10
ORDER BY headcount DESC;

外側から見ると、丸括弧の中身は dept と headcount の 2 列を持つ普通のテーブルです。実在するテーブルと同じように WHERE で絞れますし、ORDER BY で並べ替えもできます。この置き方を派生テーブル (derived table) と呼びます。

読み方のコツは、内側から外へ順に見ることです。「まず部署ごとに数える」「できた表から 10 人以上を残す」という 2 段階が、そのまま入れ子の形になっています。

同じ絞り込みは HAVING でも書けます。1 段で終わるなら HAVING のほうが短く、集計した結果をさらに別の計算に使ったり、順位を付けてから絞ったりするなら FROM に置くほうが手順が見えます。

別名を付け忘れると、止まる DB がある

FROM に置いたサブクエリには、別名を付けるのが基本です。MySQL や PostgreSQL 15 以前では別名が必須で、付けないとエラーになります。PostgreSQL 16 以降（このコースの演習環境）では省略できますが、ほかの DB に移しても動くよう、付ける書き方をおすすめします。

-- MySQL や PostgreSQL 15 以前では落ちる（PostgreSQL 15 以前のエラー文言は subquery in FROM must have an alias）。PostgreSQL 16 以降は通る
SELECT dept FROM (SELECT dept FROM employees GROUP BY dept);

-- 別名を付ければ通る
SELECT dept FROM (SELECT dept FROM employees GROUP BY dept) AS d;

外側から「どの表の列か」を指せないと困るので、古い版や MySQL では名前が必須になっています。PostgreSQL 16 以降は省略できますが、d.dept のように別名で列を指す書き方は、サブクエリが増えて同じ列名が混ざったときに役立ちます。付け忘れてエラーになったときは、メッセージにそのまま alias と出るので、見たらすぐ気付けます。

もう 1 つ、内側で付けた別名 (COUNT(*) AS headcount の headcount) は、外側のクエリでは列名として使えます。一方、同じ丸括弧の中の WHERE でその名前を参照しようとしても、SELECT 句の別名は WHERE からは見えないので、見つかりません。

平均の表示桁数をそろえる

AVG は平均を求めます。表示する小数の桁数をそろえるには、ROUND(値, 桁数) を使います。たとえば、読書記録のジャンルごとの平均読書時間を小数第 4 位まで残すなら、次のように書きます。

SELECT genre, ROUND(AVG(minutes), 4) AS avg_minutes
FROM reading_sessions
GROUP BY genre;

第 2 引数の 4 は、小数第 5 位を四捨五入して第 4 位まで残す指定です。整数列の AVG は小数を扱える numeric 型を返すので、そのまま ROUND に渡せます。平均が 12.5 なら表示は 12.5000 になります。