SQL の WITH 句（CTE）入門 - 共通テーブル式の書き方と再帰 CTE で階層データをたどる方法

サブクエリの中にサブクエリが入り、さらにその中にもう1段。集計を重ねるクエリは、内側から外側へ読まないと意味が取れなくなりがちです。WITH 句を使うと、途中の結果に名前を付けて上から順に並べられるので、クエリを「手順」として読めるようになります。この名前付きの途中結果を CTE（Common Table Expression: 共通テーブル式）と呼びます。

WITH 句にはもうひとつ、再帰 CTE（WITH RECURSIVE）という使い方があります。組織図やカテゴリのツリーのように、親子関係を何段たどればよいか事前にわからないデータを、1つのクエリで展開できます。便利な反面、「どの順で評価されるのか」「なぜ止まらなくなるのか」が見えにくい機能でもあります。

この記事では、小さなサンプルテーブルを最初に定義し、すべてのクエリをそのデータに対して実行して結果を並べます。通常の CTE の書き方から始めて、再帰 CTE の評価の進み方、階層データの展開、無限ループの防ぎ方、性能面の注意までを順に確認します。

CTE とは - クエリの途中結果に名前を付ける

CTE は、1つの SQL 文の中だけで使える、名前付きの問い合わせ結果です。SQLite の公式ドキュメントは、CTE を「1つの SQL 文の間だけ存在する一時的なビューのように振る舞うもの」と説明しています。PostgreSQL のドキュメントも、WITH は大きなクエリの中で使う補助的な文を書く方法であり、1つのクエリの間だけ存在する一時テーブルを定義するものと考えられる、としています。

WITH CTEの名前 AS (
  SELECT ...          -- 途中結果を作る問い合わせ
)
SELECT ...
FROM CTEの名前;       -- 本体のクエリからテーブルのように参照する

WITH に続けて名前と問い合わせを書き、その後ろの本体のクエリから、テーブルと同じように FROM や JOIN で参照します。

サンプルデータ

以降の例はすべて次のデータを使います。サンプル商事（仮）の社員と、その受注の記録です。

CREATE TABLE employees (
  id         INTEGER PRIMARY KEY,
  name       TEXT NOT NULL,
  dept       TEXT NOT NULL,
  manager_id INTEGER REFERENCES employees(id)   -- 上司（いなければ NULL）
);
 
INSERT INTO employees VALUES
  (1, '社長A', '経営', NULL),
  (2, '部長A', '営業部', 1), (3, '部長B', '開発部', 1),
  (4, '課長A', '営業部', 2), (5, '課長B', '開発部', 3),
  (6, '社員A', '営業部', 4), (7, '社員B', '営業部', 4),
  (8, '社員C', '開発部', 5);
 
CREATE TABLE orders (
  id          INTEGER PRIMARY KEY,
  employee_id INTEGER NOT NULL REFERENCES employees(id),
  ordered_on  TEXT NOT NULL,
  amount      INTEGER NOT NULL
);
 
INSERT INTO orders VALUES
  (1, 6, '2026-11-01', 300), (2, 7, '2026-11-01', 200),
  (3, 6, '2026-11-02', 500), (4, 4, '2026-11-04', 400),
  (5, 8, '2026-11-04', 600), (6, 7, '2026-11-07', 100);

employees は、manager_id で自分自身のテーブルを参照する形になっています。図にすると次のツリーです。

社長A (1)
├── 部長A (2)
│   └── 課長A (4)
│       ├── 社員A (6)
│       └── 社員B (7)
└── 部長B (3)
    └── 課長B (5)
        └── 社員C (8)

ポイントは2つあります。

• employees は階層が4段あり、枝によって人数が違う（再帰 CTE でたどる対象）
• orders は 11月3日・5日・6日の受注がない（日付を生成して穴を埋める例で使う）

基本の書き方 - サブクエリを上に取り出す

社員ごとの受注合計を出し、社員名を付けて並べます。まずはサブクエリで書いた形です。

SELECT e.name, s.total
FROM (
  SELECT employee_id, SUM(amount) AS total
  FROM orders
  GROUP BY employee_id
) AS s
JOIN employees AS e ON e.id = s.employee_id
ORDER BY s.total DESC, e.id;

同じクエリを CTE で書くと、次のようになります。

WITH emp_sales AS (
  SELECT employee_id, SUM(amount) AS total
  FROM orders
  GROUP BY employee_id
)
SELECT e.name, s.total
FROM emp_sales AS s
JOIN employees AS e ON e.id = s.employee_id
ORDER BY s.total DESC, e.id;

結果は同じです。違うのは読む順番で、サブクエリ版は FROM の中に潜ってから外へ戻る必要がありますが、CTE 版は「社員ごとの合計を作る」「それに名前を付けて結合する」と上から順に読めます。emp_sales という名前が、その途中結果が何であるかの説明にもなっています。JOIN の相手が増えるほど、この差は大きくなります。

列名は、CTE の名前の後ろに括弧で指定することもできます。再帰 CTE ではこの書き方をよく使います。

WITH emp_sales(employee_id, total) AS (
  SELECT employee_id, SUM(amount)
  FROM orders
  GROUP BY employee_id
)
SELECT * FROM emp_sales;

サブクエリ・ビュー・一時テーブルとの違い

「途中結果に名前を付ける」手段はほかにもあります。違いは、その名前がどこまで有効かと、データベースに何かを作るかです。

CTE の有効範囲が1つの文だけであることは、実行して確かめられます。次の2文を続けて実行すると、1文目は結果を返しますが、2文目は SQLite 3.54.0 で no such table: x というエラーになりました。

WITH x AS (SELECT 1 AS n)
SELECT n FROM x;     -- 1 を返す
 
SELECT n FROM x;     -- エラー: no such table: x

使い分けの目安は次のとおりです。

• 1つのクエリを読みやすくしたい: CTE。権限もスキーマの変更も要らない
• 複数のクエリや複数の人が同じ定義を使う: ビュー
• 重い途中結果を複数の文で使い回す、インデックスを付けたい: 一時テーブル

