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 に置くほうが手順が見えます。

別名を付け忘れると、そこで止まる

FROM に置いたサブクエリには、必ず別名が要ります。

-- 落ちる  subquery in FROM must have an alias
SELECT dept FROM (SELECT dept FROM employees GROUP BY dept);

-- 通る
SELECT dept FROM (SELECT dept FROM employees GROUP BY dept) AS d;

外側から「どの表の列か」を指せないと困るので、名前が必須になっています。慣れないうちは付け忘れが必ず起きますが、エラーメッセージがそのまま alias と言ってくれるので、見たらすぐ気付けます。

もう 1 つ、内側で付けた別名 (COUNT(*) AS headcount の headcount) を使えるのは外側だけです。同じ丸括弧の中でその名前を参照しようとしても、先ほどと同じ理由で見つかりません。