再帰CTE(WITH RECURSIVE)方式 vs. 多重LEFT OUTER JOIN方式

現場で階層データ（ツリー構造）をSQLで扱う場合、再帰CTE（WITH RECURSIVE）とLEFT OUTER JOIN（自己結合）の2つの代表的なアプローチがあります。本記事では、両者のクエリ例と結果イメージ、メリット・デメリットを整理します。

ルート (id=1)
├─ 子A (id=2)
│    └─ 葉ノードA (id=4)
└─ 子B (id=3)

---

1. 再帰CTE（WITH RECURSIVE）方式

再帰CTEは、自己参照テーブル（例：**parent_id**カラムを持つカテゴリテーブル）の階層関係を可変な深さでたどり、ツリー構造を簡潔に取得できるのが特長です。

サンプルクエリ

WITH RECURSIVE node_tree AS (
  SELECT
    id,
    parent_id,
    name,
    sort_order,
    depth
  FROM
    category
  WHERE
    parent_id IS NULL

  UNION ALL

  SELECT
    c.id,
    c.parent_id,
    c.name,
    c.sort_order,
    c.depth
  FROM
    category c
    INNER JOIN node_tree nt ON c.parent_id = nt.id
)
SELECT
  id,
  parent_id,
  name,
  sort_order,
  depth
FROM
  node_tree
ORDER BY
  depth, sort_order;

期待される結果例

id	parent_id	name	sort_order	depth
1	NULL	ルート	1	1
2	1	子A	1	2
3	1	子B	2	2
4	2	葉ノードA	1	3

---

2. 多重LEFT OUTER JOIN方式

階層の数が決まっている場合（たとえば「4段階まで」など）、LEFT OUTER JOINで自己結合を多重に重ねることで疑似的にツリー構造を1クエリで表現できます。

サンプルクエリ

SELECT
  c1.id   AS lv1_id, c1.name   AS lv1_name,
  c2.id   AS lv2_id, c2.name   AS lv2_name,
  c3.id   AS lv3_id, c3.name   AS lv3_name
FROM
  category c1
  LEFT OUTER JOIN category c2 ON c2.parent_id = c1.id
  LEFT OUTER JOIN category c3 ON c3.parent_id = c2.id
WHERE
  c1.parent_id IS NULL
ORDER BY
  c1.sort_order, c2.sort_order, c3.sort_order;

期待される結果例

lv1_id	lv1_name	lv2_id	lv2_name	lv3_id	lv3_name
1	ルート	2	子A	4	葉ノードA
1	ルート	3	子B	NULL	NULL

---

両者の比較まとめ

項目	WITH RECURSIVE方式	LEFT OUTER JOIN方式
階層上限	無制限（深さに制限なし）	設定した分のみ（管理しやすいのは4~5段まで）
クエリの可読性	慣れないとやや複雑	階層深くなるとJOIN数で冗長に
柔軟性	階層の増減に強い	階層構造の変動に弱い
パフォーマンス	深い/大きなツリーは負荷大	JOIN数が増えるとパフォーマンス悪化
主な用途	組織図、カテゴリーツリー、無限階層メニューなど	段数が決まっているカテゴリ表示

---

まとめ

• 再帰CTEは、階層の深さが未定・動的なケースや「全件検索」に有効で、構造の変化にも柔軟に追従できます。
• *多重自己結合（LEFT OUTER JOIN）**は階層が限定的かつ一覧に横並びで出したいケースに有利ですが、拡張性は低いです。