なお、CTE の名前は実在するテーブルと同じ名前にもできます。その場合、文の中では CTE のほうが参照されます（SQL Server のドキュメントに明記があり、SQLite 3.54.0 でも employees という名前の CTE が実テーブルより優先されることを確認しました）。意図せず実テーブルを隠してしまうと原因がわかりにくいので、テーブルと重ならない名前を付けるのが無難です。

複数の CTE をつなげる - 段階的な集計

WITH 句には、カンマで区切って複数の CTE を書けます。後ろの CTE は、前に定義した CTE を参照できます。これを使うと、集計を段階ごとに分けて書けます。

「社員ごとの合計」から「部署ごとの合計」を作り、さらに「全体に対する構成比」を出します。

WITH emp_sales AS (               -- 1. 社員ごとの合計
  SELECT employee_id, SUM(amount) AS total
  FROM orders
  GROUP BY employee_id
),
dept_sales AS (                   -- 2. 部署ごとの合計（1 を使う）
  SELECT e.dept,
         SUM(s.total) AS dept_total,
         COUNT(*)     AS sellers
  FROM emp_sales AS s
  JOIN employees AS e ON e.id = s.employee_id
  GROUP BY e.dept
),
grand AS (                        -- 3. 全体の合計（2 を使う）
  SELECT SUM(dept_total) AS all_total
  FROM dept_sales
)
SELECT d.dept, d.sellers, d.dept_total,
       ROUND(100.0 * d.dept_total / g.all_total, 1) AS pct
FROM dept_sales AS d
CROSS JOIN grand AS g
ORDER BY d.dept_total DESC;

書き方の注意点は3つです。

• WITH は最初に1回だけ書く: 2つ目以降は WITH を付けず、カンマで区切ります。MySQL のリファレンスマニュアルは、同じ階層に WITH 句を2つ並べる書き方は不正であり、カンマで区切って1つの WITH 句にまとめるように、と説明しています。
• 前に定義したものを後ろから参照する順で並べる: SQL Server のドキュメントには、CTE は自分自身と、同じ WITH 句の中で先に定義された CTE を参照でき、前方参照（後ろで定義される CTE の参照）はできないと書かれています。SQLite 3.54.0 では後ろの CTE を先に参照するクエリも実行できましたが、どのデータベースでも通る書き方にするなら、使う順に上から並べておくのが安全です。
• 最後の CTE の後ろにカンマを付けない: 本体の SELECT の直前にカンマが残っていると構文エラーになります。CTE を足したり消したりした直後に起きやすい間違いです。

途中の段階だけを確認したいときは、本体を SELECT * FROM dept_sales; のように書き換えれば、その CTE の中身をそのまま見られます。長いクエリを少しずつ組み立てて確かめられるのは、CTE の実用上の利点です。

同じ CTE を複数回参照する

サブクエリと違い、CTE は同じ文の中で何度でも参照できます。「受注合計が、社員1人あたりの平均より多い人」を出してみます。平均の計算と絞り込みの両方で、emp_sales を使います。

WITH emp_sales AS (
  SELECT employee_id, SUM(amount) AS total
  FROM orders
  GROUP BY employee_id
)
SELECT e.name, s.total,
       (SELECT ROUND(AVG(total), 1) FROM emp_sales) AS avg_total
FROM emp_sales AS s
JOIN employees AS e ON e.id = s.employee_id
WHERE s.total > (SELECT AVG(total) FROM emp_sales)
ORDER BY s.total DESC;

4人の合計の平均は 525 で、それを超える2人が残りました。サブクエリで書くなら、GROUP BY employee_id を含む同じ集計を3回書くことになります。CTE なら定義は1か所なので、集計の条件を変えるときに片方だけ直し忘れることがありません。

各行に平均を付けるだけであれば、ウィンドウ関数の AVG(total) OVER () でも書けます。ただしウィンドウ関数の結果は WHERE で直接使えないので、「平均より多い行に絞る」には結局 CTE かサブクエリで包むことになります。CTE とウィンドウ関数は、組み合わせて使う場面が多い機能です。

複数回参照したときに、データベースが CTE を1回だけ計算するのか、参照のたびに計算するのかは、製品によって異なります。これは後の「パフォーマンス」の節で扱います。

再帰 CTE の構造

ここからが再帰 CTE です。再帰 CTE は、自分自身の結果を参照する CTEで、次の3つの部品でできています。

WITH RECURSIVE 名前(列, ...) AS (
  SELECT ...            -- 非再帰項: 出発点になる行
  UNION ALL
  SELECT ...            -- 再帰項: 直前に得た行から、次の行を作る
  FROM 名前             --   （ここで自分自身を参照する）
  WHERE 終了条件
)
SELECT ... FROM 名前;

いちばん小さい例として、1 から 5 までの連番を作ります。

WITH RECURSIVE seq(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM seq WHERE n < 5
)
SELECT n FROM seq;

テーブルを1つも使わずに、5行の結果が得られました。

評価の進み方 - 「直前の結果」だけを使って繰り返す

「再帰」という名前ですが、実際の処理は繰り返しです。PostgreSQL のドキュメントも、RECURSIVE は再帰的に問い合わせを書けるようにするものだが、内部では反復として評価される、と述べています。同じドキュメントに書かれている評価の手順は次のとおりです。

1. 非再帰項を評価する。その結果を最終結果に加え、同時にワーキングテーブル（作業用の一時的な置き場）に入れる
2. ワーキングテーブルが空でない間、次を繰り返す
   • 再帰項を評価する。このとき、自分自身への参照はワーキングテーブルの現在の中身に置き換える。その結果を最終結果に加え、中間テーブルに入れる
   • ワーキングテーブルの中身を中間テーブルの中身で置き換え、中間テーブルを空にする

重要なのは、再帰項が見ているのは「これまでの結果全部」ではなく、直前の1回で新しく得られた行だけという点です。連番の例を、この手順で追ってみます。

