WITH 句 — 名前付き中間結果でクエリを段階化する

本記事で使うデータ — employee と department

CTE（Common Table Expression、共通テーブル式）は、WITH 名前 AS (SELECT ...)の形でサブクエリに名前を付け、後続のクエリで参照する仕組みです。

深くネストしたサブクエリを段階に分けて書けるため、可読性が上がります。

題材は社員テーブルemployee（30 行。

manager_id列が上司のemp_idを指す自己参照構造）と、部署テーブルdepartment（6 行）です。

CTE で多段の集計を読みやすく書いていく演習を順に試します。

演習に入る前に、本記事で使う 2 つのテーブル — employeeとdepartment — の列定義とデータのサンプルを確認しておきます。

① PRAGMA table_info(employee);とPRAGMA table_info(department);で両テーブルの列定義を確認してください。

② SELECT * FROM employee LIMIT 5;とSELECT * FROM department LIMIT 5;で先頭 5 行のデータをプレビューしてください。manager_id列に NULL が入る行（上司がいない社員）の入り方も観察してください。

WITH で名前付き中間結果を作る — 多段の処理を読みやすく

WITH 名前 AS (SELECT ...)と書くと、カッコ内の SELECT 結果に名前が付き、続く本体クエリのFROMやJOINで参照できます。

「まず中間結果を作り、それを使って最終結果を出す」という 2 段階の処理を、ネストしたサブクエリで書く代わりに、上から下へ読める形に分解できます。

CTE は本体クエリの実行が終わると消える一時的な名前で、テーブルを作るわけではありません。

書き方はWITH 名前 AS ( ... ) SELECT ... FROM 名前 ...;です。

-- 高給社員を CTE high_earner にまとめ、
-- department と結合して部署名を付ける
WITH high_earner AS (
  SELECT emp_id, name, dept_id, salary
  FROM employee
  WHERE salary >= 7000000
)
SELECT h.name, h.salary, d.dept_name
FROM high_earner h
LEFT JOIN department d ON h.dept_id = d.dept_id
ORDER BY h.salary DESC;

上のコードではhigh_earnerという名前で「給与 7,000,000 以上の社員」の中間結果を切り出し、続く本体クエリでFROM high_earnerとしてテーブルのように参照しています。

図にすると次のような流れです。

同じ処理はサブクエリ（SELECTのカッコの中にさらにSELECTを入れる書き方）でも書けます。

試しに、先ほどの WITH 版を、CTE ではなくサブクエリで組み立て直してみます。

-- 同じ処理を 副問い合わせ（サブクエリ）で書いた版
-- FROM のカッコの中に SELECT が入り、外→中→外の順で読むことになる
SELECT h.name, h.salary, d.dept_name
FROM (
  SELECT emp_id, name, dept_id, salary
  FROM employee
  WHERE salary >= 7000000
) AS h
LEFT JOIN department d ON h.dept_id = d.dept_id
ORDER BY h.salary DESC;

結果は WITH 版とまったく同じですが、サブクエリ版は「中のSELECTが何をしているか」を読むのに FROMの中にネストした SELECT へ目線を潜らせる 必要があります。

WITH 版なら中間結果にhigh_earnerという名前を付けて切り出すので、「① 高給社員を絞る → ② 部署名を付ける」の 2 段階を上から下へ素直に読めます。

中間結果が増えるほどこの差は大きくなり、3 段・4 段の処理になるとサブクエリの入れ子は追いにくくなります。

「上司を持たない社員 (manager_id が NULL の役員クラス) を切り出して、その人たちに所属部署名を併記した一覧を作りたい」という要件を、CTE で 2 段階に分けて書きます。（正しく実行できれば解説が表示されます）

① WITH で、employee から manager_id が NULL の行 (emp_id, name, dept_id) を取り出す名前付き CTE executive を定義してください。

② その CTE と department を、dept_id で LEFT JOIN し、name、dept_id、dept_name を取り出してください。

③ 結果を name の昇順で並べてください。

複数の WITH を連結する — 多段の集計を 1 段ずつに分ける

WITHのあとにカンマで区切って複数の CTEを並べられます。

WITH a AS (...), b AS (...) SELECT ...の形で、後ろの CTE は前の CTE を参照できます。

多段の集計（「部署ごとの平均を出す」→「その平均を超える社員を抽出する」→「部署名を付ける」など）を、1 つずつ名前を付けた段に分けて書けるため、長いクエリでも各段の役割を分けることができます。

-- ① dept_avg: 部署ごとの平均給与
-- ② top_earner: 自部署平均を上回る社員
-- ③ 本体: 部署名を付けて出力
WITH dept_avg AS (
  SELECT dept_id, AVG(salary) AS avg_salary
  FROM employee
  WHERE dept_id IS NOT NULL
  GROUP BY dept_id
),
top_earner AS (
  SELECT e.name, e.dept_id, e.salary
  FROM employee e
  JOIN dept_avg da ON e.dept_id = da.dept_id
  WHERE e.salary > da.avg_salary
)
SELECT d.dept_name, t.name, t.salary
FROM top_earner t
JOIN department d ON t.dept_id = d.dept_id
ORDER BY d.dept_name, t.salary DESC;

「各部署の平均給与を出し、その平均を上回る社員だけを、部署名付きで一覧したい」という要件を、2 つの CTE を連結して解きます。

① 1 つ目の CTE では、employeeをdept_idで集計し、部署ごとの平均給与を出してください。dept_idが NULL の社員は部署平均の計算から除いてください。

② 2 つ目の CTE では、employeeと 1 つ目の CTE をdept_idで結合し、salaryがその部署平均より大きい社員（name, dept_id, salary）を取り出してください。

③ 本体では 2 つ目の CTE とdepartmentをdept_idで結合し、dept_name、name、salaryを、部署名の昇順・同部署内は給与の降順で並べてください。