ガイド

階層構造を扱う — 隣接リスト / 再帰CTE / 経路列挙 / 入れ子集合

カテゴリツリーや組織図など、階層データを保存・検索したいとき。

問題 — 木構造は SQL の鬼門

カテゴリの親子、組織図、コメントのスレッド…階層（木構造）はどのアプリでも出てきますが、RDB のテーブル1枚で木を表現し、任意の深さの子孫をまとめて取得するのは素朴には難しいテーマです。

代表的なモデリング3種 — 隣接リスト / 経路列挙 / 入れ子集合 — と、それぞれに向くクエリを押さえておくと、要件に応じた選択ができます。

解法1: 隣接リストモデル + 再帰CTE (王道)

もっともシンプル。各行に parent_id を持たせ、NULL なら根。更新はラク・読みは再帰CTEで、が基本方針。

再帰CTE（WITH RECURSIVE）は PostgreSQL / SQL Server / MySQL 8+ / SQLite 3.8.3+ / Oracle 11gR2+ で使え、現代ではほぼ全RDBMSで使えます。

sql

-- ある category の全子孫を一発で取る
WITH RECURSIVE tree AS (
  SELECT id, parent_id, name, 1 AS depth
  FROM categories
  WHERE id = 10  -- 起点
  UNION ALL
  SELECT c.id, c.parent_id, c.name, t.depth + 1
  FROM categories c
  JOIN tree t ON c.parent_id = t.id
)
SELECT * FROM tree ORDER BY depth, id;

解法2: 経路列挙モデル (materialized path)

各行に path 列を持たせ、'/1/5/10/' のように根から自分までのIDを連結して保存する方式。子孫検索は LIKE '/1/5/%' で一発、再帰不要で速い。

弱点は親を変えたときに自分+全子孫の path を書き換える必要があること。更新頻度が低い / ほぼ読み取りのツリーに向いています。PostgreSQL には専用の ltree 型もあり、これが整備版。

sql

CREATE TABLE categories (
  id INT PRIMARY KEY,
  name TEXT,
  path TEXT  -- '/1/5/10/'
);

-- id=5 の全子孫
SELECT * FROM categories WHERE path LIKE '/1/5/%';

解法3: 入れ子集合モデル (nested sets)

各ノードに lft / rgt の数値を持たせ、「自分の左右の間にあるノードが全子孫」と表現する古典的モデル。読みは超高速（ただの範囲検索）だが、挿入・移動で全ノードの lft/rgt を更新する必要があり書き込み負荷が高い。

読み取り中心のカテゴリマスタや書籍の章立てなど、ほぼ静的な木に向きます。動的なツリーには不向き。

選び方

迷ったら隣接リスト + 再帰CTE。書きやすく更新もラク。現代のRDBMSなら十分高速
読みが極端に多く、移動がほぼない → 経路列挙（PostgreSQL なら ltree）
完全に静的で読みが超重要 → 入れ子集合

注意点 — 循環参照と深さ制限

再帰CTEで循環参照（A→B→A のようなデータ不整合）があると無限ループします。PostgreSQL なら CYCLE 句で検出できるほか、CTE 内に depth を持たせて一定深さで打ち切るのが実務的な防御策。

また、SQL Server のデフォルトは再帰最大深度 100。深い木では OPTION (MAXRECURSION N) で明示。MySQL にも cte_max_recursion_depth システム変数があります。