5回目で再帰項が1行も返さなくなり、ワーキングテーブルが空になって終了します。つまり再帰 CTE は、再帰項が新しい行を返さなくなったときに止まります。WHERE n < 5 を書き忘れると、新しい行が永遠に作られ続けます。

SQLite のドキュメントは、同じことを「キュー」で説明しています。非再帰項の結果をキューに入れ、キューから1行取り出しては再帰項を実行して結果をキューに足す、という繰り返しです。行をまとめて処理するか1行ずつ処理するかの違いはありますが、「まだ処理していない行から次の行を作り、なくなったら終わる」という骨格は同じです。

RECURSIVE キーワードの要否はデータベースで違う

RECURSIVE を書くかどうかは、データベースごとに扱いが違います。

• MySQL: WITH 句の中に自分自身を参照する CTE が1つでもあれば、WITH RECURSIVE で始める必要があります。リファレンスマニュアルによると、書き忘れた場合は Table 'cte_name' doesn't exist というエラーになることがあります。
• SQLite: 書かなくても動きます。ドキュメントには、SQL:1999 の仕様では再帰 CTE を含む WITH 句に RECURSIVE が必要だが、SQL Server や Oracle との互換性のためにこの規則を強制していない、とあります。SQLite 3.54.0 で、上の連番のクエリから RECURSIVE を外しても同じ結果が返ることを確認しました。
• SQL Server: 構文に RECURSIVE というキーワードがありません。WITH だけで書き、CTE が自分自身を参照していれば再帰 CTE になります。

RECURSIVE を付けたからといって、その WITH 句のすべての CTE が再帰である必要はありません。通常の CTE と再帰 CTE は、同じ WITH RECURSIVE の中に混ぜて書けます。

連番と日付の生成

日付を生成して「データがない日」を埋める

再帰 CTE の実用的な使い道のひとつが、連続した日付の生成です。受注を日別に集計すると、受注がなかった日は結果に現れません。

SELECT ordered_on, COUNT(*) AS orders, SUM(amount) AS total
FROM orders
GROUP BY ordered_on;

この結果には 11月1日・2日・4日・7日の4行しか出てきません。グラフや帳票にするときは、0件の日も行として欲しくなります。そこで、日付だけの列を再帰 CTE で作り、そこに LEFT JOIN します。

WITH RECURSIVE days(d) AS (
  SELECT '2026-11-01'
  UNION ALL
  SELECT date(d, '+1 day') FROM days WHERE d < '2026-11-07'
)
SELECT days.d,
       COUNT(o.id)                AS orders,
       COALESCE(SUM(o.amount), 0) AS total
FROM days
LEFT JOIN orders AS o ON o.ordered_on = days.d
GROUP BY days.d
ORDER BY days.d;

受注のなかった3日間が、0 の行として現れました。押さえておく点は次のとおりです。

• 日付の側を左に置く: days を基準に LEFT JOIN するので、相手の受注がない日も残ります。
• 件数は COUNT(o.id) で数える: COUNT(*) にすると、受注のない日も「1行ある」と数えられて 1 になります。
• 合計は COALESCE で 0 にする: 相手がいない日の SUM は NULL になるためです。MySQL のリファレンスマニュアルにも、同じ形で日付の穴を埋める例が載っています。

日付を1日進める書き方はデータベースごとに違います。上の date(d, '+1 day') は SQLite の関数で、MySQL のマニュアルの例では date + INTERVAL 1 DAY と書かれています。PostgreSQL には generate_series という関数があり、日付や連番の生成だけなら再帰 CTE を使わずに書けます（この記事では実行していません）。

階層データをたどる

再帰 CTE がもっとも力を発揮するのは、employees のような親子関係を持つテーブルです。1段下の部下なら自己結合で取れますが、「部下の部下の部下」まで含めて全員、となると、何段結合すればよいかがデータ次第になります。再帰 CTE なら、段数を決めずに書けます。

上から下へ展開する - 深さを出す

社長から始めて、全員を階層の深さ付きで取り出します。

WITH RECURSIVE org(id, name, manager_id, depth) AS (
  SELECT id, name, manager_id, 0          -- 非再帰項: 上司のいない人
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, org.depth + 1
  FROM employees AS e
  JOIN org ON e.manager_id = org.id       -- 再帰項: 直前の行を上司に持つ人
)
SELECT id, name, manager_id, depth
FROM org
ORDER BY depth, id;

再帰項の結合条件 e.manager_id = org.id は、「直前に見つかった人（org）を上司に持つ社員（e）を探す」という意味です。depth は、非再帰項で 0 を置き、再帰項で親の値に 1 を足しているだけです。1回目の繰り返しで部長の2人、2回目で課長の2人、3回目で社員の3人が見つかり、4回目は誰も見つからないので終了します。

この例には WHERE による終了条件がありません。部下のいない社員まで到達すると結合の相手がいなくなり、再帰項が自然に0行になるからです。ツリー構造のデータでは、このようにデータの末端で自然に止まります。裏を返すと、データに循環があると止まらなくなります（後述）。

特定の人の配下だけが欲しいときは、非再帰項の条件を WHERE id = 2 のように変えます。出発点を変えるだけで、その人を頂点とする部分木が取れます。

経路を出す - ルートからの道筋を文字列でつなぐ

各行に「ルートからその人までの経路」を持たせることもできます。depth と同じ要領で、親の値に自分の名前をつなげていきます。

WITH RECURSIVE org(id, name, depth, path) AS (
  SELECT id, name, 0, name
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, org.depth + 1, org.path || ' > ' || e.name
  FROM employees AS e
  JOIN org ON e.manager_id = org.id
)
SELECT id, depth, path
FROM org
ORDER BY path;

経路の列で並べ替えると、親のすぐ下に子が並ぶ順（ツリーをそのまま上から書き下した順）になります。パンくずリストやカテゴリのツリー表示にそのまま使える形です。

