組織図や商品カテゴリのように「親が子を持ち、子がさらに孫を持つ」階層データは、JOIN を何重に重ねても取得できる深さが固定されてしまいます。MySQL 8.0 以降の WITH RECURSIVE（再帰 CTE）を使うと、何段に重なる階層でも 1 本の SQL で取得できます。この記事では、アンカーメンバーと再帰メンバーの役割から始め、社員テーブルを使った組織図の全階層取得、カテゴリツリーへの応用、そして無限ループを防ぐ注意点まで、実例を交えながら順を追って解説します。
このシナリオで考える
次の社員テーブルを使います。manager_id が NULL の行が最上位（社長）で、それ以外は上司の id を参照する形です。「誰の部下か」を 1 列だけで表すこの形式を隣接リストといい、再帰 CTE の典型的な使いどころです。
CREATE TABLE employees (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
manager_id INT NULL,
FOREIGN KEY (manager_id) REFERENCES employees(id)
);
INSERT INTO employees (id, name, manager_id) VALUES
(1, '田中（社長）', NULL),
(2, '鈴木（営業部長）', 1),
(3, '高橋（開発部長）', 1),
(4, '伊藤（営業1課）', 2),
(5, '佐藤（営業2課）', 2),
(6, '山田（フロントエンド）', 3),
(7, '中村（バックエンド）', 3),
(8, '小林（テスト）', 7);田中社長を頂点に、部長→課員→担当という 4 階層の組織です。小林さんは中村さんの部下なので、田中→高橋→中村→小林と 3 段下にいます。このような可変深さの構造を通常の JOIN で取るには、階層の最大数だけ LEFT JOIN を書く必要があり、深さが変わるたびにクエリを書き直さなければなりません。
WITH RECURSIVEの基本構文
WITH RECURSIVE の骨格は次のとおりです。
WITH RECURSIVE cte_name AS (
-- アンカーメンバー: 再帰の出発点となる行を返す
SELECT ...
UNION ALL
-- 再帰メンバー: cte_name 自身を JOIN して 1 段深い行を返す
SELECT ...
FROM source_table
JOIN cte_name ON ...
)
SELECT * FROM cte_name;2 つの部分がそれぞれ役割を持ちます。
- アンカーメンバー：再帰の起点となる行を返します。「ルートノード」や「特定の開始点」を返す通常の SELECT です。再帰はここから始まります。
- 再帰メンバー：CTE 自身（
cte_name）を JOIN することで、前のステップで取得した行から 1 段深い子を追加します。MySQL はアンカーの結果を初期セットとして持ち、再帰メンバーを繰り返し実行します。新しく追加される行が 0 件になった時点で再帰が自動終了します。
UNION ALL と UNION の使い分けは次のとおりです。通常のツリー取得では重複チェックが不要なため、UNION ALL を選びます。
| 書き方 | 重複排除 | 速度 | 使いどころ |
|---|---|---|---|
| UNION ALL | なし | 速い | 通常のツリー取得（ほぼこちら） |
| UNION | あり | 遅い | 循環データで安全に止めたいとき |
組織図の全階層を取得する
田中社長（id = 1）配下の全社員を depth（深さ）と path（経路文字列）つきで取得するクエリです。
WITH RECURSIVE org AS (
-- アンカー: 社長（ルート）を起点にする
SELECT
id,
name,
manager_id,
0 AS depth,
CAST(name AS CHAR(500)) AS path
FROM employees
WHERE manager_id IS NULL
UNION ALL
-- 再帰: 1 段下の部下を結合する
SELECT
e.id,
e.name,
e.manager_id,
org.depth + 1,
CONCAT(org.path, ' > ', e.name)
FROM employees e
JOIN org ON e.manager_id = org.id
)
SELECT
id,
CONCAT(REPEAT(' ', depth), name) AS name_indented,
depth,
path
FROM org
ORDER BY path;実行結果は次のようになります。
+----+-----------------------------------+-------+
| id | name_indented | depth |
+----+-----------------------------------+-------+
| 1 | 田中（社長） | 0 |
| 2 | 鈴木（営業部長） | 1 |
| 4 | 伊藤（営業1課） | 2 |
| 5 | 佐藤（営業2課） | 2 |
| 3 | 高橋（開発部長） | 1 |
| 6 | 山田（フロントエンド） | 2 |
| 7 | 中村（バックエンド） | 2 |
| 8 | 小林（テスト） | 3 |
+----+-----------------------------------+-------+depth は 0 がルートで 1 段下がるごとに増えます。REPEAT(' ', depth) でスペースをインデントとして挿入しているので、ツリー構造がそのまま見えます。path 列でソートすると、同じ親に属するノードが隣に並ぶため視認性が上がります。
特定の部門配下だけを取得したいときは、アンカーの WHERE を書き換えるだけです。
-- 高橋開発部長（id = 3）の配下のみ
WITH RECURSIVE org AS (
SELECT id, name, manager_id, 0 AS depth
FROM employees
WHERE id = 3 -- 開始ノードを変える
UNION ALL
SELECT e.id, e.name, e.manager_id, org.depth + 1
FROM employees e
JOIN org ON e.manager_id = org.id
)
SELECT id, CONCAT(REPEAT(' ', depth), name) AS name_indented, depth
FROM org
ORDER BY depth, id;カテゴリツリーへの応用
EC サイトのカテゴリのように、商品を多階層で管理する構造でも同じパターンが使えます。manager_id を parent_id に読み替えるだけで、クエリの形はほぼそのままです。
CREATE TABLE categories (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
parent_id INT NULL,
FOREIGN KEY (parent_id) REFERENCES categories(id)
);
INSERT INTO categories (id, name, parent_id) VALUES
(1, '衣服', NULL),
(2, 'メンズ', 1),
(3, 'レディース', 1),
(4, 'Tシャツ', 2),
(5, 'ジャケット', 2),
(6, 'ワンピース', 3),
(7, 'アウター', 3),
(8, 'ダウン', 7);「衣服」（id = 1）配下の全カテゴリを深さつきで取得します。
WITH RECURSIVE cat_tree AS (
SELECT id, name, parent_id, 0 AS depth
FROM categories
WHERE id = 1
UNION ALL
SELECT c.id, c.name, c.parent_id, ct.depth + 1
FROM categories c
JOIN cat_tree ct ON c.parent_id = ct.id
)
SELECT
id,
CONCAT(REPEAT(' ', depth), name) AS name_tree,
depth
FROM cat_tree
ORDER BY depth, id;+----+--------------------+-------+
| id | name_tree | depth |
+----+--------------------+-------+
| 1 | 衣服 | 0 |
| 2 | メンズ | 1 |
| 3 | レディース | 1 |
| 4 | Tシャツ | 2 |
| 5 | ジャケット | 2 |
| 6 | ワンピース | 2 |
| 7 | アウター | 2 |
| 8 | ダウン | 3 |
+----+--------------------+-------+子孫を下に向かってたどるのとは逆に、「特定のカテゴリから根までの祖先をたどる（パンくずリスト）」場合は、再帰メンバーの JOIN を逆向きにします。
-- 「ダウン」（id = 8）から根までの祖先を逆向きにたどる
WITH RECURSIVE ancestors AS (
SELECT id, name, parent_id FROM categories WHERE id = 8
UNION ALL
SELECT c.id, c.name, c.parent_id
FROM categories c
JOIN ancestors a ON c.id = a.parent_id -- 逆方向
)
SELECT * FROM ancestors;+----+----------+-----------+
| id | name | parent_id |
+----+----------+-----------+
| 8 | ダウン | 7 |
| 7 | アウター | 3 |
| 3 | レディース| 1 |
| 1 | 衣服 | NULL |
+----+----------+-----------+下から上にたどることで、パンくずリストの元データをそのまま取得できます。子孫取得と祖先取得のどちらも、JOIN の向きを変えるだけで対応できる点が再帰 CTE の柔軟さです。
よくある落とし穴と注意点
無限ループとcte_max_recursion_depth
データに循環参照（A→B→C→A など）が含まれると、再帰メンバーが永遠に新しい行を追加し続けます。MySQL は cte_max_recursion_depth（デフォルト 1000）で再帰回数を制限しており、上限を超えると次のエラーが発生します。
ERROR 3636 (HY000): Recursive query aborted after 1001 iterations.
Try increasing @@cte_max_recursion_depth to a larger value.このエラーは循環検出として機能しますが、正常な深い階層でも上限に引っかかることがあります。その場合はセッション単位で引き上げます。
SET SESSION cte_max_recursion_depth = 10000;path文字列はCAST長を明示する
CONCAT(org.path, ' > ', e.name) のような累積文字列を作る場合、アンカーで CAST(name AS CHAR(500)) のようにキャストしないと、MySQL がアンカー列の宣言長に切り詰めます。想定する最大経路長に合わせた長さを明示するのが安全です。
manager_id にインデックスを張る
再帰メンバーでは e.manager_id = org.id の条件が繰り返し評価されます。manager_id にインデックスがないと、階層が深くなるほどフルスキャンが重なります。外部キー制約を設定すれば MySQL が自動でインデックスを作成しますが、制約なしで列だけ作った場合は手動で追加します。
CREATE INDEX idx_employees_manager ON employees(manager_id);| 注意点 | 症状 | 対処 |
|---|---|---|
| 循環参照 | ERROR 3636（再帰上限超過） | データを修正するか UNION で重複を除く |
| 深い正常階層 | ERROR 3636（上限 1000 に達する） | SET SESSION cte_max_recursion_depth を増やす |
| path 切り詰め | 経路文字列が途中で切れる | アンカーで CAST(… AS CHAR(500)) を指定 |
| インデックスなし | 深い階層でクエリが遅い | parent_id / manager_id にインデックスを追加 |
まとめ
WITH RECURSIVE を使うと、任意の深さを持つ階層データを 1 クエリで取得できます。アンカーメンバーで起点を決め、再帰メンバーで 1 段ずつ掘り下げ、新しい行がなくなれば自動終了するシンプルな仕組みです。組織図・カテゴリツリー・コメントスレッドなど、隣接リスト形式のテーブルを扱う機会があれば、JOIN を何重にも書く代わりに再帰 CTE を試してみてください。
参考リンク
アイキャッチ画像: Photo by Shubham Dhage on Unsplash
