WITH RECURSIVE — 再帰クエリで連番と階層をたどる

本記事で使うデータ — employee テーブル

WITH RECURSIVE は、CTE が 自分自身を呼び出して 行を 1 つずつ作っていく書き方です。

前の結果を次の入力に使って繰り返す この動きを 再帰（recursion） と呼びます。

題材は社員テーブル employee（30 行。

manager_id 列が上司の emp_id を指す自己参照構造）です。

演習に入る前に、本記事で使う employee テーブルの 列定義 と データのサンプル、そして再帰 CTE のアンカーで使う最小の SELECT を確認しておきます。

① SELECT 1; を実行して、1 という値が 1 行だけ返ることを確認してください（再帰 CTE のアンカーで使う最も単純な SELECT です）。

② PRAGMA table_info(employee); で列定義を確認してください。

③ SELECT * FROM employee LIMIT 5; で先頭 5 行のデータをプレビューしてください。manager_id 列に NULL が入る行（上司がいない社員）の入り方も観察してください。

再帰クエリを組み立てる 3 つの部品 — アンカー / 再帰項 / UNION ALL

以下のコードを用いて、3 つの部品 — ① アンカー (SELECT 1) / ② 再帰項 (SELECT n + 1 FROM seq WHERE n < 5) / ③ UNION ALL — を解説します。

-- このセクションの参照コード。3 つの部品 ① / ② / ③ を順に解説する
WITH RECURSIVE seq(n) AS (
  SELECT 1                            -- ① アンカー
  UNION ALL                           -- ③ UNION ALL (アンカーと再帰項の接着剤)
  SELECT n + 1 FROM seq WHERE n < 5   -- ② 再帰項
)
SELECT n FROM seq;

-- 実行結果 (5 行): n=1, 2, 3, 4, 5 が返る

① アンカー — SELECT 1 の部分

アンカー は、最初の 1 行 を作る SELECT です。

参照コードの SELECT 1 が該当 — 自分自身を参照しないふつうの SELECT を 1 回書くだけです。

ここで返した値 (1) が seq(n) の最初の中身となり、続く再帰項の n の初期値として渡されます。

② 再帰項 — SELECT n + 1 FROM seq WHERE n < 5 の部分

-- 参照コード (再掲)。このセクションでは ② 再帰項 の行を解説する
WITH RECURSIVE seq(n) AS (
  SELECT 1                            -- ① アンカー
  UNION ALL                           -- ③ UNION ALL (アンカーと再帰項の接着剤)
  SELECT n + 1 FROM seq WHERE n < 5   -- ★ ② 再帰項 ← 今ここを解説 ★
)
SELECT n FROM seq;

WITH RECURSIVE seq(n) の seq(n) は CTE の名前 (seq) と列名 (n) の宣言 で、最初は アンカーの 1、その後は 再帰項の実行結果 が格納されます。

SELECT n + 1 は毎回「直前の値 + 1」を意味します。

初期値の n は 1 なので次の n + 1 は 2、次は 3 となり、WHERE で値が返されなくなるまでこれが続きます。

③ UNION ALL — 上のコードの `UNION ALL` 部分

-- 参照コード (再掲)。今回解説するのは ③ UNION ALL の行
WITH RECURSIVE seq(n) AS (
  SELECT 1                            -- アンカー
  UNION ALL                           -- ★ 解説対象 ★ 見た目は 1 行だけだが…
  SELECT n + 1 FROM seq WHERE n < 5   -- 再帰項
)
SELECT n FROM seq;

-- …DBMS の中では「再帰項が出した行を、反復のたびに 1 回ずつ UNION ALL で積み足す」と読み替えられる:

SELECT 1            -- アンカー (1 回だけ実行)
UNION ALL           -- ← 1 回目の積み足し: 反復 1 回目の出力を積む
SELECT 2
UNION ALL           -- ← 2 回目の積み足し
SELECT 3
UNION ALL           -- ← 3 回目の積み足し
SELECT 4
UNION ALL           -- ← 4 回目の積み足し
SELECT 5;

-- 結果はどちらも [1, 2, 3, 4, 5]
-- つまり WITH RECURSIVE の中の "UNION ALL" 1 行 ≒ 再帰回数ぶんの UNION ALL の繰り返し

UNION ALL は 2 つ以上の SELECT 結果を縦に積む 演算子です。

UNION（重複を除く）でなく UNION ALL（全部残す）を使うのは、反復で生まれた行を 1 つ残らず結果に取り込むため です。

実例 1 — 連番を作る (1 から 5 まで)

「テーブルに無い 1 から 10 までの連番を、その場で行として生成したい」という要件を想定します。マスタに連番テーブルが無くても、再帰 CTE で作れます。（正しく実行できれば解説が表示されます）

① WITH RECURSIVE で連番用の CTE を定義してください。アンカーは最初の値 1 を返す SELECT にしてください。

② 再帰項では CTE 自身を参照し、直前の値に 1 を足した値を返してください。終了条件は「現在の値が 10 未満の間だけ続ける」としてください。