ただし、名前をつないだ文字列での並べ替えは、名前の文字コード順に左右されます。並び順を確実に決めたいときは、ゼロ埋めした ID（001/002/004 のような形）や表示順の列をつないだ、並べ替え専用の列を別に持たせます。

文字列の連結は方言の差が出るところです。上の || は SQLite や PostgreSQL の書き方で、MySQL のマニュアルの例は CONCAT を使っています。さらに MySQL には次の落とし穴があります。

下から上へたどる - 上司をさかのぼる

結合条件の向きを逆にすると、ある社員から上司をさかのぼれます。社員C（id 8）から社長までの経路です。

WITH RECURSIVE chain(id, name, manager_id, step) AS (
  SELECT id, name, manager_id, 0
  FROM employees
  WHERE id = 8
  UNION ALL
  SELECT e.id, e.name, e.manager_id, chain.step + 1
  FROM employees AS e
  JOIN chain ON e.id = chain.manager_id   -- 直前の行の上司を探す
)
SELECT step, id, name
FROM chain
ORDER BY step;

上から下のときは e.manager_id = org.id（直前の行を上司に持つ人）、下から上のときは e.id = chain.manager_id（直前の行の上司）です。結合条件のどちら側に CTE を置くかで、たどる向きが決まります。向きを取り違えると、期待と逆方向に展開されるか、出発点の1行しか返らなくなります。

配下全員の合計を出す

ツリーの展開と集計を組み合わせると、「その人の配下全員の受注合計」も出せます。出発点を1人に絞らず、全員をそれぞれ出発点にするのがコツです。

WITH RECURSIVE sub(root_id, id) AS (
  SELECT id, id FROM employees            -- 全員を出発点にする
  UNION ALL
  SELECT sub.root_id, e.id
  FROM employees AS e
  JOIN sub ON e.manager_id = sub.id
)
SELECT r.name,
       COUNT(DISTINCT sub.id) - 1  AS members,     -- 本人を除いた配下の人数
       COALESCE(SUM(o.amount), 0)  AS team_total
FROM sub
JOIN employees AS r ON r.id = sub.root_id
LEFT JOIN orders AS o ON o.employee_id = sub.id
GROUP BY r.id, r.name
ORDER BY r.id;

sub は「頂点（root_id）と、その配下に含まれる人（id）」の組を全通り持つ CTE です。root_id を再帰項でそのまま引き継いでいるので、どの頂点から始まった展開なのかが最後まで残ります。あとは頂点ごとに GROUP BY するだけです。

集計を再帰項の中ではなく、外側のクエリで行っている点に注意してください。再帰項の中では集約関数を使えないデータベースが多いためです（後の「つまずき」の節で扱います）。また、1人が複数の受注を持つと sub の行が受注の数だけ増えるので、人数は COUNT(DISTINCT sub.id) で数えています。1対多の結合で行が増える問題は、JOIN の記事で詳しく扱っています。

アプリケーション側で「部下を取得し、その部下ごとにまた部下を取得する」というループを書くと、階層の深さと人数に応じてクエリの回数が増えていきます（N+1問題と同じ構図です）。再帰 CTE は、それを1回の問い合わせにまとめる手段でもあります。

探索の順番 - 幅優先と深さ優先

再帰 CTE が行を見つけていく順番は、グラフ探索でいう幅優先探索（近い階層から順に）と深さ優先探索（1本の枝を末端まで先に）のどちらかに対応します。2つの探索の考え方はグラフ探索アルゴリズム入門で解説しています。

ここで注意したいのは、結果の並び順を探索の順番に頼ってはいけないことです。PostgreSQL のドキュメントは、再帰問い合わせの評価アルゴリズムは幅優先の順で出力を生成するが、これは実装の詳細であり、それに頼るのは適切でないだろう、各階層の中の行の順序は未定義である、と述べています。並び順が必要なら、これまでの例のように depth や経路の列を作り、外側のクエリで ORDER BY を明示します。

データベース固有の機能もあります。

• SQLite: 再帰項に ORDER BY を書くと、キューから行を取り出す順番を指定できます。ドキュメントによると、ORDER BY を省略した場合キューは先入れ先出しになり、結果として幅優先になります。深さの列で降順に並べると深さ優先になります。SQLite 3.54.0 で組織のクエリの再帰項に ORDER BY 3 DESC（3列目の depth の降順）を足すと、外側に ORDER BY がなくても、社長A、部長A、課長A、社員A、社員B、部長B、課長B、社員C の順で返りました。
• PostgreSQL: SEARCH DEPTH FIRST BY 列 SET 並べ替え用の列名（または BREADTH FIRST）という SEARCH 句があり、並べ替えに使える列を自動で追加してくれます。ドキュメントは、これは評価時に行を訪れる順番を制御するものではなく、後から結果を並べ替えるための便利な方法を提供するだけだ、と明記しています。

WITH RECURSIVE org(id, name, manager_id) AS (
  SELECT id, name, manager_id FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id
  FROM employees AS e JOIN org ON e.manager_id = org.id
) SEARCH DEPTH FIRST BY id SET ordercol
SELECT * FROM org ORDER BY ordercol;

UNION と UNION ALL の違い

非再帰項と再帰項をつなぐ演算子には、UNION ALL と UNION の2つがあります。違いは重複した行を捨てるかどうかで、再帰 CTE ではこれが「止まるかどうか」に直結します。

循環のあるデータで確かめます。次の links は、ノードからノードへの矢印を表すテーブルで、1 から 2、2 から 3、3 から 1 と一周する輪が含まれています。

CREATE TABLE links (
  src INTEGER NOT NULL,
  dst INTEGER NOT NULL
);
 
INSERT INTO links VALUES (1, 2), (2, 3), (3, 1), (3, 4);

ノード 1 から到達できるノードを UNION ALL でたどると、輪を回り続けます。次のクエリは、外側の LIMIT 10 で打ち切って最初の10行だけを見たものです。

WITH RECURSIVE reach(node) AS (
  SELECT 1
  UNION ALL
  SELECT l.dst FROM links AS l JOIN reach ON l.src = reach.node
)
SELECT node FROM reach LIMIT 10;

