MySQL 8.0 の CTE（共通テーブル式）を実務で使いこなす
akinoriMySQL 8.0 で追加された CTE（Common Table Expression／共通テーブル式）は、複雑な SQL を「読める」形に保つための機能です。WITH 句でネストの深いサブクエリを分解し、再帰処理まで書けるようになりました。本記事では非再帰 CTE と再帰 CTE の基本から、実務でハマるポイント、パフォーマンス上の注意点までをハンズオン形式でまとめます。サンプルは公開情報に基づく一般的なスキーマで記述しており、特定の案件・顧客情報は含みません。
動作環境
- MySQL 8.0.x（CTE は 8.0 以降で利用可能。5.7 以前では使えません）
mysqlCLI または一般的な GUI クライアントを想定
検証用に2つのテーブルを用意します。
CREATE TABLE departments (
id INT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
parent_id INT NULL,
CONSTRAINT fk_parent FOREIGN KEY (parent_id) REFERENCES departments(id)
);
CREATE TABLE sales (
id INT PRIMARY KEY AUTO_INCREMENT,
dept_id INT NOT NULL,
sold_on DATE NOT NULL,
amount INT NOT NULL,
CONSTRAINT fk_dept FOREIGN KEY (dept_id) REFERENCES departments(id)
);
INSERT INTO departments (id, name, parent_id) VALUES
(1, '本部', NULL), (2, '営業統括', 1), (3, '東日本営業', 2),
(4, '西日本営業', 2), (5, '開発統括', 1);
INSERT INTO sales (dept_id, sold_on, amount) VALUES
(3, '2024-01-10', 120000), (3, '2024-01-20', 80000),
(4, '2024-01-15', 200000), (4, '2024-02-01', 50000),
(5, '2024-02-05', 30000);
CTE とは何か
CTE は、クエリの先頭に WITH 名前 AS (...) と書くことで、そのクエリの中だけで使える「名前付きの一時結果セット」を定義する仕組みです。
WITH 名前 AS (
SELECT ...
)
SELECT * FROM 名前;
従来のサブクエリやビューに近い役割ですが、クエリ内で名前を付けて再利用できること、処理の流れが上から下へ読めること、そして再帰が書けることが大きな違いです。
非再帰 CTE：サブクエリの整理
「部署ごとの売上合計を出し、平均以上の部署だけ抽出する」典型例をサブクエリで書くと、同じ集計を2回書くことになります。
SELECT dept_id, total FROM (
SELECT dept_id, SUM(amount) AS total FROM sales GROUP BY dept_id
) AS t
WHERE total >= (
SELECT AVG(total2) FROM (
SELECT SUM(amount) AS total2 FROM sales GROUP BY dept_id
) AS x
);
CTE で書き直すと、集計を一度だけ定義して再利用できます。
WITH dept_total AS (
SELECT dept_id, SUM(amount) AS total
FROM sales
GROUP BY dept_id
)
SELECT dept_id, total
FROM dept_total
WHERE total >= (SELECT AVG(total) FROM dept_total);
複数 CTE をつなぐ
CTE はカンマ区切りで複数定義でき、後の CTE から前の CTE を参照できます。
WITH
dept_total AS (
SELECT dept_id, SUM(amount) AS total
FROM sales GROUP BY dept_id
),
ranked AS (
SELECT dept_id, total,
RANK() OVER (ORDER BY total DESC) AS rnk
FROM dept_total
)
SELECT d.name, r.total, r.rnk
FROM ranked r
JOIN departments d ON d.id = r.dept_id
ORDER BY r.rnk;
「集計 → 順位付け → 名称結合」というパイプラインが上から順に並び、各ステップを独立して検証できます。これが CTE の最大の実務メリットです。
再帰 CTE：階層構造をたどる
WITH RECURSIVE を使うと、自己参照で階層をたどれます。アンカー（起点）と再帰部を UNION ALL でつなぎます。
WITH RECURSIVE dept_tree AS (
SELECT id, name, parent_id, 0 AS depth
FROM departments
WHERE id = 1
UNION ALL
SELECT d.id, d.name, d.parent_id, t.depth + 1
FROM departments d
JOIN dept_tree t ON d.parent_id = t.id
)
SELECT CONCAT(REPEAT(' ', depth), name) AS tree, depth
FROM dept_tree
ORDER BY depth, id;
結果はツリー状に整形されて返ります。
本部 0
営業統括 1
開発統括 1
東日本営業 2
西日本営業 2
連番・日付シーケンスの生成
再帰 CTE はデータの無い連番も作れます。欠損日を埋めたレポートに便利です。
WITH RECURSIVE date_seq AS (
SELECT DATE('2024-01-01') AS d
UNION ALL
SELECT d + INTERVAL 1 DAY FROM date_seq WHERE d < '2024-01-31'
)
SELECT ds.d, COALESCE(SUM(s.amount), 0) AS daily_total
FROM date_seq ds
LEFT JOIN sales s ON s.sold_on = ds.d
GROUP BY ds.d
ORDER BY ds.d;
無限ループを防ぐ：再帰の深さ制限
再帰の深さは cte_max_recursion_depth（デフォルト 1000）で制限され、超えるとエラーになります。
SET SESSION cte_max_recursion_depth = 5000;
上限を上げる前に、再帰部へ明示的な停止条件を必ず入れてください。安易に上限だけ上げるのは危険です。
パフォーマンス上の注意点
まず、MySQL 8.0 のオプティマイザは非再帰 CTE を外側クエリにマージするか、一時表としてマテリアライズするかを状況に応じて選びます。挙動は EXPLAIN で確認するのが確実です。
EXPLAIN
WITH dept_total AS (
SELECT dept_id, SUM(amount) AS total FROM sales GROUP BY dept_id
)
SELECT * FROM dept_total WHERE total > 100000;
次に、同じ CTE を複数回参照するとマテリアライズされやすくなります。再計算は避けられますが、一時表にはインデックスが無いため、大きな結果を何度も JOIN する設計はコストになります。そして、CTE は可読性を上げる機能であり、それ自体が高速化手段ではない点に注意してください。インデックス設計や結合順序の最適化は従来通り必要です。
確認方法
- 上記の
CREATE TABLEとINSERTを流す - 各 CTE クエリを実行し、期待する結果が返るか確認する
EXPLAINでマテリアライズの有無を確認し、意図した実行計画か見るSELECT @@cte_max_recursion_depth;で現在の上限を把握する
注意点
- CTE は MySQL 8.0 以降専用。5.7 以前を併用する環境では使えないため対応バージョンを確認する
- 再帰部の停止条件は必須。境界を書き忘れると上限エラーで落ちる
- CTE はそのステートメント内でのみ有効。使い回すならビューや一時テーブルを検討する
まとめ
CTE（WITH）は深いサブクエリを「上から読める」形に分解し、複数 CTE をつなげば集計・順位・結合のパイプラインが明確になります。WITH RECURSIVE で階層・連番・日付シーケンスを生成でき、停止条件と cte_max_recursion_depth で暴走を防ぎます。可読性向上が主目的であり、速度は EXPLAIN で実行計画を見て判断する――この割り切りが、複雑なレポート SQL を長く保守するうえで効いてきます。
＼ 最新情報をチェック ／