③ 本体で生成した連番を昇順で取り出してください。

実例 2 — 給与の昇給予測 (毎年 5% 昇給で N 年後の給与は?)

実例 1 は抽象的な数値の連番でしたが、ここでは 実テーブル employee のデータを起点にした実用的な再帰 を扱います。

Sam (emp_id=19) の現在の給与から、毎年 5% 上がるとしたら 5 年後にいくらになるか を予測してみます。

再帰項を projected * 1.05 にするだけで、複利的に増えていく系列が作れます。

-- 給与の昇給予測: Sam (emp_id=19) の給与が毎年 5% 上がるとしたら N 年後はいくら?
WITH RECURSIVE salary_growth(year, projected) AS (
  SELECT 0, salary                                     -- アンカー: 現在の給与を employee から取る
  FROM employee WHERE emp_id = 19
  UNION ALL
  SELECT year + 1, projected * 1.05                    -- 再帰項: 毎年 5% 昇給
  FROM salary_growth
  WHERE year < 5
)
SELECT year, ROUND(projected) AS projected_salary
FROM salary_growth;

-- 実行結果 (6 行): Sam の現在の給与 4100000 から 5 年分を予測
--  year | projected_salary
--  -----+-----------------
--    0  | 4100000   ← アンカー (employee.salary をそのまま)
--    1  | 4305000   ← 再帰項 (4100000 × 1.05)
--    2  | 4520250   ← 再帰項 (4305000 × 1.05)
--    3  | 4746263   ← 再帰項 (4520250 × 1.05、ROUND 適用)
--    4  | 4983576   ← 再帰項 (4746262.5 × 1.05、ROUND 適用)
--    5  | 5232754   ← 再帰項。次は year=5 で WHERE year<5 が偽 → 停止

連番との違いは 2 点です。

① アンカーが employee テーブルから値を引いている こと (SELECT 0, salary FROM employee WHERE emp_id = 19) — 実データを起点にできます。

② 2 列の CTE (year と projected) を扱い、再帰項では両方の列を year + 1 と projected * 1.05 で同時に更新しています。

アンカーで実テーブルから値を引いてくれば、その値を起点にしたシミュレーション (複利計算、人口予測、目標達成までのステップ数など) が書けるようになります。

「Bob (emp_id=2) の現在の給与から、毎年 10% 昇給 したら 5 年後はいくらになるか予測したい」という要件を想定します。（正しく実行できれば解説が表示されます）

① WITH RECURSIVE の CTE 名は growth、列名は year と projected で定義してください。

② アンカーは employee から emp_id = 2 の salary を取り出し、year は 0 から始めてください。

③ 再帰項は year + 1 と projected * 1.10 を返し、終了条件は year < 5 にしてください。

④ 本体で year と ROUND(projected) AS projected_salary を昇順で取り出してください。

実例 3 — JOIN で使用する (manager_id の連鎖を 1 段ずつ)

次は再帰 CTE のもう 1 つの典型用途、自己参照テーブルの階層探索 を扱います。

employee.manager_id は 「自分の上司の emp_id」 を保持しており、これを 1 段ずつたどれば社員 → 上司 → さらに上の上司、と組織図を上向きに歩けます。

連番では n+1 で次の値を作りましたが、ここでは JOIN で次の行（上司）を取り出す ことで連鎖を進めます。

-- 階層をたどる: 起点社員 (emp_id=19, Sam) から上司を上にさかのぼる
WITH RECURSIVE chain AS (
  SELECT emp_id, name, manager_id    -- ① アンカー: 起点の 1 行
  FROM employee WHERE emp_id = 19
  UNION ALL                          -- ③ UNION ALL
  SELECT e.emp_id, e.name, e.manager_id
  FROM employee e
  JOIN chain c ON e.emp_id = c.manager_id    -- ② 再帰項: 結合した行を取り出す
)
SELECT emp_id, name, manager_id FROM chain;

-- 実行結果 (2 行):
--  emp_id | name | manager_id
--  -------+------+-----------
--    19   | Sam  |    2        ← アンカー (起点)
--     2   | Bob  |  NULL       ← 再帰項で結合した行、manager_id が NULL なので次は停止

① アンカー (SELECT ... FROM employee WHERE emp_id = 19) は 起点となる 1 行 を取り出します。

ここでは emp_id=19（Sam）の行を chain の最初の中身として入れます。

なお WITH RECURSIVE chain AS (...) には chain(...) のような 列名宣言を書いていません。これはアンカーの SELECT で employee の列により 自然な列名（emp_id / name / manager_id）がすでに付く ためで、省略しても読みやすい時は省略します。

② 再帰項 (JOIN chain c ON e.emp_id = c.manager_id) は employee と直前の chain を結合 し、結合した行を取り出して chain に格納 します。

結合相手の manager_id が NULL になると、次の反復では結合相手が見つからず 0 行になり、再帰は自然に停止します。