返ってきた node は、順に 1, 2, 3, 1, 4, 2, 3, 1, 4, 2 でした。同じノードが何度も現れ、LIMIT がなければ終わりません。演算子を UNION に変えると、結果が変わります。

WITH RECURSIVE reach(node) AS (
  SELECT 1
  UNION
  SELECT l.dst FROM links AS l JOIN reach ON l.src = reach.node
)
SELECT node FROM reach;

4行で止まりました。PostgreSQL のドキュメントは、UNION の場合、再帰項の結果から重複行と、これまでの結果のどれかと重複する行を捨てる、と説明しています。3 から 1 に戻ったとき、1 はすでに結果にあるので捨てられ、新しい行がなくなって終了します。MySQL のリファレンスマニュアルも、UNION DISTINCT は推移閉包を求める問い合わせで無限ループを避けるのに役立つ、と書いています。

ただし、UNION は万能ではありません。

• 行全体が一致して初めて重複になる: depth や経路のように、たどるたびに値が変わる列を足すと、同じノードでも別の行として扱われます。上のクエリに depth を足して UNION のまま実行すると、(1, 0)、(2, 1)、(3, 2)、(1, 3)、(4, 3)、(2, 4) と続き、止まりませんでした。PostgreSQL のドキュメントにも、深さの列がある場合は UNION ALL を UNION に変えるだけでは循環を解消できない、という趣旨の注意があります。
• 重複の確認にコストがかかる: SQLite のドキュメントは、UNION では重複を調べるためにそれまでに生成した内容をすべて保持する必要があるので、可能なら UNION ALL を使うように、と勧めています。
• 使えないデータベースがある: SQL Server のドキュメントでは、最後のアンカーメンバーと最初の再帰メンバーの間に使える集合演算子は UNION ALL だけです。

無限ループを防ぐ

組織図のようなデータでも、入力ミスで「A の上司が B、B の上司が A」になれば循環します。再帰 CTE を本番で使うなら、データが壊れていても止まるように書いておくのが安全です。方法は4つあります。

1. 深さに上限を付ける

もっとも単純で、どのデータベースでも使える方法です。深さの列を持たせ、再帰項の WHERE で上限を決めます。

WITH RECURSIVE reach(node, depth) AS (
  SELECT 1, 0
  UNION ALL
  SELECT l.dst, reach.depth + 1
  FROM links AS l
  JOIN reach ON l.src = reach.node
  WHERE reach.depth < 4
)
SELECT node, depth FROM reach;

循環は解消されていないので同じノードが再び現れていますが、深さ 4 で必ず止まります。「組織の階層は多くても10段」のように現実的な上限がわかっているなら、それより少し大きい値を入れておくだけで保険になります。

2. 経路を持たせて、通ったノードに戻らない

循環そのものを検出したい場合は、通ってきた経路を列に持たせ、次のノードがすでに経路に含まれていたら進まないようにします。

WITH RECURSIVE reach(node, depth, path) AS (
  SELECT 1, 0, '/1/'
  UNION ALL
  SELECT l.dst, reach.depth + 1, reach.path || l.dst || '/'
  FROM links AS l
  JOIN reach ON l.src = reach.node
  WHERE instr(reach.path, '/' || l.dst || '/') = 0   -- 経路に含まれていなければ進む
)
SELECT node, depth, path
FROM reach
ORDER BY depth, node;

3 から 1 へ戻ろうとすると、経路 /1/2/3/ に /1/ が含まれているので進みません。ID の前後を区切り文字で挟んでいるのは、/1/ が /11/ に誤って一致しないようにするためです。instr は SQLite の関数で、PostgreSQL のドキュメントでは、経路を配列で持ち ANY で判定する書き方が紹介されています。

3. PostgreSQL の CYCLE 句

PostgreSQL 14 以降では、上の「経路を持たせて判定する」処理を CYCLE 句に任せられます。14 のリリースノートに、SQL 標準の SEARCH と CYCLE を CTE に追加したこと、同じ結果は既存の構文でも得られたがずっと不便だったことが書かれています。

WITH RECURSIVE reach(node, depth) AS (
  SELECT 1, 0
  UNION ALL
  SELECT l.dst, reach.depth + 1
  FROM links AS l JOIN reach ON l.src = reach.node
) CYCLE node SET is_cycle USING path
SELECT * FROM reach;

ドキュメントによると、CYCLE 句には、循環の検出に使う列、循環を検出したかどうかを示す列の名前、経路を記録する列の名前を順に指定します。循環を示す列と経路の列は、CTE の出力に暗黙に追加されます。

4. データベース側の上限に頼る

再帰の回数や実行時間に上限を設ける仕組みを持つデータベースもあります。

• MySQL: リファレンスマニュアルによると、cte_max_recursion_depth の既定値は 1000 で、それを超えて再帰する CTE は実行が打ち切られます。ほかに、max_execution_time や MAX_EXECUTION_TIME オプティマイザヒントで実行時間を制限する方法、再帰項に LIMIT を書く方法も紹介されています。逆に言うと、1000 回を超える再帰が正当に必要なクエリ（数年分の日付の生成など）では、この値を引き上げる必要があります。
• SQL Server: ドキュメントによると、MAXRECURSION には 0 から 32767 までの値を指定でき、サーバー全体の既定値は 100、0 を指定すると無制限です。ヒントは CTE の定義の中ではなく、外側の文の OPTION 句に書きます。
• SQLite: 再帰の回数を制限する設定はドキュメントに見当たりませんが、再帰項に LIMIT を書けます。ドキュメントによると、この LIMIT は再帰テーブルに追加される行数の上限を決め、上限に達すると再帰が止まります。再帰の規模の上限がわかっているなら、安全のために常に LIMIT を入れておくのがよい習慣だとされています。

