サブクエリを覚えると、たいていのことは書けるようになります。「合計を出して、その平均を出して、平均を超えているものだけ出す」——書けます。
前回: ウィンドウ関数 — GROUP BYだと名前が消える これは深掘りです。本線の順路ではありません。 本線のプロセスを止めても消えない場所 — MySQLとSQLの基本で
SELECTとサブクエリを打ったところの、その先だけを扱います。読まなくても本線は先に進めます。 作業場所はVMの中(Ubuntu)です。MySQL が動いていれば、他に用意するものはありません。練習用のテーブルはこの記事の中で作ります。
書けたSQLが、これでした。
SELECT dept, cnt, ROUND(cnt / avg_cnt, 2) AS ratio
FROM (
SELECT dept, cnt,
(SELECT AVG(cnt) FROM (SELECT dept, COUNT(*) AS cnt FROM d_cte_emp GROUP BY dept) x) AS avg_cnt
FROM (SELECT dept, COUNT(*) AS cnt FROM d_cte_emp GROUP BY dept) y
) z
WHERE cnt > avg_cnt
ORDER BY cnt DESC;
動きます。正しい答えも返ります。
そして、1週間後の自分が読めません。
読めない理由ははっきりしています。
内側から外側に向かって書いたものを、外側から内側に向かって読まされるからです。 しかもまったく同じサブクエリが2回書いてあり、片方だけ直すと黙って壊れます。
これを直す道具が CTE(Common Table Expression)、SQL の上では WITH です。
やっていることは、たった1つです。
入れ子になっている部分に名前を付けて、上に出す。
新しい計算ができるようになるわけではありません。同じ計算を、上から下に読める形に並べ替えるだけです。
ただし1つだけ、WITH にしかできないことがあります。 それが WITH RECURSIVE(再帰CTE)で、「自分自身を参照する」と書けます。これがあると、組織図や、カテゴリの木構造を、SQLだけでたどれるようになります。
この記事は、その2つを扱います。
対象レベル: 中級〜上級 / 区分: 発展
この記事でわかること
- CTE は「新しい機能」ではなく「サブクエリに名前を付けて上に出したもの」だということ。
EXPLAINの中では派生表と同じDERIVEDとして現れること WITHを使っても、多くの場合まったく速くならないこと。速さの道具ではなく、読みやすさの道具であること- 同じCTEを2回参照すると、2回実行されることがあること。「1回だけ計算して使い回してくれる」と思い込むと事故ること
- 再帰CTEは「開始行 /
UNION ALL/ 自分を参照する部分」の3部構成であること。難しいのは再帰ではなく、止め方だということ - 止める条件を書き忘れると無限ループになること。MySQL は
cte_max_recursion_depthで強制的に止めてくれるが、それは安全装置であって設計ではないこと - 循環参照(自分が自分の上司)があると、条件を書いても止まらないこと。深さで打ち切るか、通った経路を覚えておくしかないこと
- 木構造の持ち方は隣接リストだけではないこと。経路列挙・閉包テーブルという選択肢があること
この記事でやること
- MySQL のバージョンを確認する。 8.0 未満だと、この記事の SQL は1行も動きません
- 練習用のテーブルを3枚作る(組織図とカテゴリの木と日付の歯抜けログ)。
appdbの中にd_cte_で始まる名前で作り、既存のテーブルには一切触りません - 読めないSQLを
WITHで書き直し、EXPLAINを並べて実行計画を比べる WITH RECURSIVEで1から10まで数える。 そのあとわざと止まらなくして、エラーで止まるところを見る- 組織図を上(上司をたどる)と下(配下を全部出す)の両方向にたどり、経路を文字列で作る
- 循環参照を仕込んで止まらなくし、深さで打ち切る型に直す
- 再帰CTEで日付を全部作って、歯抜けのログを埋める
- 後片づけをして、
d_cte_で始まるテーブルを全部消す
先に知る言葉
この記事で初めて出てくる言葉です。 基本的な言葉は用語集にもまとめてあります。
| 言葉 | 意味 |
|---|---|
| CTE | WITH 名前 AS (SELECT ...) で作る、名前の付いた一時的な結果表。 Common Table Expression の略。日本語では「共通テーブル式」 |
WITH | CTE を書き始めるキーワード。SELECT の前に置く |
| 派生表 | FROM (SELECT ...) t の形で、FROM の中に直接書いたサブクエリ。 CTE は、これを上に出したもの |
| マテリアライズ | 途中結果を一時テーブルとして実際に作って置いておくこと。作れば使い回せるが、作るコストがかかる |
| 再帰 | 自分自身を呼び出して、少しずつ進めていくやり方。必ず「止まる条件」とセット |
WITH RECURSIVE | 自分自身を参照できる CTE。 階層構造をたどるときに使う |
| 隣接リスト | 各行が「自分の親は誰か」だけを持つ木の持ち方。manager_id や parent_id がこれ |
| 循環参照 | AはBの部下で、BはAの部下のように、たどると元に戻ってくる状態。木ではなくなる |
CTE と派生表は、書ける内容がほぼ同じです。 違うのは置き場所だけです。 「
FROMの中に埋め込む」か「先頭に名前付きで並べる」か。 それだけの差が、読みやすさを決めます。
① まずバージョンを確認する(ここを飛ばすと全部落ちます)
CTE も再帰CTEも、MySQL 8.0 で入りました。 それ以前の 5.7 系には存在しません。
VM の中で打ちます。
mysql Ver 8.0.43-0ubuntu0.24.04.1 for Linux on x86_64 ((Ubuntu))
Ver 8. 以上なら大丈夫です。
ただし、クライアントの版とサーバーの版は別物です。本番はサーバー側なので、そちらを見ます。ログインします。
SELECT VERSION();
+-------------------------+
| VERSION() |
+-------------------------+
| 8.0.43-0ubuntu0.24.04.1 |
+-------------------------+
この VERSION() が 5.7.x なら、以降は1行も動きません。
5.7 だった場合、どうするか
5.7 では、こういう出方をします。
ERROR 1064 (42000): You have an error in your SQL syntax; ... near 'WITH dept_count AS (' at line 1
「そんな機能はありません」ではなく「文法が違います」と言われるのがポイントです。WITH という書き方自体を知らないので、パーサーがそこで止まります。綴りを疑って時間を溶かさないでください。 版の問題です。
選べる道は3つです。
| 道 | 中身 |
|---|---|
| MySQL 8 に上げる | 一番まっすぐ。学習用の VM なら、これが早いです |
| PostgreSQL を使う | PostgreSQL は 8.4 の頃から WITH と WITH RECURSIVE を持っています。 本線のMySQLとPostgreSQLの違いは1割の回で入れたものがあれば、この記事の SQL はほぼそのまま通ります |
| 派生表で書く | CTE の部分は、全部サブクエリで書き直せます。 ただしこの記事の出発点である「読めない形」に戻ります。 そして再帰だけは書き直せません |
再帰が書けない環境で階層をたどるには、「深さの分だけ JOIN を並べる」か「アプリ側で何度も問い合わせる」か「ストアドプロシージャでループを書く」しかありません。 どれも、木の深さが変わった瞬間に破綻します。 8 系に上げる理由としては十分です。
以降は 8.0 以降が動いている前提で進めます。
② 練習用のテーブルを作る
この記事は、これだけで完結します。 前の回のデータは使いません。
まだ mysql に入っていなければ入ります。
既存の appdb の中に作りますが、名前は全部 d_cte_ で始めます。 最後に、この接頭辞のものだけをまとめて消します。tasks や users には触りません。
USE appdb;
組織図(社員と上司)
各行が「自分の上司は誰か」を1つだけ持ちます。 これを隣接リストと呼びます。木構造のいちばん普通の持ち方です。
CREATE TABLE d_cte_emp (
id INT PRIMARY KEY,
name VARCHAR(20) NOT NULL,
dept VARCHAR(20) NOT NULL,
manager_id INT NULL
) CHARACTER SET utf8mb4;
manager_idをNULL可にしています。 社長には上司がいないからです。 「上司がいない」は、この設計ではNULLでしか表せません。 0 やマイナスの値を使うと、あとでJOINの条件が全部おかしくなります。
データを入れます。社長を頂点にした5階層です。
INSERT INTO d_cte_emp (id, name, dept, manager_id) VALUES
(1, '社長', '経営', NULL),
(2, '営業部長', '営業', 1),
(3, '開発部長', '開発', 1),
(4, '東京課長', '営業', 2),
(5, '大阪課長', '営業', 2),
(6, '基盤課長', '開発', 3),
(7, '佐藤', '営業', 4),
(8, '鈴木', '営業', 4),
(9, '田中', '営業', 5),
(10, '高橋', '開発', 6),
(11, '伊藤', '開発', 6),
(12, '渡辺', '営業', 7),
(13, '中村', '開発', 6);
形はこうなっています。 紙に描いておくと、あとの結果が全部照らし合わせられます。
社長(1)
├─ 営業部長(2)
│ ├─ 東京課長(4)
│ │ ├─ 佐藤(7)
│ │ │ └─ 渡辺(12)
│ │ └─ 鈴木(8)
│ └─ 大阪課長(5)
│ └─ 田中(9)
└─ 開発部長(3)
└─ 基盤課長(6)
├─ 高橋(10)
├─ 伊藤(11)
└─ 中村(13)
カテゴリの木
同じ形を、商品カテゴリでも作ります。 「同じ構造は、名前が変わっても同じ SQL でたどれる」ことを、あとで確かめるためです。
CREATE TABLE d_cte_cat (
id INT PRIMARY KEY,
name VARCHAR(30) NOT NULL,
parent_id INT NULL
) CHARACTER SET utf8mb4;
INSERT INTO d_cte_cat (id, name, parent_id) VALUES
(1, 'すべて', NULL),
(2, '家電', 1),
(3, 'パソコン', 2),
(4, 'ノートPC', 3),
(5, 'デスクトップPC', 3),
(6, '周辺機器', 2),
(7, 'キーボード', 6),
(8, 'マウス', 6),
(9, '書籍', 1),
(10, '技術書', 9),
(11, 'プログラミング', 10),
(12, 'データベース', 10);
列の名前が manager_id から parent_id に変わっただけです。構造はまったく同じです。
歯抜けのログ
最後に、日付が飛んでいる集計ログを作ります。 ⑨ で使います。
CREATE TABLE d_cte_log (
d DATE NOT NULL PRIMARY KEY,
cnt INT NOT NULL
);
INSERT INTO d_cte_log (d, cnt) VALUES
('2026-09-01', 12),
('2026-09-02', 8),
('2026-09-05', 20),
('2026-09-06', 3),
('2026-09-09', 15);
9月3日・4日・7日・8日・10日がありません。 「その日は0件だった」のではなく、「行そのものが無い」状態です。
入ったか確かめる
SELECT (SELECT COUNT(*) FROM d_cte_emp) AS emp,
(SELECT COUNT(*) FROM d_cte_cat) AS cat,
(SELECT COUNT(*) FROM d_cte_log) AS log_rows;
+-----+-----+----------+
| emp | cat | log_rows |
+-----+-----+----------+
| 13 | 12 | 5 |
+-----+-----+----------+
13 / 12 / 5 でなければ、INSERT のどこかが落ちています。 そのテーブルを DROP TABLE して作り直してください。
[スクリーンショット: 01_バージョンとデータ確認.png] SELECT VERSION(); と、上の件数確認の結果が続けて写っている画面を撮ってください。
③ 読めないクエリを、WITH で書き直す
冒頭のSQLを、実際に打ってみます。
「部署ごとの人数が、全部署の平均人数を超えている部署」を、平均の何倍かも一緒に出します。
SELECT dept, cnt, ROUND(cnt / avg_cnt, 2) AS ratio
FROM (
SELECT dept, cnt,
(SELECT AVG(cnt) FROM (SELECT dept, COUNT(*) AS cnt FROM d_cte_emp GROUP BY dept) x) AS avg_cnt
FROM (SELECT dept, COUNT(*) AS cnt FROM d_cte_emp GROUP BY dept) y
) z
WHERE cnt > avg_cnt
ORDER BY cnt DESC;
+--------+-----+-------+
| dept | cnt | ratio |
+--------+-----+-------+
| 営業 | 7 | 1.62 |
| 開発 | 5 | 1.15 |
+--------+-----+-------+
正しい答えです。 経営1人・営業7人・開発5人で、平均は 4.33 人。それを超えているのは営業と開発です。
問題は結果ではありません。 このSQLには、読み手を殺す点が3つあります。
| 問題 | 中身 |
|---|---|
| 読む順番が逆 | 一番内側の GROUP BY dept から書いたのに、読むときは一番外の SELECT から入る |
| 同じものが2回書いてある | SELECT dept, COUNT(*) ... GROUP BY dept が2箇所。片方だけ直すと、静かに嘘の結果になる |
名前が x y z | 意味のない別名が3つ。 増えるほど、どれが何だったか分からなくなる |
WITH で書き直す
同じ結果を返します。 見比べてください。
WITH dept_count AS (
SELECT dept, COUNT(*) AS cnt
FROM d_cte_emp
GROUP BY dept
),
dept_avg AS (
SELECT AVG(cnt) AS avg_cnt
FROM dept_count
)
SELECT d.dept, d.cnt, ROUND(d.cnt / a.avg_cnt, 2) AS ratio
FROM dept_count d
CROSS JOIN dept_avg a
WHERE d.cnt > a.avg_cnt
ORDER BY d.cnt DESC;
+--------+-----+-------+
| dept | cnt | ratio |
+--------+-----+-------+
| 営業 | 7 | 1.62 |
| 開発 | 5 | 1.15 |
+--------+-----+-------+
同じです。 そして、上から下に読めます。
1.
dept_count= 部署ごとの人数 2.dept_avg= その平均(dept_countを使っている) 3. 本体 = 平均を超えている部署だけ
日本語で説明した順番と、SQLの並び順が一致しました。 これがCTEの効果の全部です。
WITH は複数書ける。そして、前のCTEを次で使える
上の書き方で、いちばん大事なのはここです。
WITH
最初の名前 AS ( ... ), -- ← カンマで区切る
次の名前 AS ( ... 最初の名前 を使える ... ),
その次 AS ( ... 上の2つとも使える ... )
SELECT ... -- ← 本体。全部使える
| 規則 | 中身 |
|---|---|
WITH は1回だけ書く | 2つ目以降に WITH は付けません。 カンマで並べます |
| 区切りはカンマ | 最後のCTEの後ろにカンマは付けません(付けると SELECT の直前で文法エラー) |
| 使えるのは「上で定義したもの」だけ | 下で定義したCTEを、上のCTEから参照することはできません(再帰を除く) |
| 本体の直前で終わる | CTEの並びが終わったら、そのまま SELECT を書きます。セミコロンは入れません |
「重複していたサブクエリが1箇所になった」のも、そのまま効きます。dept_count の定義は1箇所しかありません。 直すときも1箇所です。
③ でやったことは、機能の追加ではありません。 同じSQLを、書いた順に読める形へ並べ替えただけです。それでも、これが
WITHの使い道の9割です。
別名を先に決めておく書き方
CTE には、列名を先に書いてしまうこともできます。
WITH dept_count (bumon, ninzu) AS (
SELECT dept, COUNT(*) FROM d_cte_emp GROUP BY dept
)
SELECT bumon, ninzu FROM dept_count ORDER BY ninzu DESC;
+--------+-------+
| bumon | ninzu |
+--------+-------+
| 営業 | 7 |
| 開発 | 5 |
| 経営 | 1 |
+--------+-------+
内側で AS を書かなくてよくなります。 ただし、列の数がぴったり合っていないとエラーです。
ERROR 1353 (HY000): In definition of view, derived table or common table expression, SELECT list and column names list have different column counts
再帰CTEでは、この書き方のほうが読みやすくなることが多いので、覚えておいてください。
④ EXPLAIN で比べる — 速くはならない
「WITH にすると速くなるんですか」 —— ここをはっきりさせます。
まず、書き直す前のSQL。
EXPLAIN SELECT dept, cnt, ROUND(cnt / avg_cnt, 2) AS ratio
FROM (
SELECT dept, cnt,
(SELECT AVG(cnt) FROM (SELECT dept, COUNT(*) AS cnt FROM d_cte_emp GROUP BY dept) x) AS avg_cnt
FROM (SELECT dept, COUNT(*) AS cnt FROM d_cte_emp GROUP BY dept) y
) z
WHERE cnt > avg_cnt;
次に、WITH で書いたほう。
EXPLAIN WITH dept_count AS (
SELECT dept, COUNT(*) AS cnt FROM d_cte_emp GROUP BY dept
),
dept_avg AS (
SELECT AVG(cnt) AS avg_cnt FROM dept_count
)
SELECT d.dept, d.cnt, ROUND(d.cnt / a.avg_cnt, 2) AS ratio
FROM dept_count d CROSS JOIN dept_avg a
WHERE d.cnt > a.avg_cnt;
手元では、どちらにも DERIVED が出ました。(下は WITH 版の例)
+----+-------------+------------+------+---------+------+------+----------+---------------------------------+
| id | select_type | table | type | key | ref | rows | filtered | Extra |
+----+-------------+------------+------+---------+------+------+----------+---------------------------------+
| 1 | PRIMARY | <derived3> | ALL | NULL | NULL | 3 | 100.00 | NULL |
| 1 | PRIMARY | <derived4> | ALL | NULL | NULL | 3 | 100.00 | Using where; Using join buffer |
| 4 | DERIVED | d_cte_emp | ALL | NULL | NULL | 13 | 100.00 | Using temporary |
| 3 | DERIVED | d_cte_emp | ALL | NULL | NULL | 13 | 100.00 | Using temporary |
+----+-------------+------------+------+---------+------+------+----------+---------------------------------+
derived の番号や rows の数字は、環境や版で変わります。 見てほしいのは数字ではなく、次の1点だけです。
select_typeにCTEという値はありません。 CTE はDERIVED、つまり派生表として出てきます。
MySQL にとって、WITH は派生表と同じものです。名前の付いた場所が違うだけで、実行計画のレベルでは区別されていません。
| 派生表(入れ子) | CTE(WITH) | |
|---|---|---|
EXPLAIN での表示 | DERIVED | DERIVED(同じ) |
| 速さ | 多くの場合、変わらない | 多くの場合、変わらない |
| 読みやすさ | 入れ子が深いほど落ちる | 上から下に読める |
| 同じ計算の重複 | 手で2回書くことになる | 1箇所で済む |
| 再帰 | 書けない | 書ける |
結論を先に置きます。 CTE は、性能改善の道具ではありません。 読みやすさと、重複を消すための道具です。 「
WITHにしたのに速くならない」は、失敗ではなく想定どおりです。速さの話をしたいときは、本線の速くする前に必ず測る — パフォーマンス改善とEXPLAINの回に全部あります。索引を張る・読む行を減らすという話であって、書き方を変える話ではありません。
[スクリーンショット: 02_EXPLAIN比較.png] 派生表版と WITH 版の EXPLAIN を続けて打って、両方に DERIVED が出ているところが同じ画面に写るように撮ってください。
⑤ 同じCTEを2回使うと、2回実行されることがある
ここが、CTEでいちばん誤解される点です。
「WITH で名前を付けたのだから、1回だけ計算して使い回してくれるはず」 —— そうとは限りません。
MySQL は、CTE を次の2通りのどちらかで扱います。
| 扱い方 | 中身 | 結果 |
|---|---|---|
| マージ(展開) | CTE の中身を、使っている場所へそのまま埋め込む | 参照した回数だけ計算される |
| マテリアライズ | 一時テーブルに書き出して、それを読む | 1回だけ計算される |
どちらになるかは、ふつうはオプティマイザが決めます。 ただし、集約関数・GROUP BY・DISTINCT・LIMIT・UNION などを含む CTE や、テーブルを参照しない CTE は、マージされずにマテリアライズされます。 マテリアライズされた CTE は、何回参照しても1回だけ作られます。 こちらから指定したいときは、MERGE / NO_MERGE のオプティマイザヒント(CTE ごとに書けます)か、optimizer_switch の derived_merge を使います。
自分の環境で確かめる
RAND() を使うと、何回計算されたかが目で見えます。 同じCTEを2回参照します。
WITH t AS (SELECT RAND() AS r)
SELECT r FROM t
UNION ALL
SELECT r FROM t;
MySQL では、2行とも同じ値になります。 この CTE はテーブルを参照していないので、マージされずにマテリアライズされるからです。
(マテリアライズされた=1回だけ計算された)
+---------------------+
| r |
+---------------------+
| 0.24831055912456789 |
| 0.24831055912456789 | ← 同じ値
+---------------------+
-- マテリアライズ(1回だけ計算)を自分で指定する書き方
WITH t AS (SELECT dept FROM d_cte_emp)
SELECT /*+ NO_MERGE(t) */ COUNT(*)
FROM t a JOIN t b ON a.dept = b.dept;
テーブルを読むだけの単純な CTE(上の t のような形)は、マージされることがあります。 そのときは参照した場所ごとに展開され、参照した回数だけ計算されます。1回だけにしたいときは、上のように NO_MERGE ヒントを書きます(逆は MERGE。ただし GROUP BY などマージを妨げる書き方が入っていると効きません)。
EXPLAIN でも確かめられます。
EXPLAIN WITH t AS (SELECT RAND() AS r)
SELECT r FROM t UNION ALL SELECT r FROM t;
Extra に Using temporary が出ていれば、一時テーブルを作っている(=マテリアライズ寄り)と読めます。
だから何に気をつけるか
気をつけるのは、マージされうる CTE(集約や GROUP BY を含まず、テーブルを読むだけの CTE)を、本体で何度も参照するときです。参照した場所ごとに展開されるので、重い結合や絞り込みが参照の回数だけ実行されることがあります。
-- GROUP BY を含む CTE はマテリアライズされる。2回参照しても計算は1回
WITH heavy AS (
SELECT dept, COUNT(*) AS cnt FROM d_cte_emp GROUP BY dept
)
SELECT a.dept, a.cnt, b.cnt
FROM heavy a JOIN heavy b ON a.dept <> b.dept;
集計を含む CTE は、このように何回参照しても1回で済みます。 逆に、GROUP BY を外してテーブルを読むだけにした CTE を2回参照すると、マージされて2回読むことがあります。どちらになったかは EXPLAIN で確かめます。
マージされる CTE は「1回だけ計算」になりません。 1回だけにしたいなら、
NO_MERGEヒントを書くか、自分で一時テーブルを作ります。CREATE TEMPORARY TABLE d_cte_tmp AS SELECT dept, COUNT(*) AS cnt FROM d_cte_emp GROUP BY dept;
TEMPORARYを付けたテーブルは、その接続を閉じたときに自動で消えます。 「必ず1回だけ計算して、あとは読むだけ」を自分で保証したいときの手です。
そして、この誤解には裏返しもあります。
「
WITHにしたから遅くなった」も、たいていは思い込みです。 ④ で見たとおり、派生表と同じ扱いなので、書き方を戻しても同じだけ遅いです。 遅いと感じたら、書き方ではなくEXPLAINを見てください。
⑥ 再帰CTEは3部構成
ここから、WITH にしかできないことに入ります。
再帰CTEは、必ずこの形をしています。 部品は3つだけです。
WITH RECURSIVE 名前 AS (
① SELECT ... -- 開始行(アンカー)。自分を参照しない
UNION ALL -- ② 必ず UNION ALL(または UNION)
③ SELECT ... FROM 名前 WHERE ... -- 自分を参照する。ここに「止まる条件」
)
SELECT * FROM 名前;
動き方を、言葉にするとこうです。
| 回 | 何が起きるか |
|---|---|
| 0回目 | ①だけを実行して、結果を作る。これが「いま持っている行」 |
| 1回目 | 「いま持っている行」に対して③を実行する。出てきた行を結果に足し、それが次の「いま持っている行」になる |
| 2回目 | 1回目で出てきた行に対して③を実行する。前の回の分は、もう見ない |
| … | ③ が1行も返さなくなったら終わり |
ここが最大の勘所です。 ③ が見ている
名前は、「これまでの全部」ではありません。「直前の回で新しく出てきた行だけ」です。 だから、③ が空を返した瞬間に終わります。 逆に言えば、③ が空を返さない限り、永遠に終わりません。
一番簡単な例 — 1から10まで数える
テーブルを1枚も使いません。 再帰の骨だけです。
WITH RECURSIVE nums AS (
SELECT 1 AS n
UNION ALL
SELECT n + 1 FROM nums WHERE n < 10
)
SELECT n FROM nums;
+----+
| n |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
+----+
10 rows in set (0.00 sec)
3つの部品を、この例で確認します。
| 部品 | この例では | 役割 |
|---|---|---|
| 開始行 | SELECT 1 AS n | 1行だけ作る。 ここで列の名前と型が決まる |
UNION ALL | UNION ALL | 上と下をつなぐ。この位置は動かせない |
| 自分を参照する部分 | SELECT n + 1 FROM nums WHERE n < 10 | nums が自分自身。 WHERE n < 10 が止まる条件 |
n = 10 になった回で、WHERE n < 10 が偽になり、③ が0行を返します。そこで終わります。
RECURSIVEは、CTEが複数あって1つだけ再帰でも、WITHの直後に1回書きます。WITH RECURSIVE a AS (...), b AS (...)の形です。bの前にもう一度書くことはありません。
止まらないと、どうなるか
WHERE n < 10 を消します。 わざとです。
WITH RECURSIVE nums AS (
SELECT 1 AS n
UNION ALL
SELECT n + 1 FROM nums
)
SELECT n FROM nums;
ERROR 3636 (HY000): Recursive query aborted after 1001 iterations. Try increasing @@cte_max_recursion_depth to a larger value.
VM は固まりません。MySQL が止めてくれました。
止めているのは cte_max_recursion_depth という設定です。 既定は 1000 です。
SELECT @@cte_max_recursion_depth;
+---------------------------+
| @@cte_max_recursion_depth |
+---------------------------+
| 1000 |
+---------------------------+
実際に、この数字で止まっていることを見ます。 わざと小さくします。
SET SESSION cte_max_recursion_depth = 5;
WITH RECURSIVE nums AS (
SELECT 1 AS n
UNION ALL
SELECT n + 1 FROM nums
)
SELECT n FROM nums;
ERROR 3636 (HY000): Recursive query aborted after 6 iterations. Try increasing @@cte_max_recursion_depth to a larger value.
さっきは 1001、今度は 6 です。 設定した数 + 1 回目で止まります(開始行の分が1回に数えられます)。この安全装置が実在することが、これで確かめられました。
戻しておきます。
SET SESSION cte_max_recursion_depth = 1000;
ここを勘違いすると事故ります。
cte_max_recursion_depthは「安全装置」であって「止め方」ではありません。 止め方はWHEREに書くものです。 設定を大きくして通すのは、エラーを消しただけで、無限ループはそのままです。1000 を超えて正当に必要な場面は、確かにあります(10万件の連番を作るときなど)。 そのときは
SET SESSIONで、その接続の中だけ上げてください。SET GLOBALで全体を上げると、他の暴走も止まらなくなります。
大きな連番が要るときは、こう書きます。
SET SESSION cte_max_recursion_depth = 100000;
WITH RECURSIVE seq AS (
SELECT 1 AS n
UNION ALL
SELECT n + 1 FROM seq WHERE n < 50000
)
SELECT COUNT(*), MAX(n) FROM seq;
+----------+--------+
| COUNT(*) | MAX(n) |
+----------+--------+
| 50000 | 50000 |
+----------+--------+
SET SESSION は、その接続の中だけ有効です。EXIT して入り直すと 1000 に戻ります。
[スクリーンショット: 03_再帰の停止.png] SET SESSION cte_max_recursion_depth = 5; を打ってから、止まる条件の無い再帰CTEを実行し、 Recursive query aborted after 6 iterations が出ている画面を撮ってください。
UNION ALL と UNION の違い
UNION(=UNION DISTINCT)でも書けます。 違いはここです。
| 書き方 | 重複行 | 速さ | 無限ループ |
|---|---|---|---|
UNION ALL | そのまま残る | 速い | 同じ行が出続けると止まらない |
UNION | 消える | 重複判定の分だけ遅い | 同じ行が出たら消えるので、止まることがある |
基本は UNION ALL です。 UNION を「循環対策」として使うのは勧めません。 止まるかどうかがデータ次第になり、止まらなかったときに理由が分からなくなるからです。⑧ で、ちゃんとした止め方をやります。
⑦ 組織図をたどる
ここまでの部品で、組織図をたどれます。
上へ — ある社員の上司を、根までたどる
渡辺(id=12)の上司を、社長まで全部出します。
WITH RECURSIVE up AS (
SELECT id, name, manager_id, 1 AS lv
FROM d_cte_emp
WHERE id = 12
UNION ALL
SELECT e.id, e.name, e.manager_id, up.lv + 1
FROM d_cte_emp e
JOIN up ON e.id = up.manager_id
)
SELECT lv, id, name FROM up ORDER BY lv;
+----+----+-----------+
| lv | id | name |
+----+----+-----------+
| 1 | 12 | 渡辺 |
| 2 | 7 | 佐藤 |
| 3 | 4 | 東京課長 |
| 4 | 2 | 営業部長 |
| 5 | 1 | 社長 |
+----+----+-----------+
②で描いた図と一致しています。
なぜ止まったのかを、はっきりさせておきます。
WHEREに止まる条件を1つも書いていません。 それでも止まりました。 社長のmanager_idがNULLだからです。JOIN up ON e.id = up.manager_idで、up.manager_idがNULLの行は、どのe.idとも一致しません。 ③ が0行を返し、そこで終わります。
NULL が終端の印になっているわけです。② で「manager_id に 0 を入れるな」と書いたのは、これが理由です。0 を入れると、id = 0 の社員がいないので結果的には止まりますが、「終端だから止まった」のか「たまたま該当が無かった」のかが区別できなくなります。
JOIN の向きに注意してください。
| たどる向き | 結合条件 | 日本語で言うと |
|---|---|---|
| 上へ(上司) | e.id = up.manager_id | いま持っている行の「上司ID」に一致する社員 |
| 下へ(部下) | e.manager_id = down.id | いま持っている行の「ID」を上司に持つ社員 |
この2行を取り違えると、1件も返らないか、全件返るかのどちらかになります。 エラーは出ません。
下へ — ある部長の配下を、全部出す
営業部長(id=2)の配下を、深さも一緒に出します。
WITH RECURSIVE down AS (
SELECT id, name, manager_id, 0 AS depth
FROM d_cte_emp
WHERE id = 2
UNION ALL
SELECT e.id, e.name, e.manager_id, d.depth + 1
FROM d_cte_emp e
JOIN down d ON e.manager_id = d.id
)
SELECT depth, id, name FROM down ORDER BY depth, id;
+-------+----+-----------+
| depth | id | name |
+-------+----+-----------+
| 0 | 2 | 営業部長 |
| 1 | 4 | 東京課長 |
| 1 | 5 | 大阪課長 |
| 2 | 7 | 佐藤 |
| 2 | 8 | 鈴木 |
| 2 | 9 | 田中 |
| 3 | 12 | 渡辺 |
+-------+----+-----------+
7行です。開発側(3・6・10・11・13)は入っていません。 開始行を営業部長にしたからです。
「自分自身を含めたくない」場合は、外側で外します。
SELECT depth, id, name FROM down WHERE depth > 0 ORDER BY depth, id;
depthは、0から始めるか1から始めるかを自分で決めます。0にすると「開始点からの距離」、1にすると「階層の番号」になります。 どちらでもいいのですが、混ぜると必ず1つずれます。 私は下へたどるときは 0、上へたどるときは 1 に統一しました。
経路(パス)を文字列で作る
「どこを通ってきたか」を持つと、木の形を1列で表せます。
WITH RECURSIVE tree AS (
SELECT id, name, manager_id, 0 AS depth,
CAST(name AS CHAR(200)) AS path
FROM d_cte_emp
WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.name, e.manager_id, t.depth + 1,
CONCAT(t.path, ' > ', e.name)
FROM d_cte_emp e
JOIN tree t ON e.manager_id = t.id
)
SELECT depth, path FROM tree ORDER BY path;
+-------+---------------------------------------------------+
| depth | path |
+-------+---------------------------------------------------+
| 0 | 社長 |
| 1 | 社長 > 営業部長 |
| 2 | 社長 > 営業部長 > 大阪課長 |
| 3 | 社長 > 営業部長 > 大阪課長 > 田中 |
| 2 | 社長 > 営業部長 > 東京課長 |
| 3 | 社長 > 営業部長 > 東京課長 > 佐藤 |
| 4 | 社長 > 営業部長 > 東京課長 > 佐藤 > 渡辺 |
| 3 | 社長 > 営業部長 > 東京課長 > 鈴木 |
| 1 | 社長 > 開発部長 |
| 2 | 社長 > 開発部長 > 基盤課長 |
| 3 | 社長 > 開発部長 > 基盤課長 > 中村 |
| 3 | 社長 > 開発部長 > 基盤課長 > 伊藤 |
| 3 | 社長 > 開発部長 > 基盤課長 > 高橋 |
+-------+---------------------------------------------------+
開始行を WHERE manager_id IS NULL にしたので、木全体(13行)が出ました。
そして ORDER BY path にすると、親のすぐ下に子が並びます。 これが経路を作る最大の利点です。ORDER BY depth では、こうはなりません(同じ深さの人が、親と無関係にまとまってしまいます)。
インデントを付けて木らしく出すこともできます。
WITH RECURSIVE tree AS (
SELECT id, name, manager_id, 0 AS depth, CAST(name AS CHAR(200)) AS path
FROM d_cte_emp WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.name, e.manager_id, t.depth + 1, CONCAT(t.path, ' > ', e.name)
FROM d_cte_emp e JOIN tree t ON e.manager_id = t.id
)
SELECT CONCAT(REPEAT(' ', depth), name) AS soshiki FROM tree ORDER BY path;
+----------------------+
| soshiki |
+----------------------+
| 社長 |
| 営業部長 |
| 大阪課長 |
| 田中 |
| 東京課長 |
| 佐藤 |
| 渡辺 |
| 鈴木 |
| 開発部長 |
| 基盤課長 |
| 中村 |
| 伊藤 |
| 高橋 |
+----------------------+
CAST を外すと落ちる
CAST(name AS CHAR(200)) を書いた理由を説明します。外してみます。
WITH RECURSIVE tree AS (
SELECT id, name, 0 AS depth, name AS path
FROM d_cte_emp WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.name, t.depth + 1, CONCAT(t.path, ' > ', e.name)
FROM d_cte_emp e JOIN tree t ON e.manager_id = t.id
)
SELECT path FROM tree;
ERROR 1406 (22001): Data too long for column 'path' at row 1
原因は、これです。
再帰CTEの列の型は、開始行(①)だけで決まります。
nameはVARCHAR(20)なので、path列も 20 文字分しか用意されません。 2回目以降にCONCATで長い文字列を作っても、入る場所が無いのでエラーになります。
③ で作られる値がどれだけ長くなるかは、MySQL には分かりません。 だから①で先に広げておく必要があります。
再帰CTEで文字列を積み上げるときは、開始行で必ず
CAST(... AS CHAR(十分な長さ))を書く。 これは覚えるしかない作法です。同じことは数値の桁でも起こります。
カテゴリの木でも、同じSQLが動く
列名を manager_id → parent_id、テーブルを d_cte_emp → d_cte_cat に変えるだけです。
WITH RECURSIVE ctree AS (
SELECT id, name, parent_id, 0 AS depth, CAST(name AS CHAR(200)) AS path
FROM d_cte_cat WHERE parent_id IS NULL
UNION ALL
SELECT c.id, c.name, c.parent_id, t.depth + 1, CONCAT(t.path, ' / ', c.name)
FROM d_cte_cat c JOIN ctree t ON c.parent_id = t.id
)
SELECT depth, path FROM ctree ORDER BY path;
+-------+--------------------------------------------------+
| depth | path |
+-------+--------------------------------------------------+
| 0 | すべて |
| 1 | すべて / 家電 |
| 2 | すべて / 家電 / パソコン |
| 3 | すべて / 家電 / パソコン / デスクトップPC |
| 3 | すべて / 家電 / パソコン / ノートPC |
| 2 | すべて / 家電 / 周辺機器 |
| 3 | すべて / 家電 / 周辺機器 / キーボード |
| 3 | すべて / 家電 / 周辺機器 / マウス |
| 1 | すべて / 書籍 |
| 2 | すべて / 書籍 / 技術書 |
| 3 | すべて / 書籍 / 技術書 / データベース |
| 3 | すべて / 書籍 / 技術書 / プログラミング |
+-------+--------------------------------------------------+
「パソコン配下のカテゴリを全部」のような、画面でよく必要になるやつも同じです。
WITH RECURSIVE sub AS (
SELECT id, name, parent_id FROM d_cte_cat WHERE id = 3
UNION ALL
SELECT c.id, c.name, c.parent_id FROM d_cte_cat c JOIN sub s ON c.parent_id = s.id
)
SELECT id, name FROM sub;
+----+-----------------+
| id | name |
+----+-----------------+
| 3 | パソコン |
| 4 | ノートPC |
| 5 | デスクトップPC |
+----+-----------------+
これが「カテゴリで絞り込むと、その下のカテゴリの商品も出る」の正体です。 アプリ側で「子カテゴリを取る問い合わせ」を深さの分だけ繰り返す書き方をよく見ますが、 深さが変わった瞬間に足りなくなります。 再帰CTEなら、深さがいくつでも1回で済みます。
[スクリーンショット: 04_組織図の再帰.png] インデント付きの組織図(REPEAT を使ったほう)が全部写っている画面を撮ってください。
⑧ 循環参照があると止まらない
ここまでは、データが正しい木であることを前提にしていました。
木ではなくなったら、どうなるか。 実際に壊します。
山田の上司が松本、松本の上司が山田、という2人を入れます。
INSERT INTO d_cte_emp (id, name, dept, manager_id) VALUES
(90, '山田', '営業', 91),
(91, '松本', '営業', 90);
これは異常なデータですが、外部キーでは防げません。
manager_idにd_cte_emp(id)への外部キーを張っても、90 も 91 も実在する社員なので通ります。 外部キーが見ているのは「参照先があるか」だけで、「輪になっていないか」は見ていません。 制約の話はアプリで弾いたはずの値が入る — SQL深掘りの回にまとめてあります。
山田の上司をたどります。 ⑦ の「上へ」と同じSQLです。
WITH RECURSIVE up AS (
SELECT id, name, manager_id, 1 AS lv FROM d_cte_emp WHERE id = 90
UNION ALL
SELECT e.id, e.name, e.manager_id, up.lv + 1
FROM d_cte_emp e JOIN up ON e.id = up.manager_id
)
SELECT lv, name FROM up;
ERROR 3636 (HY000): Recursive query aborted after 1001 iterations. Try increasing @@cte_max_recursion_depth to a larger value.
止まりません。 山田 → 松本 → 山田 → 松本 …… と、永遠に新しい行が出続けます。
⑥ の「止まる条件を書き忘れた」ときと、まったく同じエラーです。ここが怖いところです。
条件は書いてあります。
manager_idがNULLになったら終わる、という条件です。 ただ、そこに永遠に到達しないだけです。 SQL は正しく、データが壊れている。 それでも症状は「SQLが暴走した」として出ます。
直し方1 — 深さで打ち切る(いちばん確実)
「組織が20階層を超えることはない」と言えるなら、そこで切ります。
WITH RECURSIVE up AS (
SELECT id, name, manager_id, 1 AS lv FROM d_cte_emp WHERE id = 90
UNION ALL
SELECT e.id, e.name, e.manager_id, up.lv + 1
FROM d_cte_emp e JOIN up ON e.id = up.manager_id
WHERE up.lv < 20
)
SELECT lv, name FROM up;
+----+--------+
| lv | name |
+----+--------+
| 1 | 山田 |
| 2 | 松本 |
| 3 | 山田 |
| 4 | 松本 |
...
| 20 | 松本 |
+----+--------+
20 rows in set (0.00 sec)
止まりました。 ただし、結果は嘘です。 山田と松本が交互に10回ずつ出ています。
深さの打ち切りは「サーバーを守る」ためのものです。「正しい答えを出す」ためのものではありません。 それでも必ず書いてください。 暴走を止めるのが先で、変な結果が出れば、データがおかしいと気づけます。
lvが上限ちょうどで返ってきたら、循環を疑う —— これが実務での見つけ方です。
直し方2 — 通った経路を覚えておく
「もう通ったIDなら、そこで止める」という書き方もできます。経路の文字列を使います。
WITH RECURSIVE up AS (
SELECT id, name, manager_id, 1 AS lv,
CAST(CONCAT('/', id, '/') AS CHAR(500)) AS visited
FROM d_cte_emp WHERE id = 90
UNION ALL
SELECT e.id, e.name, e.manager_id, up.lv + 1,
CONCAT(up.visited, e.id, '/')
FROM d_cte_emp e
JOIN up ON e.id = up.manager_id
WHERE up.lv < 20
AND LOCATE(CONCAT('/', e.id, '/'), up.visited) = 0
)
SELECT lv, name, visited FROM up;
+----+--------+---------+
| lv | name | visited |
+----+--------+---------+
| 1 | 山田 | /90/ |
| 2 | 松本 | /90/91/ |
+----+--------+---------+
2行で正しく止まりました。 「山田 → 松本 と来て、次に山田へ戻ろうとしたが、もう通っている」ので打ち切られています。
LOCATE(A, B) は「B の中に A が現れる位置」を返し、無ければ 0 を返します。 IDを /90/91/ のようにスラッシュで囲んでつなぐのは、/9/ と /90/ を取り違えないためです。囲まずに 90 だけで探すと、190 や 900 にも一致します。
WHERE up.lv < 20を残してあるのは、保険です。 経路チェックがあれば理屈の上では止まりますが、片方だけに頼りません。 深さの上限は常に置いてください。
PostgreSQL には CYCLE という専用の書き方があります(バージョン14以降)。MySQL 8 にはありません。 上のように自分で書きます。
壊したデータを戻す
DELETE FROM d_cte_emp WHERE id IN (90, 91);
SELECT COUNT(*) FROM d_cte_emp;
+----------+
| COUNT(*) |
+----------+
| 13 |
+----------+
13 に戻っていれば大丈夫です。
循環を、そもそも見つける
入れてしまう前に、既存データに輪が無いかを調べておく手もあります。 全員について、根に到達するかを見ます。
WITH RECURSIVE walk AS (
SELECT id AS start_id, id, manager_id, 1 AS lv FROM d_cte_emp
UNION ALL
SELECT w.start_id, e.id, e.manager_id, w.lv + 1
FROM d_cte_emp e JOIN walk w ON e.id = w.manager_id
WHERE w.lv < 20
)
SELECT start_id, MAX(lv) AS max_lv FROM walk GROUP BY start_id HAVING MAX(lv) >= 20;
0行なら、20階層以内に全員が根へ到達している=循環は無いということです。行が返ってきたら、その start_id から輪に入っています。
⑨ 実用例 — 日付の穴を埋める
再帰CTEの用途は、階層だけではありません。 「無いものを作る」のに使えます。
②で作った d_cte_log を見ます。
SELECT * FROM d_cte_log ORDER BY d;
+------------+-----+
| d | cnt |
+------------+-----+
| 2026-09-01 | 12 |
| 2026-09-02 | 8 |
| 2026-09-05 | 20 |
| 2026-09-06 | 3 |
| 2026-09-09 | 15 |
+------------+-----+
5行しかありません。 これをそのままグラフにすると、9月3日と9月5日が隣同士に並びます。 休んだ日が詰まって、右肩上がりに見えるグラフができあがります。
欲しいのは「9月1日から9月10日まで、10行」です。
そして、その10行はデータベースのどこにもありません。 だから作ります。
WITH RECURSIVE cal AS (
SELECT CAST('2026-09-01' AS DATE) AS d
UNION ALL
SELECT d + INTERVAL 1 DAY FROM cal WHERE d < '2026-09-10'
)
SELECT c.d, COALESCE(l.cnt, 0) AS cnt
FROM cal c
LEFT JOIN d_cte_log l ON l.d = c.d
ORDER BY c.d;
+------------+-----+
| d | cnt |
+------------+-----+
| 2026-09-01 | 12 |
| 2026-09-02 | 8 |
| 2026-09-03 | 0 |
| 2026-09-04 | 0 |
| 2026-09-05 | 20 |
| 2026-09-06 | 3 |
| 2026-09-07 | 0 |
| 2026-09-08 | 0 |
| 2026-09-09 | 15 |
| 2026-09-10 | 0 |
+------------+-----+
10行になりました。
読みどころが3つあります。
| 書き方 | 意味 |
|---|---|
CAST('2026-09-01' AS DATE) | 開始行で型を決めています。 ⑦ と同じ理由で、ここを文字列のままにすると d + INTERVAL 1 DAY の結果が入らないことがあります |
LEFT JOIN | 左(カレンダー)を全部残す。 INNER JOIN にすると、穴が埋まらず元の5行に戻ります |
COALESCE(l.cnt, 0) | 繋がらなかった日の cnt は NULL になる。 それを 0 に置き換えています |
COALESCEで 0 にしてよいかは、その都度考えてください。 「その日は0件だった」なら 0 で正しいです。 「その日は集計が動いていなかった(=不明)」なら、0 にすると嘘になります。NULLのまま出したほうが正直な場面もあります。 この判断は、 FLOATで金額を持つと1円ずれる — SQL深掘りの回で書いた「無いと0は違う」と同じ話です。
開始日と終了日を固定で書きたくないときは、データから取れます。
WITH RECURSIVE cal AS (
SELECT (SELECT MIN(d) FROM d_cte_log) AS d
UNION ALL
SELECT d + INTERVAL 1 DAY FROM cal WHERE d < (SELECT MAX(d) FROM d_cte_log)
)
SELECT COUNT(*) FROM cal;
+----------+
| COUNT(*) |
+----------+
| 9 |
+----------+
9月1日から9月9日まで9行です。データの範囲そのままなので、10日は入りません。
同じ手は、連番でも使えます。 「1番から100番までの席のうち、まだ埋まっていない番号」のような問い合わせが、再帰CTEで連番を作って LEFT JOIN するだけで書けます。
⑩ 木構造の持ち方は、3つある
この記事はずっと manager_id / parent_id という持ち方でやってきました。 これは3つある選択肢の1つです。
再帰CTEが使えない環境(MySQL 5.7 など)や、たどる回数が非常に多い場面では、他の持ち方を選ぶことがあります。
| 持ち方 | 各行が持つもの | 得意なこと | 苦手なこと |
|---|---|---|---|
| 隣接リスト | 親のID1つ(parent_id) | 移動・追加が1行の UPDATE で済む。構造が単純 | たどるのに再帰が要る |
| 経路列挙 | 根からの経路の文字列(/1/2/3/) | 配下の全取得が LIKE '/1/2/%' の1発。再帰が要らない | 枝ごと移動すると、配下全部の文字列を書き換える |
| 閉包テーブル | 「先祖と子孫の全組み合わせ」を別の表に持つ | 上下どちらも JOIN 1回。速い | 行数が増える。追加・移動のたびに関連行を作り直す |
経路列挙は、こういう形です。
-- 例(この記事では作りません)
-- id=7 の path が '/1/2/4/7/' のように入っている
SELECT * FROM cat WHERE path LIKE '/1/2/%';
閉包テーブルは、こういう形です。
-- 例(この記事では作りません)
-- (先祖, 子孫, 距離) を全組み合わせ持つ
-- 社長の配下を全部: SELECT descendant FROM cat_tree WHERE ancestor = 1;
どれを選ぶかは、「たどる回数」と「構造を変える回数」のどちらが多いかで決まります。
| 状況 | 向いているもの |
|---|---|
| MySQL 8 以降が使えて、木がそれほど深くない | 隣接リスト + 再帰CTE。 この記事の形。まずこれで足りる |
| 再帰が使えない。読むほうが圧倒的に多い | 経路列挙(LIKE '前方一致%' は索引が効きます) |
| たどるのが最重要で、構造はめったに変えない | 閉包テーブル |
経路列挙で
LIKE '%...%'(前後に%)を書くと索引が効きません。 効くのは前方一致だけです。この話は本線の1つの表に全部入れると壊れる — テーブル設計と正規化の回でもやりました。そして、どの持ち方にしても「循環を作れる」ことは変わりません。 ⑧ の対策は、形を変えても必要です。
この記事の立場は、はっきりしています。
迷ったら隣接リスト + 再帰CTE。 構造がいちばん単純で、壊れにくいからです。 他の2つは、測って遅かったときに検討するものです。先に複雑にしないでください。
つまずいたところ
書いた順に、実際に詰まった場所を残します。
1. RECURSIVE を書き忘れて、意味の分からないエラーが出た
WITH nums AS (
SELECT 1 AS n UNION ALL SELECT n + 1 FROM nums WHERE n < 10
)
SELECT n FROM nums;
ERROR 1146 (42S02): Table 'appdb.nums' doesn't exist
「テーブルがありません」と言われます。目の前に nums と書いてあるのにです。
RECURSIVE を書かないと、CTEは自分自身を参照できません。 その状態で FROM nums と書いたので、MySQL は「appdb の中の nums というテーブル」を探しに行き、無かったわけです。
エラーメッセージは正しいのに、まったく別の場所を指しています。 再帰を書いていて doesn't exist が出たら、まず RECURSIVE を疑ってください。
2. CTEのカンマを、最後にも付けた
WITH a AS (SELECT 1 AS x),
b AS (SELECT 2 AS y),
SELECT * FROM a, b;
ERROR 1064 (42000): You have an error in your SQL syntax; ... near 'SELECT * FROM a, b' at line 3
b の後ろのカンマが余計です。 「まだCTEが続く」と言ったのに SELECT が来たので、そこで止まっています。
エラーの指す行は SELECT の行ですが、間違っているのは1行上です。SQLの文法エラーは、たいてい「気づいた場所」であって「間違えた場所」ではありません。
3. path の Data too long に30分溶かした
⑦ の ERROR 1406 です。 最初、CONCAT の使い方を間違えたと思って、そこばかり見ていました。
気づけたのは、path を出さずに depth だけ出したら通ったからです。「文字列を作るところだけ落ちる」という差に目が行きました。
再帰CTEでは、列の型が開始行だけで決まります。 2回目以降で長くなるものは、開始行で
CASTして広げておく。 これは文法の問題ではなく、型の問題でした。
4. JOIN の向きを逆にして、2行しか返らなかった
下へたどるつもりで、上へたどる条件を書きました。
WITH RECURSIVE down AS (
SELECT id, name, manager_id FROM d_cte_emp WHERE id = 2
UNION ALL
SELECT e.id, e.name, e.manager_id
FROM d_cte_emp e JOIN down d ON e.id = d.manager_id -- ⚠️ 上へたどる条件
)
SELECT * FROM down;
エラーは出ません。 社長までの2行が返ってきて終わりです。 「配下が0人」と読めてしまいます。
以来、再帰を書いたら必ず声に出して確認するようにしました。
e.manager_id = d.idは「この人を上司に持つ人」=下へ。e.id = d.manager_idは「この人の上司」=上へ。
5. WITH で名前を付けたのに「速くならない」と悩んだ
④ の話です。 CTEは派生表と同じ扱いなので、書き方を変えても計画は変わりません。
時間を使うべきだったのは、EXPLAIN の type と key の列でした。索引の話であって、書き方の話ではありませんでした。
6. cte_max_recursion_depth を上げて「解決」したつもりになった
⑧ の循環参照のときです。 エラーメッセージに「大きくしてみてください」と書いてあるので、素直に上げました。
待ち時間が長くなっただけで、同じエラーが出ました。 当たり前です。循環は、何回回しても終わらないからです。
エラーメッセージの提案は「その方向で正しいこともある」というだけです。
Recursive query abortedが出たら、まず「本当に止まる条件があるか」を疑う。 上限を上げるのは、正当に深いと分かってからです。
7. SET SESSION が効いていないように見えた
EXIT して入り直したあとでした。SET SESSION はその接続の中だけです。
SELECT @@cte_max_recursion_depth;
打ち直せばすぐ分かります。 接続を切ったら 1000 に戻っています。
8. UNION にすれば循環が止まると思った
UNION ALL を UNION に変えると、⑧ の SQL から lv を外した形なら循環は止まります。 同じ行が重複として消えるからです。
ただし、これに頼るのは危険でした。
lvのような「毎回変わる列」を持っていると、行は重複しません。 山田(lv=1) と 山田(lv=3) は別の行なので、UNIONでも消えません。止まりません。
「動いたけど、なぜ動いたか説明できない」状態だったので、⑧ の深さ打ち切りに書き直しました。
後片づけ
この記事で作ったものは、すべて d_cte_ で始まっています。 既存のテーブルには触っていません。
まず、消す対象を目で確認します。
SELECT table_name FROM information_schema.tables
WHERE table_schema = 'appdb' AND table_name LIKE 'd\_cte\_%';
+-------------+
| TABLE_NAME |
+-------------+
| d_cte_cat |
| d_cte_emp |
| d_cte_log |
+-------------+
この3枚だけであることを確認してから消します。
DROP TABLE IF EXISTS d_cte_log;
DROP TABLE IF EXISTS d_cte_cat;
DROP TABLE IF EXISTS d_cte_emp;
残っているものを確認します。
SHOW TABLES;
tasks・usersなど、本線で作ったものがそのまま残っていれば正常です。d_cte_で始まるものが1枚も無ければ、元通りです。
設定も戻しておきます。
SET SESSION cte_max_recursion_depth = 1000;
⑤ で CREATE TEMPORARY TABLE を試した場合も、接続を閉じれば消えます。 気になるなら明示的に消してください。
DROP TEMPORARY TABLE IF EXISTS d_cte_tmp;
抜けます。
EXIT;
DROP TABLEは確認を聞いてきません。 打つ前に、名前がd_cte_で始まっていることを必ず目で見てください。DROP DATABASE appdb;と打ってはいけません。 この記事では、データベースは作っていません。
まとめ — 理解できたこと
CTE は、新しい計算をする道具ではありませんでした。 同じ計算を、読める順番に並べ替える道具でした。
WITH 名前 AS (...)は、サブクエリに名前を付けて上に出しただけ。 書ける内容は派生表とほぼ同じWITHは1回だけ書き、CTEはカンマで並べる。 前に定義したCTEを、次のCTEから使える- 上から下に読める形になる。 日本語で説明する順番と、SQLの並び順が一致する
- 同じサブクエリを2回書かなくて済む。 「片方だけ直して静かに壊す」が起きなくなる
EXPLAINの中で、CTEはDERIVEDとして現れる。 MySQL にとっては派生表と同じもの- だから、速くはならない。 CTEは読みやすさの道具であって、性能改善の道具ではない
- マージされた CTE を2回参照すると、2回実行される。 集約や
GROUP BYを含む CTE はマテリアライズされて1回だけ。自分で決めたいときはMERGE/NO_MERGEヒント - 1回だけ計算する保証が欲しいなら、
CREATE TEMPORARY TABLEで自分で作る - 再帰CTEは3部構成。 開始行 /
UNION ALL/ 自分を参照する部分 - 自分を参照する部分が見ているのは「直前の回で出た行」だけ。 そこが0行になったら終わる
RECURSIVEはWITHの直後に1回だけ。 書き忘れるとTable doesn't existという、まるで別の場所を指すエラーになる- 止まる条件は
WHEREに書くもの。cte_max_recursion_depthは安全装置であって、止め方ではない - 上限を上げてエラーを消すのは、無限ループを長くしただけ
- 上へたどるのは
e.id = 自分.manager_id、下へたどるのはe.manager_id = 自分.id。 逆にしてもエラーは出ない manager_idがNULLだから、上へのたどりは自然に止まる。 終端を 0 で表すと、この性質が失われる- 文字列を積み上げるときは、開始行で
CAST(... AS CHAR(n))。 列の型は開始行だけで決まる - 経路(パス)を作ると、
ORDER BY pathで木の形どおりに並ぶ。ORDER BY depthではそうならない - 循環参照があると、条件を書いても止まらない。 外部キーでは防げない
- 循環対策は「深さで打ち切る」が基本。 経路を覚えて
LOCATEで弾く手もあるが、深さの上限は常に併用する - 深さの打ち切りはサーバーを守るためのもので、正しい答えを出すためのものではない。 上限ちょうどで返ってきたら循環を疑う
- 再帰CTEは「無いものを作る」のにも使える。 日付や連番を生成して
LEFT JOINすれば、歯抜けが埋まる COALESCE(x, 0)を書くかどうかは、「0だった」のか「不明」なのかで決める- 木の持ち方は隣接リスト・経路列挙・閉包テーブルの3つ。 迷ったら隣接リスト + 再帰CTE
一番効いたのは、⑥ で WHERE n < 10 を消して、実際にエラーを出したところでした。 「止め方は自分で書くもの」が、そこで腹に落ちました。再帰の難しさは再帰そのものではなく、止め方です。
この記事のSQLは、CHARACTER SET utf8mb4 を除けば PostgreSQL でもほぼそのまま通ります。 WITH も WITH RECURSIVE もSQL の標準だからです。 2つ目のデータベースを触る話は、本線のMySQLとPostgreSQLの違いは1割 — 移して確かめるにあります。
参考リンク
- roadmap.sh — SQL
- roadmap.sh — Backend
- MySQL 公式ドキュメント — Reference Manual の WITH(Common Table Expressions)の章
- PostgreSQL — WITH Queries — 再帰CTEの説明が丁寧で、
CYCLEの書き方もここにあります - SQLite — WITH clause — 再帰の動きの説明が短くて読みやすい
- 本線: プロセスを止めても消えない場所 — MySQLとSQLの基本
- 本線: 1つの表に全部入れると壊れる — テーブル設計と正規化
- 本線: 速くする前に必ず測る — パフォーマンス改善とEXPLAIN
- 本線: MySQLとPostgreSQLの違いは1割 — 移して確かめる
- 深掘り: GROUP BYだと名前が消える — SQL深掘り
- 深掘り: アプリで弾いたはずの値が入る — SQL深掘り
次に読む
この記事は 【深掘り】SQL の 11 / 17 本目です。
| 記事 | |
|---|---|
| ← 前の記事 | ウィンドウ関数 — GROUP BYだと名前が消える |
| → 次の記事 | ビューとストアドプロシージャ — 列を消したら壊れた |
上から順に読めば積み上がるように並べています。 飛ばしても構いませんが、詰まったら前の回に戻ってください。
全体の順路は はじめに にあります。
roadmap.sh のどこをやっているか
- CTE(WITH)で読めるクエリにする
- 再帰クエリで木構造をたどる
この記事は、上の項目を実際に手を動かして確かめる回です。

コメント