WITH句（CTE）の活用
WITH句（CTE）の活用 とは
SQLのWITH句（CTE）の構文と、具体的な活用方法について学習します。本レッスンでは、WITH句（CTE）の活用 の基本から実際の使いどころまでを整理し、現場で迷わず使える形に落とし込みます。
なぜ重要か
サブクエリが入れ子になりすぎると、SQL は急激に読みにくくなります。「内側の集計を一旦名前で受けてから、外側で再利用したい」と感じたら、それは CTE（Common Table Expression） の出番です。WITH cte_name AS (...) という構文を使えば、SQL の先頭に処理の名前付きステップを並べられます。CTE は MySQL 8 / TiDB / PostgreSQL / Snowflake などほとんどの現代 RDB で使え、可読性と再利用性が劇的に上がる重要構文です。
構文・キーワード解説
基本構文は次のとおりです。
SQL クエリ
WITH cte_name AS (
SELECT col1, col2 FROM tablename WHERE ...
)
SELECT * FROM cte_name WHERE col1 > 100;複数の CTE をカンマで並べることもできます。後の CTE から前の CTE を参照できる点が、FROM 句のサブクエリより強力です。
SQL クエリ
WITH
base AS (
SELECT category, SUM(price) AS total
FROM products
GROUP BY category
),
ranked AS (
SELECT category, total,
RANK() OVER (ORDER BY total DESC) AS rk
FROM base
)
SELECT * FROM ranked WHERE rk <= 3;ステップごとの動作
CTE は内部的には「名前付きの一時テーブル」として動きます。
上から順に CTE が評価され、最後の SELECT で利用される、という流れです。
代表例
サブクエリ vs CTE の書き換え
まずはサブクエリで書いた例です。
SQL クエリ
SELECT category, avg_price
FROM (
SELECT category, AVG(price) AS avg_price
FROM products
GROUP BY category
) AS t
WHERE avg_price >= 1500;これを CTE にすると次のようになります。処理の段階に名前がついて、読み手の負担が減ります。
SQL クエリ
WITH category_avg AS (
SELECT category, AVG(price) AS avg_price
FROM products
GROUP BY category
)
SELECT category, avg_price
FROM category_avg
WHERE avg_price >= 1500
ORDER BY avg_price DESC;複数 CTE で段階的に処理
SQL クエリ
WITH
category_total AS (
SELECT category, SUM(price) AS total
FROM products
GROUP BY category
),
overall AS (
SELECT SUM(total) AS grand_total FROM category_total
)
SELECT c.category, c.total,
ROUND(c.total * 100.0 / o.grand_total, 1) AS pct
FROM category_total c
CROSS JOIN overall o
ORDER BY c.total DESC;カテゴリ別合計と全体合計を別々の CTE にすると、構成比の計算が直感的になります。
CTE のメリット
| 観点 | サブクエリ | CTE |
|---|---|---|
| 可読性 | 入れ子が深くなりやすい | 上から順に読める |
| 再利用 | 同じサブクエリを書き直す必要 | 同一 SQL 内で何回でも参照 |
| デバッグ | 内側を単独で実行しづらい | CTE を 1 個ずつ確認できる |
「2 段階以上の集計」「同じサブクエリを何度も使う」「複雑な条件で分岐したい」のときは CTE を選ぶと一気に読みやすくなる。
CTE は SQL:1999 で定義され、現代のリレーショナル DB ほぼ全てで使える標準機能。
WITHから書き始める SQL は世界中で読み手にやさしい。
再帰 CTE（おまけ）
CTE には自分自身を参照する 再帰 CTE もあります。階層構造（部署ツリーなど）を辿るときに使います。
SQL クエリ
WITH RECURSIVE numbers AS (
SELECT 1 AS n
UNION ALL
SELECT n + 1 FROM numbers WHERE n < 5
)
SELECT n FROM numbers;注意 再帰 CTE は便利ですが、終了条件を間違えると無限ループになります。
WHERE n < 5のような明確な終了条件を必ず付けましょう。
まとめ
- CTE は
WITH name AS (...)で名前付きの一時テーブルを作る構文 - 複数の CTE を順番に書けて、後ろから前を参照できる
- 多段階の集計や再利用が必要な場合、サブクエリより大きくに読みやすい
- 再帰 CTE を使えば階層データも辿れる（終了条件を忘れない）
次のレッスン
次は サブクエリ実践クイズ です。サブクエリの理解度をクイズで試しましょう。複雑なデータ抽出に挑戦！
事前確認 — 進む前に次の 3 つができることを確認しましょう。
- WITH 句（CTE） の要点を自分の言葉で説明できる
- このレッスンの最小コード (または操作手順) を見ずに書ける
- 練習問題やクイズで間違えた箇所を読み直して理解した
理解度チェック (30 秒)
Q. WITH 句（CTE） とは何か、1 文で説明してください。
A. 本文の「このレッスンで分かること」または冒頭の説明文を見直し、自分の言葉で要約できれば OK。詰まったら本レッスンの最初の H2 セクションを読み返してみましょう。
関連レッスン
テーブル構造
schema.sql
CREATE TABLE products (
id INT PRIMARY KEY,
name VARCHAR(40),
category VARCHAR(30),
price INT
);
INSERT INTO products VALUES
(1, 'シャープペン', '文具', 200),
(2, 'ノート', '文具', 350),
(3, '万年筆', '文具', 5000),
(4, 'マグカップ', '雑貨', 1200),
(5, 'タンブラー', '雑貨', 2500),
(6, 'ワイヤレスマウス', '家電', 3200),
(7, 'キーボード', '家電', 6800),
(8, 'スピーカー', '家電', 4000);期待される出力
| category | total | pct |
|---|---|---|
| 家電 | 14000 | 60.2 |
| 文具 | 5550 | 23.9 |
| 雑貨 | 3700 | 15.9 |