PostgreSQL には、再帰の回数そのものを制限する設定は、今回確認したドキュメントの範囲では見当たりませんでした（未確認）。ドキュメントが紹介しているのは、ループするかどうか確信が持てないクエリを試すときに、親のクエリに LIMIT を付ける方法です。PostgreSQL の実装は親クエリが実際に取り出す行数しか WITH の問い合わせを評価しないため、これで止まります。ただし同じドキュメントが、他のシステムでは動作が異なる可能性があるので本番での使用は勧めない、外側のクエリで並べ替えや結合をすると通常は効かない、と注意しています。あくまで動作確認用の手段です。

パフォーマンス - マテリアライズとインライン化

CTE をデータベースがどう実行するかは、大きく2通りあります。

• マテリアライズ: CTE の結果を一度計算して一時的な置き場に保存し、参照のたびにそれを読む
• インライン化（マージ）: CTE を本体のクエリの中に展開し、サブクエリと同じように全体をまとめて最適化する

マテリアライズは、重い計算を1回で済ませられる代わりに、外側の絞り込み条件を CTE の内側に持ち込めません。インライン化はその逆です。どちらが選ばれるかの既定の動作が、データベースごとに違います。

PostgreSQL - 12 でインライン化が既定になった

PostgreSQL 12 のリリースノートには、CTE を外側のクエリにインライン化できるようにしたこと、具体的には副作用がなく、再帰でなく、クエリの中で1回だけ参照される CTEが自動的にインライン化されること、そして「以前は CTE は決してインライン化されず、常にクエリの残りの部分より先に評価されていた」ことが書かれています。

現在のドキュメントの説明をまとめると、次のようになります。

この既定の判断は、キーワードで上書きできます。

-- 1回しか参照しないが、あえて先に計算させる
WITH w AS MATERIALIZED (
  SELECT * FROM big_table
)
SELECT * FROM w WHERE key = 123;
 
-- 2回参照するが、それぞれの場所に展開させる
WITH w AS NOT MATERIALIZED (
  SELECT * FROM big_table
)
SELECT * FROM w AS w1 JOIN w AS w2 ON w1.key = w2.ref
WHERE w2.key = 123;

ドキュメントは、複数回参照される CTE はマテリアライズされるので、親のクエリの絞り込み条件を CTE の中へ押し込めないという不利があること、NOT MATERIALIZED は CTE を重複して計算する危険がある一方で、それぞれの参照箇所が CTE の出力のごく一部しか必要としないなら全体として得になりうることを説明しています。反対に、CTE の中で高コストな関数を呼んでいる場合は、マテリアライズされたほうが呼び出しが1回で済みます。

SELECT のリファレンスには、PostgreSQL 12 より前のバージョンではこの畳み込みが行われなかったため、古いバージョン向けに書かれたクエリは WITH が「最適化の障壁」として働くことを当てにしている場合がある、という注意もあります。古いクエリを新しいバージョンへ移したら実行計画が変わった、というときは、該当の CTE に MATERIALIZED を付けると以前の動作に戻せます。

MySQL - マージできなければマテリアライズ

MySQL のリファレンスマニュアルは、オプティマイザが派生テーブル（FROM 句のサブクエリ）・ビュー参照・CTE を同じ方法で扱うと説明しています。戦略は、外側のクエリブロックへのマージと、内部一時テーブルへのマテリアライズの2つで、不要なマテリアライズはできるだけ避けるとされています。

マージできなくなる条件として、集約関数やウィンドウ関数、DISTINCT、GROUP BY、HAVING、LIMIT、UNION または UNION ALL、SELECT リスト内のサブクエリなどが挙げられています。この記事の emp_sales のように GROUP BY を含む CTE は、この条件に当てはまります。そのほか、次の点が書かれています。

• CTE がマテリアライズされる場合、クエリの中で何度参照されても、マテリアライズは1回だけ
• 再帰 CTE は常にマテリアライズされる
• オプティマイザは、マテリアライズした結果に対してインデックスを追加することがある
• MERGE / NO_MERGE オプティマイザヒントで、CTE ごとに指定できる

SQLite - 3.35.0 以降は複数回参照でマテリアライズ

SQLite は 3.35.0 で MATERIALIZED と NOT MATERIALIZED のヒントに対応しました。同じリリースノートに、既定の動作は以前は NOT MATERIALIZED だったが、2回以上使われる CTE については MATERIALIZED に変更されたとあります。

手元の SQLite 3.54.0 で EXPLAIN QUERY PLAN を確認すると、この説明どおりの計画になりました。

2行目と3行目の差が、インライン化とマテリアライズの違いをよく表しています。インライン化されると外側の id = 3 が CTE の中まで届いて主キーで1行だけ読みますが、マテリアライズを強制すると、全行を読んで保存してから絞り込むことになります。

SQLite のドキュメントは、どちらのヒントもない場合は SQLite が最適と考える方法を自由に選ぶこと、それが推奨される使い方であり、やむを得ない理由がない限りヒントを使わないように、としています。

SQL Server - 参照のたびに再実行される

SQL Server のドキュメントには、CTE の問い合わせ結果はマテリアライズされず、名前付きの結果セットへの外側からの参照のたびに、定義された問い合わせの再実行が必要になる、と書かれています。複数回の参照が必要なクエリでは、代わりに一時オブジェクトの利用を検討するよう勧めています。同じ CTE を何度も参照する書き方は、SQL Server では性能に直接響く可能性があります。

再帰 CTE の性能

再帰 CTE では、再帰項が繰り返しのたびに実行されます。再帰項の結合条件に使う列（この記事の例では employees.manager_id や links.src）にインデックスがないと、繰り返しのたびにテーブル全体を読むことになります。階層をたどるテーブルでは、親を指す列にインデックスを作っておくのが基本です。

MySQL のリファレンスマニュアルは、再帰 CTE について、EXPLAIN のコスト見積もりは1回の繰り返しあたりのコストであり、合計のコストとは大きく異なる場合があると注意しています。オプティマイザは繰り返しの回数を予測できないためです。再帰 CTE を含むクエリが遅いときは、見積もりの数字だけでなく、実際の実行時間と行数を確認してください。実行計画の読み方はクエリ実行計画入門にまとめています。

