再帰CTE：それって何？なんで必要なの？

今日はもう一歩進んで、再帰のマジックに挑戦しよう。もしPythonみたいな再帰をサポートする言語でプログラミングしたことがあれば、なんとなくイメージできるかも。でも「なんか難しそう…」って思っても大丈夫、めっちゃ丁寧に説明するから安心して！

再帰CTEは、会社の組織図や家系図、ファイルディレクトリみたいな階層・ツリー構造のデータを扱うための強力なツールなんだ。

簡単に言うと、「自分自身を呼び出せる」SQL式で、データの全レベルを順番に辿って処理できるってこと。

再帰CTEの主なポイント：

1. WITH RECURSIVEキーワードを使う。
2. 再帰CTEは2つのパートでできてる：
   • ベースクエリ：再帰のスタート地点（「ルート」）を決める部分。
   • 再帰クエリ：前のステップの結果を使って、残りのデータを処理する部分。

再帰CTEの動きは、階段を上るのに似てるよ：

• まず最初の一段目に乗る（これがベースクエリ）。
• 次に一段上がるとき、前の段の結果を使う（再帰クエリ）。
• このプロセスを、階段がなくなる（終了条件に達する）まで繰り返す。

再帰CTEの構文

まずはテンプレ的な例を見てみよう：

WITH RECURSIVE cte_name AS (
    -- ベースクエリ
    SELECT column1, column2
    FROM table_name
    WHERE condition_for_base_case

    UNION ALL

    -- 再帰クエリ
    SELECT column1, column2
    FROM table_name
    JOIN cte_name ON some_condition
    WHERE stop_condition
)
SELECT * FROM cte_name;

再帰CTEでのUNIONとUNION ALLの役割

どの再帰CTEも、ベース部分と再帰部分の間にUNIONかUNION ALLのどちらかの演算子を必ず使う必要があるよ。

どっちを選ぶ？UNIONかUNION ALL？

迷ったら、ほぼいつもUNION ALLを選んでOK。なぜかというと、こっちの方が速いから！ただ結果を結合するだけで、重複チェックしない。つまり計算もリソースも少なくて済むし、結果も早い。

特に再帰CTEでは重要。例えばコメントツリーや会社の部下構造を作るとき、UNION ALLがほぼ必須。もしUNIONを使うと、DBが「このステップはもうやった」と勘違いして、結果の一部を切り捨てちゃうことがある。そうなるとロジックが壊れる。

重複が本当に邪魔で消したい場合だけUNIONを使ってOK。でもこれは「綺麗さ」と「速さ」のトレードオフだって覚えておこう。

アプローチの違い例

-- UNION: 重複は除外される
SELECT 'A'
UNION
SELECT 'A';     -- 結果: 1行 'A'

-- UNION ALL: 重複も残る
SELECT 'A'
UNION ALL
SELECT 'A';     -- 結果: 2行 'A'

再帰クエリでは、構造を辿るとき大事なステップを失わないためにも、基本的にUNION ALLを使うのが安全だよ。

よくある課題を見てみよう：社員テーブルがあって、カラムはemployee_id、manager_id、name。社長（manager_id = NULLの人）から階層を作りたい。

例えば社員テーブル：employees

誰が誰の部下か、各社員の階層レベルを知りたいときに便利。例えば社員ツリーをUIで表示したり、組織構造のレポートを作るときに使えるよ。

WITH RECURSIVE employee_hierarchy AS (
    -- 上司がいない人からスタート
    SELECT
        employee_id,
        name,
        manager_id,
        1 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- 部下を追加してレベルを増やす
    SELECT
        e.employee_id,
        e.name,
        e.manager_id,
        eh.level + 1
    FROM employees e
    INNER JOIN employee_hierarchy eh
    ON e.manager_id = eh.employee_id
)
SELECT * FROM employee_hierarchy;

結果はこんな感じ：

このクエリで、社員の階層を「社長から一番下の人まで」辿れるのがよくわかる。levelはツリーの表示やフォーマットに便利だよ。

例：商品カテゴリ

今度は、商品カテゴリのテーブルがあって、各カテゴリがサブカテゴリを持てる場合を考えよう。サブカテゴリのさらにサブカテゴリ…みたいなツリーをどうやって作る？

テーブルcategories

再帰クエリ：

WITH RECURSIVE category_tree AS (
    -- ベースケース：ルートカテゴリを探す
    SELECT
        category_id,
        name,
        parent_id,
        1 AS depth
    FROM categories
    WHERE parent_id IS NULL

    UNION ALL

    -- 再帰部分：今のカテゴリのサブカテゴリを探す
    SELECT
        c.category_id,
        c.name,
        c.parent_id,
        ct.depth + 1
    FROM categories c
    INNER JOIN category_tree ct
    ON c.parent_id = ct.category_id
)
SELECT * FROM category_tree;

結果：

これでカテゴリのツリーと階層レベルが見えるね。

なぜ再帰CTEはイケてるのか？

再帰CTEは、SQLの中でも超表現力が高くてパワフルなツール。複雑なネストロジックを書かなくても、「どこから始めるか（ベースケース）」と「どう進めるか（再帰部分）」だけ書けば、あとはPostgreSQLが全部やってくれる。

よく使うのは、社員や商品カテゴリ、ディレクトリ構造、SNSのグラフなどの階層を辿るとき。テーブルに新しいデータが増えても、クエリは自動でそれを拾ってくれるから、拡張性も抜群。

でも注意点もある。終了条件をちゃんと書かないと、クエリが無限ループしちゃう。大きなテーブルではインデックスも大事。UNION ALLは階層系のタスクではほぼ必須、でないと重複削除で再帰のステップが消えてしまうリスクがあるよ。

うまく設計した再帰CTEなら、複雑なビジネスロジックも数行で表現できる。手続きやループ、追加コードなしでOK。SQLが「正しくて美しい」って思える瞬間だね。

再帰CTEでありがちなミス

• 無限再帰：ちゃんとした終了条件（WHERE）を書かないと、クエリがループし続ける。
• データの重複：UNION ALLの使い方を間違えると、重複データが増える。
• パフォーマンス：大量データだと再帰クエリは重くなりがち。キーとなるカラム（例：manager_id）にインデックスを貼ると速くなる。

再帰クエリが必須なとき

「再帰クエリって理論だけでしょ？」って思うかもだけど、実は日常開発でめっちゃ使う。例えば：

• 会社の組織や商品カテゴリのレポートを作るとき
• フォルダツリーを辿って、全部のサブディレクトリをリストアップしたいとき
• グラフ解析（SNSのつながり、ルート、タスク依存関係など）
• 複雑なオブジェクト間の関係を見やすく表現したいとき

「何かが何かに依存している」構造を辿りたいなら、WITH RECURSIVEがほぼ必須だよ！