（連番の WHERE n < 5 のような明示的な終了条件は不要 — 階層の終端そのものが終了条件になります）

「emp_id = 19（Sam）を起点に、その上司、さらにその上司…と、manager_id の連鎖を上にたどって役職の系列を出したい」という要件を想定します。

① WITH RECURSIVE で階層用の CTE を定義してください。アンカーは employee から emp_id = 19 の 1 行 (emp_id、name、manager_id) を取り出してください。

② 再帰項では employee と CTE を「employee.emp_id = CTE の manager_id」で結合し、上司の行 (emp_id、name、manager_id) を 1 段ずつ取り出してください。

③ 本体で emp_id、name、manager_id を取り出してください。

実践 3 は 下から上 に上司をたどりました。

今度は逆向きに、「emp_id = 1（Alice）を起点に、Alice の部下、さらにその部下…と、manager_id の連鎖を 下にたどって自分のチーム全員 を出したい」という要件を想定します。

① WITH RECURSIVE で部下用の CTE を定義してください。アンカーは employee から emp_id = 1 の 1 行 (emp_id、name、manager_id) を取り出してください。

② 再帰項では employee と CTE を「employee.manager_id = CTE の emp_id」で結合し、いまの行の部下 (emp_id、name、manager_id) を 1 段ずつ取り出してください。

③ 本体で emp_id、name、manager_id を、emp_id の昇順で取り出してください。

実例 4 — 複数行を一度に取得する再帰 (組織全体のツリー)

実例 3 は Sam → Bob → 停止 と 1 行ずつ 進む例でした。

ここでは アンカーや再帰項の出力が複数行になる ケースを扱います。

「役員 5 名 (上司を持たない社員) を起点に、組織全体 30 名を一度に取得する」シナリオで、再帰クエリが 幅優先で複数の枝を並行にたどる 動きを確認します。

-- 全役員 (上司を持たない社員) を起点に、組織配下の全員を再帰的に取得
WITH RECURSIVE org_tree AS (
  -- ① アンカー: manager_id が NULL の 5 名 (一度に複数行を返す)
  SELECT emp_id, name, manager_id
  FROM employee
  WHERE manager_id IS NULL
  UNION ALL
  -- ② 再帰項: 直前の行に対する直属部下を取得 (これも複数行になる)
  SELECT e.emp_id, e.name, e.manager_id
  FROM employee e
  JOIN org_tree t ON e.manager_id = t.emp_id
)
SELECT emp_id, name, manager_id FROM org_tree
ORDER BY emp_id;

-- 実行結果 (30 行 = 全社員):
--   アンカー (5 行): emp_id=1 Alice, 2 Bob, 3 Carol, 9 Ivan, 28 Brian
--   再帰項 1 回目 (25 行): Alice の部下 9 名 + Bob の部下 8 名 + Carol の部下 8 名
--   再帰項 2 回目 (0 行): 部下たちはさらに下を持たない → 停止
--   合計: 5 + 25 = 30 行

実例 3 との違いは 2 点です。

① アンカーが複数行 (5 行) を一度に返す — WHERE manager_id IS NULL の条件に合う行が全て初期化に使われます。

② 再帰項が複数の枝を並行にたどる — org_tree に 5 名が入った状態で再帰項を 1 回実行すると、それぞれの部下を 同じ反復の中で同時に 取得します (Alice の部下 9 + Bob の部下 8 + Carol の部下 8 = 25 行)。

つまり再帰クエリは「1 行ずつ深く潜る」だけでなく「複数の枝を幅優先で並行に展開する」動きもできます。

「Bob (emp_id=2) と Carol (emp_id=3) の 2 名を 同時に 起点として、それぞれの配下を一度に取得したい」という要件を想定します。（正しく実行できれば解説が表示されます）

① WITH RECURSIVE の CTE 名は team、列名は省略してください (employee の自然な列名を使う)。

② アンカーは employee から emp_id が 2 または 3 の 2 行 を取り出してください。

③ 再帰項は employee と CTE を「employee.manager_id = CTE の emp_id」で結合し、配下の社員を取り出してください。

④ 本体で emp_id、name、manager_id を emp_id の昇順で取り出してください。

Tips — なぜ WITH RECURSIVE を使うのか?

WITH RECURSIVE が活きる 3 つの場面

再帰クエリは 手書きの SQL では書けない / 大変な場面 に使います。主な用途は 3 つ。

① 階層をたどる — 組織図、フォルダ、部品表など、深さが分からない自己参照構造 に。手書きの JOIN を何段も並べる代わりに、データの終端で自然に止まる形で書けます。

② 大量の行を生成 — 連番、連続日付、カレンダーなど、UNION ALL を 100 回手書きする代わりに WHERE の 1 行で済みます。

③ アプリのループを SQL に寄せる — 親 ID をたどるループをアプリで書くと N+1 クエリ問題 が出ますが、再帰クエリなら 1 クエリで完結します。

普通の JOIN や GROUP BY で書けるなら使う必要はありません。上の 3 場面に当てはまるときの第一選択肢です。