なお、上の SQLite の実行計画は数行のサンプルテーブルでの確認です。実際にどちらの方法が選ばれるか、どちらが速いかは、データ量、統計情報、データベースのバージョンによって変わります。

データ変更を伴う CTE

WITH 句は SELECT 専用ではありません。PostgreSQL・MySQL・SQLite・SQL Server のいずれのドキュメントにも、WITH を INSERT・UPDATE・DELETE と組み合わせられることが書かれています（組み合わせられる文の種類は製品ごとに異なります）。

CTE の結果を使って更新・削除する

再帰 CTE で対象の行を集め、その結果を使って削除する例です。課長A（id 4）とその配下を、まとめて削除します。

WITH RECURSIVE sub(id) AS (
  SELECT 4
  UNION ALL
  SELECT e.id FROM employees AS e JOIN sub ON e.manager_id = sub.id
)
DELETE FROM employees
WHERE id IN (SELECT id FROM sub);

SQLite 3.54.0 でトランザクションの中で実行し、課長A・社員A・社員B の3行が削除されて5行が残ることを確認しました（確認後にロールバックしています）。先に DELETE の部分を SELECT id, name FROM employees に置き換えて、対象の行を目で確かめてから実行するのが安全です。

PostgreSQL のドキュメントには、データ変更文の中で再帰的な自己参照はできないが、このように再帰的な WITH の出力を参照することで回避できる、として同じ形の例が載っています。

PostgreSQL - WITH の中に INSERT / UPDATE / DELETE を書く

PostgreSQL では、さらに踏み込んで、WITH の中にデータ変更文そのものを書けます（9.1 のリリースノートに追加が記載されています）。RETURNING 句と組み合わせると、「削除した行をそのまま別のテーブルへ入れる」といった処理が1文で書けます。

WITH moved AS (
  DELETE FROM orders
  WHERE ordered_on < '2026-11-03'
  RETURNING *
)
INSERT INTO orders_archive
SELECT * FROM moved;

DELETE が削除した行を RETURNING * が返し、それが moved という CTE の中身になります。ドキュメントが挙げている性質のうち、知らないと驚くものをまとめます。

• CTE の中身になるのは RETURNING の出力: 対象テーブルそのものではありません。RETURNING のないデータ変更文は CTE として参照できませんが、それでも実行はされます。
• 必ず1回、最後まで実行される: 本体のクエリがその出力を全部読むかどうか（あるいはまったく読まないか）に関係なく、データ変更文は完全に実行されます。
• 同じスナップショットで実行される: WITH の中の各文と本体のクエリは同時に実行されるものとして扱われ、互いの変更結果を対象テーブル上では見られません。WITH の中で UPDATE をして、本体でそのテーブルを直接 SELECT すると、更新前の値が返ります。変更後の値を受け渡す手段は RETURNING だけです。
• 同じ行を2回変更しない: 1つの文の中で同じ行を2回更新することはサポートされておらず、どちらが反映されるかを確実に予測するのは難しいとされています。
• 最上位の文にだけ書ける: データ変更文を含められるのは、最上位の文に付いた WITH 句だけです。

この書き方は PostgreSQL の機能です。SQLite 3.54.0 では、WITH の中に DELETE を書いたクエリは構文エラーになりました。MySQL と SQL Server については、今回確認したドキュメントの WITH の構文に、CTE の本体としてデータ変更文を書く形は載っていませんでした。

データベースごとの対応差

ここまでに出てきた違いを、表にまとめます。

• PostgreSQL: 8.4 のリリースノートに、WITH 句（CTE）と WITH RECURSIVE のサポート追加が記載されています。その後、9.1 でデータ変更文、12 でインライン化と MATERIALIZED / NOT MATERIALIZED、14 で SEARCH 句と CYCLE 句が加わりました。
• MySQL: 8.0.1（2017年4月10日、開発マイルストーン版）のリリースノートに、非再帰と再帰の両方の CTE をサポートしたことが記載されています。5.7 以前では WITH 句は使えません。
• SQLite: 3.8.3（2014年2月3日）のリリースノートに、CTE と WITH 句のサポート追加が記載されています。OS やアプリケーションに同梱された古い SQLite を使っている場合は、SELECT sqlite_version(); でバージョンを確認してください。なお、WITH 句は CREATE TRIGGER の中では使えません。
• SQL Server: 今回参照した公式ドキュメントのページは SQL Server 2017 以降を対象としており、CTE が最初に導入されたバージョンの記載は確認できませんでした（未確認）。Azure Synapse Analytics については、同じページに、自分自身を参照する CTE（再帰 CTE）はサポートされないと書かれています。

よくあるつまずき

再帰項の中で集約関数やウィンドウ関数を使えない

「再帰のたびに合計を取りたい」と考えて再帰項に SUM や MAX を書くと、エラーになります。SQLite 3.54.0 では recursive aggregate queries not supported というエラーになりました。

• MySQL のリファレンスマニュアルは、再帰項に書けないものとして、集約関数、ウィンドウ関数、GROUP BY、ORDER BY、DISTINCT を挙げています。
• SQLite のドキュメントも、再帰項では集約関数とウィンドウ関数を使えないとしています。
• SQL Server のドキュメントは、再帰メンバーに書けないものとして SELECT DISTINCT、GROUP BY、HAVING、スカラー集計、TOP、外部結合、サブクエリなどを挙げています。

対処は、「配下全員の合計を出す」の節のように、再帰 CTE では行を展開するだけにして、集計は外側のクエリで行うことです。

再帰項の中で CTE を2回参照できない

再帰項の中で自分自身を2回結合しようとすると、SQLite 3.54.0 では multiple references to recursive table というエラーになりました。SQLite のドキュメントは再帰テーブルが再帰項の FROM 句にちょうど1回だけ現れることを求めており、MySQL のリファレンスマニュアルも、再帰項は CTE を1回だけ、FROM 句の中でのみ参照でき、サブクエリの中では参照できないとしています。MySQL ではさらに、CTE を LEFT JOIN の右側に置くこともできません。

再帰項は「直前の行」しか見ていない

再帰項から見える CTE の中身は、これまでの結果全部ではなく、直前の繰り返しで得られた行です（「評価の進み方」の節を参照）。「すでに結果に含まれているノードは除外する」という条件を、再帰項の中で NOT IN (SELECT ... FROM CTE) のように書くことはできません。訪問済みかどうかを判定したいなら、UNION を使うか、経路の列を行に持たせて引き継ぎます。

SQL Server のドキュメントも、再帰部分の中の分析関数や集約関数は、CTE 全体ではなく現在の再帰レベルの集合に対して適用される、と注意しています。

終了条件を「外側の WHERE」に書いても止まらない

次の2つは、似ているようで意味が違います。

-- 再帰項の中に書く: 5 で再帰が止まる
WITH RECURSIVE seq(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM seq WHERE n < 5
)
SELECT n FROM seq;
 
-- 外側に書く: 再帰は止まらず、結果を絞り込んでいるだけ
WITH RECURSIVE seq(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM seq
)
SELECT n FROM seq WHERE n <= 5;

外側の WHERE は、再帰 CTE が作った行を後から絞り込む条件です。再帰そのものを止める働きは期待できません。終了条件は、必ず再帰項の中に書きます。

結果の並び順を期待してしまう

再帰 CTE の結果は、たまたま階層の浅い順に返ってくることが多いので、ORDER BY を書かなくても正しく見えます。しかし前述のとおり、PostgreSQL のドキュメントはこの順序を実装の詳細としており、当てにすべきではないとしています。画面にツリーとして表示するなら、深さや経路の列で ORDER BY を明示します。

SQL Server では直前の文にセミコロンが要る

SQL Server のドキュメントには、CTE をバッチの一部である文で使う場合、その前の文の後ろにセミコロンが必要だと書かれています。前の文をセミコロンで終えていないスクリプトに CTE を足すとエラーになるので、SQL Server 向けのコードでは文末のセミコロンを省略しない習慣にしておくと安全です。

深い階層で上限に当たる

無限ループでなくても、正当に深い再帰が上限に当たることがあります。MySQL は既定で 1000、SQL Server は既定で 100 が上限です。「1年分の日付を生成する」クエリは 365 回の再帰になるので、SQL Server の既定値では足りません。上限に当たったら、まずデータの循環を疑い、正当な深さだとわかった場合にだけ、そのクエリに限って上限を引き上げます。

まとめ

• CTE は、1つの SQL 文の中だけで有効な、名前付きの問い合わせ結果。サブクエリを上に取り出して、手順として上から読めるクエリにできる。
• 複数の CTE はカンマで区切って並べ、後ろの CTE から前の CTE を参照する。WITH は最初に1回だけ書く。
• 同じ CTE は、同じ文の中で何度でも参照できる。ビューと違ってデータベースに定義は残らず、一時テーブルと違って次の文からは見えない。
• 再帰 CTE は、非再帰項、UNION ALL、再帰項の3つでできている。再帰項は直前の繰り返しで得た行だけを参照し、新しい行が出なくなったときに止まる。
• 連番や日付を生成して LEFT JOIN すると、データのない日を 0 の行として埋められる。
• 階層データは、結合条件の向きで「上から下」と「下から上」を切り替える。深さは親の値に 1 を足し、経路は親の値に自分をつなげて作る。
• UNION は既出の行を捨てるので循環で止まるが、深さや経路の列があると効かない。循環がありうるデータでは、深さの上限や経路による判定を入れる。
• データベース側の上限は、MySQL が cte_max_recursion_depth（既定 1000）、SQL Server が MAXRECURSION（既定 100）。PostgreSQL 14 以降には CYCLE 句がある。
• CTE がインライン化されるかマテリアライズされるかは製品ごとに違う。PostgreSQL は 12 以降、1回だけ参照される CTE をインライン化し、MATERIALIZED / NOT MATERIALIZED で指定できる。SQL Server は参照のたびに再実行する。
• PostgreSQL では WITH の中に INSERT / UPDATE / DELETE と RETURNING を書ける。各文は同じスナップショットで実行される。

再帰 CTE を書いたら、わざと循環を含むデータを用意して一度実行してみてください。正常なデータでは問題なく止まるクエリが、データの不整合ひとつで止まらなくなることを、本番より先に確かめられます。

参考リンク

• WITH Queries (Common Table Expressions)（PostgreSQL 公式）
• SELECT - WITH Clause（PostgreSQL 公式）
• PostgreSQL 8.4 リリースノート
• PostgreSQL 9.1 リリースノート
• PostgreSQL 12 リリースノート
• PostgreSQL 14 リリースノート
• WITH (Common Table Expressions)（MySQL 8.4 リファレンスマニュアル）
• Optimizing Derived Tables, View References, and Common Table Expressions with Merging or Materialization（MySQL 8.4 リファレンスマニュアル）
• Changes in MySQL 8.0.1（MySQL リリースノート）
• The WITH Clause（SQLite 公式）
• SQLite Release 3.8.3
• SQLite Release 3.35.0
• WITH common_table_expression（SQL Server 公式）
• Recursive queries using common table expressions（SQL Server 公式）

関連記事

• SQL の JOIN の種類と違い - INNER / LEFT / FULL / CROSS の結果を例で理解する
• SQL ウィンドウ関数入門 - OVER と PARTITION BY、ROW_NUMBER・RANK・LAG の使い分け
• クエリ実行計画入門 - EXPLAINの読み方とプランナがJOINアルゴリズムを選ぶ理由
• データベースインデックス入門 - B-treeの仕組みと、効くクエリ・効かないクエリ
• N+1問題とは - ORM で起きる原因と Eager Loading・JOIN・バッチ取得での対策
• データベース正規化 入門 - 第1〜第3正規形とBCNFを実例で理解する