SQL WITH句の使い方｜CTEと再帰クエリをコピペSQLと実行結果で解説

SQL の WITH 句は、WITH 名前 AS (SELECT ...) の形で「名前付きの一時的な結果」を文の先頭で定義し、続く SELECT・UPDATE・DELETE からテーブルのように参照する書き方です。共通テーブル式（CTE：Common Table Expression）とも呼びます。 サブクエリを上から順に読める形に直せて、同じ結果を1つの文の中で何度でも参照でき、WITH RECURSIVE を付けると階層をたどる処理や連番の生成も1つの文で書けます。

WITH member_total AS (                       -- 名前を付けた中間結果（CTE）
  SELECT member_id, SUM(amount) AS total
  FROM orders WHERE status = 'new'
  GROUP BY member_id
)
SELECT member_id, total                      -- 本体。CTE をテーブルのように使う
FROM member_total
WHERE total >= 5000;

WITH句の早見表（やりたいこと→書き方）

自分のやりたいことの行を見ると、書き方の骨組みと注意点が分かります。例はこの記事の orders（注文）・staff（社員）テーブルを使っています。

やりたいこと	書き方の骨組み	注意点
サブクエリに名前を付ける	WITH t AS (SELECT ...) SELECT ... FROM t	有効なのはその1文だけ
CTE の列名を付ける	WITH t(member_id, total) AS (SELECT member_id, SUM(amount) ...)	列数が合わないとエラー
複数の CTE を順に作る	WITH a AS (...), b AS (SELECT ... FROM a) SELECT ... FROM b	WITH は1回。2つ目以降はカンマ
同じ CTE を2回使う	FROM t a JOIN t b ON ...／WHERE x > (SELECT MAX(x) FROM t)	計算は原則1回（性能の節）
ウィンドウ関数の結果で絞る	WITH r AS (SELECT ..., ROW_NUMBER() OVER (...) AS rn FROM ...) SELECT * FROM r WHERE rn = 1	別名を直接 WHERE に書くとエラー
階層をたどる（再帰）	WITH RECURSIVE t AS (起点の SELECT UNION ALL SELECT ... FROM 表 JOIN t ON ...) SELECT * FROM t	終了条件（再帰部が0行になる）が要る
連番・日付を作る	WITH RECURSIVE seq AS (SELECT 1 AS n UNION ALL SELECT n + 1 FROM seq WHERE n < 100)	MySQL は 1000 回が既定の上限
閉路（循環）を止める	PostgreSQL ) CYCLE dst SET is_cycle USING path、MySQL は訪問済み文字列	UNION だけでは止まらないことがある
更新対象を CTE で絞る	PostgreSQL WITH c AS (...) UPDATE t SET ... FROM c WHERE ...、MySQL WITH c AS (...) UPDATE t JOIN c ON ... SET ...	先に同じ WITH を SELECT で確認
削除した行を別の表へ移す	PostgreSQL WITH moved AS (DELETE ... RETURNING *) INSERT INTO archive SELECT * FROM moved	MySQL は WITH の中に DELETE を書けない
INSERT と組み合わせる	PostgreSQL WITH c AS (...) INSERT INTO t SELECT * FROM c、MySQL INSERT INTO t WITH c AS (...) SELECT * FROM c	MySQL は WITH の位置が違う
計算を1回に固定する／展開する	WITH t AS MATERIALIZED (...)／AS NOT MATERIALIZED（PostgreSQL 12 以降・SQLite 3.35 以降）	MySQL は NO_MERGE／MERGE ヒント
定数をまとめて渡す	WITH params(min_amount) AS (VALUES (2500)) SELECT ... FROM orders, params WHERE amount >= min_amount	MySQL は AS (SELECT 2500)
暴走した再帰を止める	MySQL cte_max_recursion_depth（既定 1000）、PostgreSQL SET statement_timeout、SQL Server OPTION (MAXRECURSION n)	本番では終了条件を必ず書く

この記事と関連記事の分担

知りたいこと	読む記事
サブクエリそのものの書き方・EXISTS・JOIN への書き換え	SQLサブクエリの使い方｜相関サブクエリ・EXISTS・JOIN書き換え
複数の文で使い回す一時テーブルとの違い	SQL一時テーブルの作成｜TEMPORARY TABLEとCTEの違い
ビュー・マテリアライズドビューとの使い分け	SQLビューとマテリアライズドビュー｜違い・REFRESH・使い分け
ウィンドウ関数（累計・前月比・上位N件）	SQLウィンドウ関数の使い方｜累計・前月比・上位N件を実行結果で解説
グループごとの最新1件を取る6方式の実測	SQLでグループごとに最新の1件を取得｜6方式をDB別に100万件で実測
UPDATE 文そのものの書き方（DB別）	SQL UPDATE文の書き方｜複数列・別テーブル・CASE・DB別構文の早見表

確認環境：PostgreSQL 16.15（Ubuntu 24.04、psql）、MySQL 8.0.46（mysql クライアント、sql_mode は STRICT_TRANS_TABLES を含む既定値）、SQLite 3.45.1（Python 3 の sqlite3 モジュール）で、この記事の SQL を実行し、出力をそのまま載せています。性能の節は 100 万行の表で測り、EXPLAIN の実行時間は実行のたびに変わります。SQL Server・Oracle・MariaDB・BigQuery の構文と制限は、Microsoft Learn「WITH common_table_expression (Transact-SQL)」、Oracle Database 23ai SQL Language Reference、MariaDB Server Documentation、BigQuery ドキュメントで確かめました（確認日 2026年10月16日）。

この記事で使うテーブルとデータ（コピペ用）

社員の上下関係 staff、日別売上 sales、経路 routes、注文 orders の4テーブルです。PostgreSQL・MySQL・SQLite のどれでも、このまま貼って実行できます。

CREATE TABLE staff (id INT PRIMARY KEY, name VARCHAR(20), manager_id INT);
INSERT INTO staff VALUES
  (1,'社長',NULL),(2,'開発部長',1),(3,'営業部長',1),(4,'開発リーダー',2),
  (5,'開発メンバーA',4),(6,'開発メンバーB',4),(7,'営業メンバー',3);

CREATE TABLE sales (sold_on DATE, amount INT);
INSERT INTO sales VALUES ('2026-10-01',1200),('2026-10-02',800),('2026-10-04',1500),('2026-10-07',600);

CREATE TABLE routes (src VARCHAR(5), dst VARCHAR(5), minutes INT);
INSERT INTO routes VALUES ('A','B',10),('B','C',15),('A','C',30),('C','D',5),('D','B',7);

CREATE TABLE orders (id INT PRIMARY KEY, member_id INT, amount INT, status VARCHAR(10));
INSERT INTO orders VALUES
  (1,101,3000,'new'),(2,101,5000,'new'),(3,102,1000,'new'),(4,103,8000,'new'),(5,103,2000,'canceled');

staff は manager_id で上司を指す、4段の組織ツリーです。

flowchart TD
  n1["1 社長"] --> n2["2 開発部長"]
  n1 --> n3["3 営業部長"]
  n2 --> n4["4 開発リーダー"]
  n4 --> n5["5 開発メンバーA"]
  n4 --> n6["6 開発メンバーB"]
  n3 --> n7["7 営業メンバー"]

routes は「A→B→C→D→B」と、B から D を経由して B に戻る閉路（循環） を含むデータです。閉路の節で使います。

WITH句の基本：サブクエリに名前を付ける

WITH 句は3つの部品でできています。

WITH 名前 [(列名, ...)] AS (
  SELECT ...            -- この結果に「名前」が付く
)
SELECT ... FROM 名前;   -- 本体。SELECT・INSERT・UPDATE・DELETE

部品	役割	省略すると
WITH 名前 AS (...)	中間結果に名前を付ける。括弧の中は普通の SELECT	省略できない
(列名, ...)	CTE の列名を付け直す	SELECT の列名・別名がそのまま使われる
本体の文	CTE を FROM・JOIN・サブクエリの中でテーブルのように使う	省略できない（WITH だけでは文にならない）

最小の形と、サブクエリで書いた場合の比較

会員ごとの合計金額を出してから、5000 円以上の会員に絞る例です。PostgreSQL 16 の結果で、MySQL 8.0・SQLite 3.45 でも同じ2行でした。

WITH member_total AS (
  SELECT member_id, SUM(amount) AS total
  FROM orders
  WHERE status = 'new'
  GROUP BY member_id
)
SELECT member_id, total
FROM member_total
WHERE total >= 5000
ORDER BY member_id;
 member_id | total
-----------+-------
       101 |  8000
       103 |  8000
(2 rows)

同じ内容をサブクエリ（FROM 句の派生テーブル）で書くと、内側から読むことになります。

SELECT member_id, total
FROM (SELECT member_id, SUM(amount) AS total
      FROM orders WHERE status = 'new' GROUP BY member_id) AS member_total
WHERE total >= 5000
ORDER BY member_id;

観点	サブクエリ	WITH 句（CTE）
読む順番	内側から外側へ	上から下へ
同じ結果を2回使う	2回書く	名前で何度でも参照できる
再帰	できない	WITH RECURSIVE でできる
有効な範囲	その場所だけ	その1文の中だけ
性能	多くの場合同じ実行計画になる	同じ。2回以上の参照は「性能」の節で差が出る

列名リストで名前を付け直す

WITH member_total(member_id, total) AS (
  SELECT member_id, SUM(amount) FROM orders WHERE status = 'new' GROUP BY member_id
)
SELECT * FROM member_total ORDER BY member_id;
 member_id | total
-----------+-------
       101 |  8000
       102 |  1000
       103 |  8000
(3 rows)

SELECT 側に AS total を書かなくても、列名リストで名前が付きます。列数が合わないと、PostgreSQL は ERROR: WITH query "t" has 1 columns available but 2 columns specified、MySQL は ERROR 1353 (HY000): In definition of view, derived table or common table expression, SELECT list and column names list have different column counts で止まります。再帰 CTE では、Oracle のように列名リストが必須の DB もあります（DB 対応表の節）。

WITH 句は「その1文だけ」で有効

CTE は次の文には残りません。2つ目の文で同じ名前を使うと、テーブルが無いというエラーになります。

WITH t AS (SELECT 1 AS n) SELECT n FROM t;
 n
---
 1
(1 row)

SELECT n FROM t;
ERROR:  relation "t" does not exist
LINE 1: SELECT n FROM t;
                      ^

MySQL 8.0 は ERROR 1146 (42S02): Table 'r34_demo.t' doesn't exist、SQLite は no such table: t です。複数の文で使い回すなら、ビュー・一時テーブル・マテリアライズドビューを使います（「CTE・サブクエリ・ビュー・一時テーブルの判断表」の節）。

WITH を書ける場所（文頭・サブクエリ・INSERT・CREATE VIEW）

WITH は文の先頭か、サブクエリの括弧の直後に書きます。FROM の後ろなど文の途中に書くと構文エラーです。

SELECT * FROM WITH t AS (SELECT 1 AS n) SELECT n FROM t;
ERROR:  syntax error at or near "WITH"
LINE 1: SELECT * FROM WITH t AS (SELECT 1 AS n) SELECT n FROM t;
                      ^

MySQL は ERROR 1064 (42000): You have an error in your SQL syntax; ... near 'WITH t AS (SELECT 1 AS n) SELECT n FROM t' at line 1 になります。書ける場所と、PostgreSQL 16・MySQL 8.0 で実行して通った形は次のとおりです。

場所	PostgreSQL 16	MySQL 8.0
SELECT の先頭	WITH t AS (...) SELECT ...	同じ
UPDATE・DELETE の先頭	WITH t AS (...) UPDATE ...／DELETE ...	同じ
INSERT と組み合わせる	WITH t AS (...) INSERT INTO x SELECT * FROM t	INSERT INTO x WITH t AS (...) SELECT * FROM t（WITH を前に置くと 1064）
サブクエリの中	WHERE member_id IN (WITH big AS (...) SELECT member_id FROM big)	同じ
CTE の中にさらに WITH	WITH o AS (WITH i AS (SELECT 5 AS n) SELECT n * 2 AS n FROM i) SELECT n FROM o	同じ（結果 10）
CREATE VIEW の本体	CREATE VIEW v AS WITH RECURSIVE tree AS (...) SELECT ...	同じ
CREATE TABLE AS	CREATE TABLE x AS WITH t AS (...) SELECT * FROM t	CREATE TABLE x WITH t AS (...) SELECT * FROM t
EXPLAIN	EXPLAIN WITH t AS (...) SELECT ...	同じ

よく使う形：ウィンドウ関数の結果で絞り込む

ROW_NUMBER() などの結果は、同じ SELECT の WHERE では使えません。WITH 句に入れてから絞るのが型です。会員ごとに金額が最大の注文を1件ずつ取る例です（PostgreSQL 16・MySQL 8.0 で同じ結果）。

WITH ranked AS (
  SELECT id, member_id, amount,
         ROW_NUMBER() OVER (PARTITION BY member_id ORDER BY amount DESC) AS rn
  FROM orders
)
SELECT id, member_id, amount FROM ranked WHERE rn = 1 ORDER BY member_id;
 id | member_id | amount
----+-----------+--------
  2 |       101 |   5000
  3 |       102 |   1000
  4 |       103 |   8000
(3 rows)

別名を直接 WHERE に書くと、PostgreSQL は ERROR: column "rn" does not exist、MySQL は ERROR 1054 (42S22): Unknown column 'rn' in 'where clause' です。WHERE が SELECT より先に評価されるためで、理由は関連記事「SQLの実行順序｜書く順番と違う理由と、エラーになる書き方」で扱っています。ウィンドウ関数そのものの書き方は関連記事「SQLウィンドウ関数の使い方｜累計・前月比・上位N件を実行結果で解説」で、グループごとの最新1件を取る他の方式との速度の比較は関連記事「SQLでグループごとに最新の1件を取得｜6方式をDB別に100万件で実測」で扱っています。

定数をまとめて渡す（VALUES の CTE）

しきい値などの定数を1か所にまとめると、同じ値を文の中に何度も書かずに済みます。

WITH params(min_amount, target_status) AS (VALUES (2500, 'new'))
SELECT o.id, o.amount
FROM orders o, params p
WHERE o.amount >= p.min_amount AND o.status = p.target_status
ORDER BY o.id;
 id | amount
----+--------
  1 |   3000
  2 |   5000
  4 |   8000
(3 rows)

MySQL 8.0 は AS (VALUES ...) の代わりに AS (SELECT 2500, 'new') と書くと、同じ3行になりました。

複数のCTEをつなげる・同じCTEを2回使う

カンマで区切ると複数の CTE を並べられ、後ろの CTE は前の CTE を参照できます。 会員ごとの合計（member_total）から平均（avg_total）を作り、平均より多い会員を出します。

WITH member_total AS (
  SELECT member_id, SUM(amount) AS total
  FROM orders WHERE status = 'new' GROUP BY member_id
), avg_total AS (
  SELECT AVG(total) AS avg_t FROM member_total      -- 前の CTE を参照
)
SELECT m.member_id, m.total, a.avg_t
FROM member_total m CROSS JOIN avg_total a
WHERE m.total > a.avg_t
ORDER BY m.member_id;
 member_id | total |         avg_t
-----------+-------+-----------------------
       101 |  8000 | 5666.6666666666666667
       103 |  8000 | 5666.6666666666666667
(2 rows)

MySQL 8.0 では avg_t が 5666.6667 と表示される以外は同じです。

同じ CTE を2回参照する例です。各会員の合計と、最大の会員との差を出します。

WITH member_total AS (
  SELECT member_id, SUM(amount) AS total
  FROM orders WHERE status = 'new' GROUP BY member_id
)
SELECT m.member_id, m.total, m.total - x.max_total AS diff_from_top
FROM member_total m
CROSS JOIN (SELECT MAX(total) AS max_total FROM member_total) x
ORDER BY m.member_id;
 member_id | total | diff_from_top
-----------+-------+---------------
       101 |  8000 |             0
       102 |  1000 |         -7000
       103 |  8000 |             0
(3 rows)

複数 CTE の決まり（実行して確かめた3点）

決まり	破ったときのエラー（原文）
WITH は文の先頭に1回だけ。2つ目以降は , 名前 AS (...)	PostgreSQL ERROR: syntax error at or near "WITH"、MySQL ERROR 1064 ... near 'WITH b AS (SELECT 2 AS n) SELECT * FROM a, b'
参照できるのは自分より前に書いた CTE だけ（PostgreSQL は WITH RECURSIVE を付けると後ろの CTE も参照できた。MySQL は付けても 1146）	PostgreSQL ERROR: relation "b" does not exist ＋ HINT: Use WITH RECURSIVE, or re-order the WITH items to remove forward references.、MySQL ERROR 1146 (42S02): Table 'r34_demo.b' doesn't exist
同じ名前の CTE は1つの WITH に1回まで	PostgreSQL ERROR: WITH query name "t" specified more than once、MySQL ERROR 1066 (42000): Not unique table/alias: 't'

CTE の名前は、同じ名前のテーブルより優先されます。 WITH staff AS (SELECT 999 AS id, 'CTEの行' AS name) SELECT * FROM staff; は、両 DB とも本物の staff ではなく CTE の1行（999）を返しました。MySQL のドキュメントも「CTE はベーステーブル・TEMPORARY テーブル・ビューを隠す」と説明しています。既存のテーブルと同じ名前は付けないほうが読み違えを防げます。

再帰CTE（WITH RECURSIVE）の仕組み

再帰 CTE は「最初の行（アンカー）」と「直前に増えた行から次の行を作る部分（再帰部）」を UNION ALL でつないだものです。

WITH RECURSIVE 名前 AS (
  SELECT ...                                  -- ① アンカー（非再帰項）：1回だけ実行
  UNION ALL
  SELECT ... FROM 表 JOIN 名前 ON ...          -- ② 再帰部：直前に増えた行だけを使って次の行を作る
)
SELECT * FROM 名前;

PostgreSQL のドキュメントは、評価の手順を次のように説明しています。①アンカーを評価して結果に入れ、同じ行を「作業表」にも置く。②作業表が空でない間、作業表の中身を自己参照の代わりにして再帰部を評価し、出た行を結果と「中間表」に入れ、中間表で作業表を置き換える。再帰部が0行を返した時点で作業表が空になり、終わります。 開発部長（id = 2）の部下を全員たどる場合、行はこう増えていきます。

flowchart TD
  A["アンカー: id = 2 の行を取る"] --> W1["作業表: 開発部長 (depth 0)"]
  W1 --> R1["再帰部: manager_id が作業表の id に一致する行を取る"]
  R1 --> W2["作業表: 開発リーダー (depth 1)"]
  W2 --> R2["再帰部をもう一度"]
  R2 --> W3["作業表: 開発メンバーA と 開発メンバーB (depth 2)"]
  W3 --> R3["再帰部をもう一度"]
  R3 --> W4["作業表: 0 行"]
  W4 --> E["終了。結果は 1 + 1 + 2 = 4 行"]

再帰部は「結果の全行」ではなく「直前の1回で増えた行」だけを見ます。だから同じ行を何度も処理せず、深さを1つずつ進められます。

再帰クエリの定番：組織ツリー（部下・上司・深さ・パス）

部下を全員たどる（深さ付き）

WITH RECURSIVE subordinates AS (
  SELECT id, name, manager_id, 0 AS depth
  FROM staff WHERE id = 2                                      -- アンカー：起点
  UNION ALL
  SELECT s.id, s.name, s.manager_id, sub.depth + 1
  FROM staff s JOIN subordinates sub ON s.manager_id = sub.id  -- 再帰部：直前の行の部下
)
SELECT id, name, depth FROM subordinates ORDER BY depth, id;
 id |     name      | depth
----+---------------+-------
  2 | 開発部長      |     0
  4 | 開発リーダー  |     1
  5 | 開発メンバーA |     2
  6 | 開発メンバーB |     2
(4 rows)

MySQL 8.0・SQLite 3.45 でも同じ4行です。s.manager_id = sub.id（子の親＝直前の行）で下へ、s.id = a.manager_id（直前の行の親）で上へたどります。起点自身を除くなら、本体に WHERE depth > 0 を付けます。

上司を全員たどる（逆方向）

WITH RECURSIVE ancestors AS (
  SELECT id, name, manager_id, 0 AS depth FROM staff WHERE id = 6
  UNION ALL
  SELECT s.id, s.name, s.manager_id, a.depth + 1
  FROM staff s JOIN ancestors a ON s.id = a.manager_id
)
SELECT id, name, depth FROM ancestors ORDER BY depth;
 id |     name      | depth
----+---------------+-------
  6 | 開発メンバーB |     0
  4 | 開発リーダー  |     1
  2 | 開発部長      |     2
  1 | 社長          |     3
(4 rows)

承認ルートやカテゴリの親をたどる処理と同じ形です。manager_id が NULL の社長で結合相手が無くなり、再帰部が0行になって終わります。

深さを制限する

深さの上限を再帰部の WHERE に書くと、その段で止まります。社長から2段目までを出す例です。

WITH RECURSIVE tree AS (
  SELECT id, name, 0 AS depth FROM staff WHERE id = 1
  UNION ALL
  SELECT s.id, s.name, t.depth + 1 FROM staff s JOIN tree t ON s.manager_id = t.id
  WHERE t.depth < 1
)
SELECT id, name, depth FROM tree ORDER BY depth, id;
 id |   name   | depth
----+----------+-------
  1 | 社長     |     0
  2 | 開発部長 |     1
  3 | 営業部長 |     1
(3 rows)

本体の WHERE で絞ると全段たどってから捨てるので、再帰部の WHERE に書くほうが読む行数が減ります。

パス文字列（パンくず）：PostgreSQL と MySQL で違う型のエラー

親のパスに自分の名前をつなぐと、パンくずリストになります。ここで DB ごとの型の扱いの違いが出ます。PostgreSQL は、アンカーと再帰部で列の型が完全に一致していることを要求します。

WITH RECURSIVE tree AS (
  SELECT id, name, CAST(name AS VARCHAR(200)) AS path, 1 AS lvl
  FROM staff WHERE manager_id IS NULL
  UNION ALL
  SELECT s.id, s.name, CONCAT(t.path, ' > ', s.name), t.lvl + 1
  FROM staff s JOIN tree t ON s.manager_id = t.id
)
SELECT id, lvl, path FROM tree ORDER BY path;
ERROR:  recursive query "tree" column 3 has type character varying(200) in non-recursive term but type character varying overall
LINE 2:   SELECT id, name, CAST(name AS VARCHAR(200)) AS path, 1 AS ...
                           ^
HINT:  Cast the output of the non-recursive term to the correct type.

アンカーを CAST(name AS text) にそろえると通ります。

WITH RECURSIVE tree AS (
  SELECT id, name, CAST(name AS text) AS path, 1 AS lvl
  FROM staff WHERE manager_id IS NULL
  UNION ALL
  SELECT s.id, s.name, t.path || ' > ' || s.name, t.lvl + 1
  FROM staff s JOIN tree t ON s.manager_id = t.id
)
SELECT id, lvl, path FROM tree ORDER BY path;
 id | lvl |                      path
----+-----+------------------------------------------------
  1 |   1 | 社長
  3 |   2 | 社長 > 営業部長
  7 |   3 | 社長 > 営業部長 > 営業メンバー
  2 |   2 | 社長 > 開発部長
  4 |   3 | 社長 > 開発部長 > 開発リーダー
  5 |   4 | 社長 > 開発部長 > 開発リーダー > 開発メンバーA
  6 |   4 | 社長 > 開発部長 > 開発リーダー > 開発メンバーB
(7 rows)

同じエラーは数値や日付でも出ます。SELECT 1 AS n UNION ALL SELECT n + 0.5 は column 1 has type integer in non-recursive term but type numeric overall、SELECT DATE '2026-10-01' UNION ALL SELECT d + INTERVAL '1 day' は has type date in non-recursive term but type timestamp without time zone overall でした。HINT のとおり、アンカー側を CAST して再帰部の型に合わせます。

MySQL は逆に、列の型と幅がアンカーだけで決まります（ドキュメント：「CTE の結果列の型は非再帰 SELECT 部だけから決まり、再帰 SELECT 部は無視される」）。CAST を付けないと、VARCHAR(20) の name の幅に長いパスが入りきらず、エラーになります。

WITH RECURSIVE tree AS (
  SELECT id, name, name AS path, 1 AS lvl FROM staff WHERE manager_id IS NULL
  UNION ALL
  SELECT s.id, s.name, CONCAT(t.path, ' > ', s.name), t.lvl + 1 FROM staff s JOIN tree t ON s.manager_id = t.id
)
SELECT id, lvl, path FROM tree ORDER BY path;
ERROR 1406 (22001): Data too long for column 'path' at row 1

アンカーで CAST(name AS CHAR(200)) AS path と幅を確保すると、PostgreSQL と同じ7行になりました。ドキュメントによると、厳格モードでない設定ではエラーにならず切り詰められます。同じ理由で、MySQL は SELECT 1 AS n UNION ALL SELECT n + 0.5 がエラーにならず、INT 列として 1・2・3 を返しました。

表示順を親の直後に子が来る順にする（ORDER BY path・SEARCH 句）

ORDER BY path でツリーの表示順に並ぶのがパス文字列の利点です。PostgreSQL 14 以降は SEARCH 句で、並べ替え用の列を自動で作れます。

WITH RECURSIVE tree AS (
  SELECT id, name, manager_id, 0 AS depth FROM staff WHERE manager_id IS NULL
  UNION ALL
  SELECT s.id, s.name, s.manager_id, t.depth + 1 FROM staff s JOIN tree t ON s.manager_id = t.id
) SEARCH DEPTH FIRST BY id SET ord
SELECT id, repeat('  ', depth) || name AS indented, ord FROM tree ORDER BY ord;
 id |      indented       |        ord
----+---------------------+-------------------
  1 | 社長                | {(1)}
  2 |   開発部長          | {(1),(2)}
  4 |     開発リーダー    | {(1),(2),(4)}
  5 |       開発メンバーA | {(1),(2),(4),(5)}
  6 |       開発メンバーB | {(1),(2),(4),(6)}
  3 |   営業部長          | {(1),(3)}
  7 |     営業メンバー    | {(1),(3),(7)}
(7 rows)

SEARCH BREADTH FIRST BY id SET ord にすると ord が (0,1)、(1,2)、(1,3)、(2,4)… と「深さ, id」の組になり、同じ段の行がまとまって並びます。ドキュメントは「再帰の評価は幅優先の順に出るが、それは実装の都合で、各段の中の順序は決まっていない」と注意しているので、順序が要るときは ORDER BY を付けます。SQLite は再帰部の ORDER BY で深さ優先・幅優先を切り替えられ（ORDER BY 3 DESC で深さ優先）、MySQL はパス文字列で並べます。

再帰クエリの定番：連番・日付（カレンダー）の生成

連番を作る

WITH RECURSIVE seq AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM seq WHERE n < 5    -- 終了条件
)
SELECT n FROM seq;
 n
---
 1
 2
 3
 4
 5
(5 rows)

PostgreSQL には SELECT n FROM generate_series(1, 5) AS n; という関数もあり、連番だけならそちらが短く書けます。

MySQL は 1000 回を超える再帰が既定ではエラーになります。 2000 までの連番を作ろうとした結果です。

WITH RECURSIVE seq AS (SELECT 1 AS n UNION ALL SELECT n + 1 FROM seq WHERE n < 2000)
SELECT MAX(n) FROM seq;
ERROR 3636 (HY000): Recursive query aborted after 1001 iterations. Try increasing @@cte_max_recursion_depth to a larger value.

上限を広げる方法は2つあり、どちらも 2000 が返りました。

-- 方法1：この文だけ上限を上げる（オプティマイザヒント）
WITH RECURSIVE seq AS (SELECT 1 AS n UNION ALL SELECT n + 1 FROM seq WHERE n < 2000)
SELECT /*+ SET_VAR(cte_max_recursion_depth = 3000) */ MAX(n) FROM seq;

-- 方法2：セッションの上限を上げる
SET SESSION cte_max_recursion_depth = 10000;

方法1は、その文にしか効かないので、必要な場所だけ広げられます。グローバルに変える方法は、やってはいけない操作の節で触れます。

日付を作って売上0の日を埋める（カレンダー）

sales には 10 月 3 日・5 日・6 日の行がありません。日付を再帰で作り、売上を LEFT JOIN すると、売上の無い日も 0 で出せます。

-- PostgreSQL 16（再帰部の型を date にそろえるため ::date を付ける）
WITH RECURSIVE calendar AS (
  SELECT DATE '2026-10-01' AS d
  UNION ALL
  SELECT (d + 1)::date FROM calendar WHERE d < DATE '2026-10-07'
)
SELECT c.d, COALESCE(SUM(s.amount), 0) AS amount
FROM calendar c LEFT JOIN sales s ON s.sold_on = c.d
GROUP BY c.d ORDER BY c.d;
     d      | amount
------------+--------
 2026-10-01 |   1200
 2026-10-02 |    800
 2026-10-03 |      0
 2026-10-04 |   1500
 2026-10-05 |      0
 2026-10-06 |      0
 2026-10-07 |    600
(7 rows)

DB	再帰部の「翌日」の書き方	結果
PostgreSQL 16	SELECT (d + 1)::date FROM calendar WHERE d < DATE '2026-10-07'	上の7行
MySQL 8.0	SELECT d + INTERVAL 1 DAY FROM calendar WHERE d < '2026-10-07'	同じ7行
SQLite 3.45	SELECT date(d, '+1 day') FROM cal WHERE d < '2026-10-07'（アンカーは '2026-10-01' の文字列）	同じ7行

PostgreSQL では generate_series(DATE '2026-10-01', DATE '2026-10-07', INTERVAL '1 day') でも同じ日付の列を作れます。日付の関数は関連記事「SQL 日付関数 一覧｜4DB比較表とコピペSQL・実行結果つき」で、売上の無い日を残す LEFT JOIN と INNER JOIN の違いは関連記事「SQL INNER JOINとLEFT JOINの違い｜JOIN種類早見表と落とし穴」で扱っています。

閉路（循環）と無限ループを止める方法

routes の B→C→D→B のように、データに閉路があると、訪問済みのチェックをしない再帰は止まりません。DB ごとに止まり方が違うので、実際に動かしました。

-- 終了条件のない再帰（B→C→D→B→… と回り続ける）
WITH RECURSIVE walk AS (
  SELECT src, dst FROM routes WHERE src = 'B'
  UNION ALL
  SELECT r.src, r.dst FROM routes r JOIN walk w ON r.src = w.dst
)
SELECT COUNT(*) FROM walk;

-- MySQL 8.0：既定の上限 1000 回で止まる
ERROR 3636 (HY000): Recursive query aborted after 1001 iterations. Try increasing @@cte_max_recursion_depth to a larger value.

-- PostgreSQL 16：上限が無いので、SET statement_timeout = '2s' を設定してから実行
ERROR:  canceling statement due to statement timeout

止め方の比較

止め方	書き方	向いている場面
終了条件	WHERE n < 上限／WHERE t.depth < 10	連番・カレンダー・深さの決まった階層。まずこれ
訪問済みチェック	PostgreSQL の CYCLE 句、配列、MySQL は文字列	経路の列挙、閉路の検出
UNION で重複を除く	UNION ALL を UNION に	同じ行が繰り返し出るだけの閉路（depth 列があると効かない）
時間の上限	PostgreSQL SET statement_timeout = '5s'、MySQL /*+ MAX_EXECUTION_TIME(5000) */	試すときの安全装置
回数の上限	MySQL cte_max_recursion_depth、SQL Server OPTION (MAXRECURSION n)、BigQuery は 500 回	最後の安全装置
LIMIT	PostgreSQL は本体に LIMIT n、MySQL 8.0.19 以降・SQLite は再帰部に LIMIT n	試しに数行だけ見る

MySQL の時間の上限は、SELECT に MAX_EXECUTION_TIME ヒントを付けると ERROR 3024 (HY000): Query execution was interrupted, maximum statement execution time exceeded で止まりました。PostgreSQL の LIMIT について、ドキュメントは「親クエリが取り出す分しか WITH 句を評価しないので止まるが、他のシステムでは違う動きをするため本番では勧めない。外側で並べ替えや結合をすると全行を取りに行くので、たいてい止まらない」と説明しています。

PostgreSQL 14 以降の CYCLE 句

CYCLE 列 SET フラグ列 USING パス列 を再帰 CTE の閉じ括弧の後ろに書くと、「その列の値がパス内で2回目に出た行」にフラグを立て、その先はたどりません。

WITH RECURSIVE walk(src, dst, depth) AS (
  SELECT src, dst, 1 FROM routes WHERE src = 'B'
  UNION ALL
  SELECT r.src, r.dst, w.depth + 1 FROM routes r JOIN walk w ON r.src = w.dst
) CYCLE dst SET is_cycle USING path
SELECT src, dst, depth, is_cycle, path FROM walk;
 src | dst | depth | is_cycle |       path
-----+-----+-------+----------+-------------------
 B   | C   |     1 | f        | {(C)}
 C   | D   |     2 | f        | {(C),(D)}
 D   | B   |     3 | f        | {(C),(D),(B)}
 B   | C   |     4 | t        | {(C),(D),(B),(C)}
(4 rows)

4行目で C が2回目に出て is_cycle = t になり、そこで止まりました。WHERE NOT is_cycle を本体に付ければ閉路の行を除けます。組織データの manager_id が誤って循環していないかの点検にも、この形がそのまま使えます。

MySQL・SQLite は訪問済みの文字列で同じことをする

WITH RECURSIVE walk AS (
  SELECT src, dst, 1 AS depth, CAST(CONCAT(',', dst, ',') AS CHAR(200)) AS visited, 0 AS is_cycle
  FROM routes WHERE src = 'B'
  UNION ALL
  SELECT r.src, r.dst, w.depth + 1, CONCAT(w.visited, r.dst, ','),
         LOCATE(CONCAT(',', r.dst, ','), w.visited) > 0
  FROM routes r JOIN walk w ON r.src = w.dst
  WHERE w.is_cycle = 0
)
SELECT src, dst, depth, is_cycle, visited FROM walk;
+------+------+-------+----------+-----------+
| src  | dst  | depth | is_cycle | visited   |
+------+------+-------+----------+-----------+
| B    | C    |     1 |        0 | ,C,       |
| C    | D    |     2 |        0 | ,C,D,     |
| D    | B    |     3 |        0 | ,C,D,B,   |
| B    | C    |     4 |        1 | ,C,D,B,C, |
+------+------+-------+----------+-----------+

PostgreSQL の CYCLE 句と同じ4行です。区切り文字で前後を囲んで ,B, で探すのは、B と BB のような名前を取り違えないためです。

UNION に変えると止まる場合と止まらない場合

WITH RECURSIVE walk AS (
  SELECT src, dst FROM routes WHERE src = 'B'
  UNION
  SELECT r.src, r.dst FROM routes r JOIN walk w ON r.src = w.dst
)
SELECT src, dst FROM walk;
 src | dst
-----+-----
 B   | C
 C   | D
 D   | B
(3 rows)

UNION は「結果にすでにある行と同じ行」を捨てるので、(B, C) が2回目に出た時点で新しい行が増えず終わります（PostgreSQL・MySQL とも3行）。depth やパスの列を持つと毎回違う行になるため、UNION では止まりません。 その場合は訪問済みチェックを使います。UNION と UNION ALL の違いは関連記事「SQL UNIONとUNION ALLの違い｜実行結果つき使い方とエラー対処」で扱っています。

経路をすべて列挙する（訪問済みを配列で持つ）

A から D への経路と所要時間を全部出す例です。訪問済みの点を持ち回り、同じ点に戻る行を作らないことで閉路を避けます。

-- PostgreSQL 16：配列で持つ
WITH RECURSIVE paths AS (
  SELECT src, dst, minutes, ARRAY[src::text, dst::text] AS route
  FROM routes WHERE src = 'A'
  UNION ALL
  SELECT p.src, r.dst, p.minutes + r.minutes, p.route || r.dst::text
  FROM paths p JOIN routes r ON r.src = p.dst
  WHERE NOT r.dst = ANY(p.route)
)
SELECT array_to_string(route, '→') AS route, minutes
FROM paths WHERE dst = 'D' ORDER BY minutes;
  route  | minutes
---------+---------
 A→B→C→D |      30
 A→C→D   |      35
(2 rows)

MySQL 8.0 は配列が無いので、CAST(CONCAT(',', src, ',', dst, ',') AS CHAR(200)) の文字列を持ち回り、LOCATE(CONCAT(',', r.dst, ','), p.route) = 0 で絞ると、A,B,C,D（30）と A,C,D（35）の同じ2行になりました。最短経路だけなら ORDER BY minutes LIMIT 1 を付けます。点や辺が多いグラフは組み合わせが急に増えるので、深さの上限も付けます。

再帰部に書けるもの・書けないもの（DB別）

PostgreSQL 16・MySQL 8.0・SQLite 3.45 は実行して確かめ、SQL Server・Oracle はドキュメントの記載です。

再帰部に書くもの	PostgreSQL 16	MySQL 8.0	SQLite 3.45	SQL Server／Oracle
集計関数・GROUP BY	不可	不可（ERROR 3575）	不可	不可／不可
ウィンドウ関数	書ける（型はアンカーに合わせる）	不可（ERROR 3575）	不可	―／分析関数は書ける
DISTINCT	書ける	不可（ERROR 1235）	書ける	不可／不可
ORDER BY	書ける（括弧で囲む）	不可（ERROR 1235）	書ける（深さ優先・幅優先の制御）	―／―
LIMIT・TOP	書ける（括弧で囲む）	8.0.19 以降は書ける	書ける（止める用途に使える）	TOP 不可／―
自分を2回参照	不可	不可（ERROR 3577）	不可	不可／不可（ちょうど1回）
サブクエリの中で自分を参照	不可	不可（ERROR 3577）	不可	不可／不可
外部結合の右側に自分	不可	不可（ERROR 3576）	―	外部結合そのものが不可／右側は不可

エラー文の原文は「エラー原文と直し方」の節にまとめています。集計は再帰 CTE の外（本体の SELECT）で行う、と覚えておくと迷いません。

性能：MATERIALIZED／NOT MATERIALIZED と EXPLAIN の実物（100万行）

PostgreSQL のドキュメントは、CTE の扱いをこう決めています。非再帰で副作用の無い（揮発性関数を含まない SELECT の）CTE は、親クエリから1回しか参照されなければ親に畳み込まれ（展開）、2回以上参照されると畳み込まれず、1回だけ計算した結果が保持されます。 12 以降は MATERIALIZED（必ず保持）と NOT MATERIALIZED（必ず展開）で上書きできます。100 万行の big_orders（id が主キー、member_id にインデックス）で、EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) の実物を見ます。

1回参照は展開され、外側の条件が CTE の中まで届く

WITH t AS (SELECT * FROM big_orders)
SELECT * FROM t WHERE id = 1;
 Index Scan using big_orders_pkey on big_orders (actual rows=1 loops=1)
   Index Cond: (id = 1)
 Execution Time: 0.100 ms

WITH t AS MATERIALIZED (SELECT * FROM big_orders)
SELECT * FROM t WHERE id = 1;
 CTE Scan on t (actual rows=1 loops=1)
   Filter: (id = 1)
   Rows Removed by Filter: 999999
   CTE t
     ->  Seq Scan on big_orders (actual rows=1000000 loops=1)
 Execution Time: 159.751 ms

1つ目は条件 id = 1 が CTE の中まで届き、主キーのインデックスで1行だけ読んでいます。MATERIALIZED を付けた2つ目は 100 万行を先に読んでから絞っており、1600 倍の時間がかかりました。

巨大な CTE を2回参照すると遅くなる（既定で保持されるため）

同じ CTE を2回参照すると、既定では保持されます。「全件の CTE」を2つの集計から参照した例です。

WITH t AS (SELECT * FROM big_orders)
SELECT (SELECT COUNT(*) FROM t WHERE member_id = 1) AS m1,
       (SELECT COUNT(*) FROM t WHERE member_id = 2) AS m2;
 Result (actual rows=1 loops=1)
   CTE t
     ->  Seq Scan on big_orders (actual rows=1000000 loops=1)
   InitPlan 2 (returns $1)
     ->  Aggregate (actual rows=1 loops=1)
           ->  CTE Scan on t (actual rows=100 loops=1)
                 Filter: (member_id = 1)
                 Rows Removed by Filter: 999900
   InitPlan 3 (returns $2)
     ->  Aggregate (actual rows=1 loops=1)
           ->  CTE Scan on t t_1 (actual rows=100 loops=1)
                 Filter: (member_id = 2)
                 Rows Removed by Filter: 999900
 Execution Time: 201.215 ms

100 万行を1回読んで保持し、2つの参照がそれぞれ 100 万行をフィルタしています。member_id のインデックスは使われていません。直し方は2つあり、どちらも実行して確かめました。

-- 直し方1：NOT MATERIALIZED で展開し、参照ごとにインデックスを使わせる
WITH t AS NOT MATERIALIZED (SELECT * FROM big_orders)
SELECT (SELECT COUNT(*) FROM t WHERE member_id = 1) AS m1,
       (SELECT COUNT(*) FROM t WHERE member_id = 2) AS m2;
 Result (actual rows=1 loops=1)
   InitPlan 1 (returns $0)
     ->  Aggregate (actual rows=1 loops=1)
           ->  Index Only Scan using idx_big_orders_member on big_orders (actual rows=100 loops=1)
                 Index Cond: (member_id = 1)
   InitPlan 2 (returns $1)
     ->  Aggregate (actual rows=1 loops=1)
           ->  Index Only Scan using idx_big_orders_member on big_orders big_orders_1 (actual rows=100 loops=1)
                 Index Cond: (member_id = 2)
 Execution Time: 0.069 ms

-- 直し方2：絞り込みの条件を CTE の中に入れ、保持する行を減らす
WITH t AS (SELECT * FROM big_orders WHERE member_id IN (1, 2))
SELECT (SELECT COUNT(*) FROM t WHERE member_id = 1) AS m1,
       (SELECT COUNT(*) FROM t WHERE member_id = 2) AS m2;
   CTE t
     ->  Bitmap Heap Scan on big_orders (actual rows=200 loops=1)
           ->  Bitmap Index Scan on idx_big_orders_member (actual rows=200 loops=1)
                 Index Cond: (member_id = ANY ('{1,2}'::integer[]))
 Execution Time: 1.192 ms

201 ms が 0.069 ms（直し方1）と 1.2 ms（直し方2）になりました。

重い集計を2回参照するときは、既定（保持）のほうが速い

逆に、CTE の中身が重い集計なら、1回計算して使い回す既定の動きが有利です。会員ごとの合計を出し、平均より多い上位3人を取る例です。

指定	実行計画の要点	Execution Time
既定（2回参照なので保持）	CTE agg の HashAggregate が1回。2つの参照は CTE Scan on agg	119.875 ms
NOT MATERIALIZED	HashAggregate と Seq Scan が2回（平均用と本体用）	229.837 ms

WITH agg AS (SELECT member_id, SUM(amount) AS total FROM big_orders GROUP BY member_id)
SELECT a.member_id, a.total
FROM agg a
WHERE a.total > (SELECT AVG(total) FROM agg)
ORDER BY a.total DESC LIMIT 3;

MATERIALIZED を付けるかの判断

flowchart TD
  A["CTE を書いた"] --> B{"参照は1回だけ?"}
  B -- "はい" --> C["既定で展開される"]
  B -- "2回以上" --> D{"中身は重い集計?"}
  D -- "はい" --> E["既定の保持のまま"]
  D -- "軽い SELECT" --> F["NOT MATERIALIZED で展開"]
  C --> G{"外側の条件で重い計算が増える?"}
  G -- "はい" --> H["MATERIALIZED で1回に固定"]
  G -- "いいえ" --> I["そのまま"]

既定で展開されると外側の条件が CTE の中まで届き、NOT MATERIALIZED で展開すると参照ごとにインデックスが効きます。

指定	動き	使いどころ
なし（既定）	1回参照なら展開、2回以上なら保持。再帰 CTE と副作用のある CTE は常に保持	まずこれで EXPLAIN を見る
MATERIALIZED	必ず先に計算して保持	重い計算を1回にしたい。外側の条件で計算が増えるのを防ぎたい
NOT MATERIALIZED	2回参照でも展開	参照ごとに違う条件でインデックスを使わせたい

揮発性関数の扱いも確かめました。WITH r AS (SELECT random() AS v) SELECT a.v = b.v FROM r a, r b; は PostgreSQL・MySQL（RAND()）とも真を返し、CTE が1回だけ評価されて同じ値が2回使われています。

MySQL 8.0 の扱い（マージと実体化、NO_MERGE ヒント）

MySQL は CTE を派生テーブルと同じ規則で扱います。ドキュメントによると、集計・ウィンドウ関数・DISTINCT・GROUP BY・HAVING・LIMIT・UNION・SELECT リストのサブクエリなどを含まない CTE は親にマージされ、含む CTE は実体化（内部一時テーブルに保持）されます。実体化される CTE は、何回参照されても1回だけ実体化され、再帰 CTE は常に実体化されます。 EXPLAIN ANALYZE の実物です。

-- 単純な CTE を2回参照：参照ごとにマージされ、どちらもインデックスを使う
WITH t AS (SELECT * FROM big_orders)
SELECT (SELECT COUNT(*) FROM t WHERE member_id = 1) AS m1,
       (SELECT COUNT(*) FROM t WHERE member_id = 2) AS m2;
-> Select #2 (subquery in projection; run only once)
    -> Aggregate: count(0)  (actual time=0.0338..0.0338 rows=1 loops=1)
        -> Covering index lookup on big_orders using idx_big_orders_member (member_id=1)  (actual time=0.016..0.0264 rows=100 loops=1)
-> Select #4 (subquery in projection; run only once)
    -> Aggregate: count(0)  (actual time=0.0224..0.0225 rows=1 loops=1)
        -> Covering index lookup on big_orders using idx_big_orders_member (member_id=2)  (actual time=0.0108..0.0174 rows=100 loops=1)

-- NO_MERGE ヒントで実体化を強制：100万行の一時テーブルを1回作り、2回目の参照は再利用
WITH t AS (SELECT * FROM big_orders)
SELECT (SELECT /*+ NO_MERGE(t) */ COUNT(*) FROM t WHERE member_id = 1) AS m1,
       (SELECT /*+ NO_MERGE(t) */ COUNT(*) FROM t WHERE member_id = 2) AS m2;
-> Select #2 (subquery in projection; run only once)
    -> Aggregate: count(0)  (actual time=6520..6520 rows=1 loops=1)
        -> Index lookup on t using <auto_key0> (member_id=1)  (actual time=6519..6520 rows=100 loops=1)
            -> Materialize CTE t if needed  (actual time=6519..6519 rows=1e+6 loops=1)
                -> Table scan on big_orders  (actual time=0.396..830 rows=1e+6 loops=1)
-> Select #4 (subquery in projection; run only once)
    -> Aggregate: count(0)  (actual time=0.327..0.327 rows=1 loops=1)
        -> Index lookup on t using <auto_key0> (member_id=2)  (actual time=0.0374..0.316 rows=100 loops=1)
            -> Materialize CTE t if needed (query plan printed elsewhere)  (never executed)

実体化した一時テーブルには、ドキュメントのとおり自動でインデックス（<auto_key0>）が付きましたが、100 万行の実体化そのものに 6.5 秒かかっています。集計を含む CTE（上の agg の例）も MySQL では Materialize CTE agg if needed が1回だけ出て、2回目の参照は (query plan printed elsewhere) (never executed) と表示されました。MySQL には MATERIALIZED キーワードは無く、書くと ERROR 1064 になります。マージを止めたいときは NO_MERGE(名前)、マージさせたいときは MERGE(名前) のヒントを、その CTE を参照するクエリブロックの SELECT に書きます。実行計画の読み方は関連記事「MySQL EXPLAINの見方｜type・key・rows・Extra早見表」で扱っています。

SQL Server のドキュメントは「CTE の結果は実体化されず、外側の参照ごとに定義のクエリが再実行される。複数回参照するなら一時オブジェクトの利用を検討する」と説明しています。SQLite は 3.35.0 から PostgreSQL と同じ MATERIALIZED／NOT MATERIALIZED ヒントを受け付け、指定が無ければプランナが選びます。

CTE・サブクエリ・ビュー・一時テーブルの判断表

観点	CTE（WITH）	サブクエリ	ビュー	一時テーブル
有効な範囲	その1文	その場所	定義を消すまで	セッション（接続）中
再帰	できる	できない	定義の中で CTE を使える	できない（INSERT で積む）
同じ結果の複数回参照	名前で参照。計算は1回か展開	書くたびに評価	参照ごとに展開	1回作って何度でも
インデックス	付けられない（MySQL は自動）	付けられない	元の表のものを使う	付けられる
向く場面	1文の中を読みやすくする、再帰、更新対象の絞り込み	短い条件、EXISTS	複数の文・複数の人で同じ定義を使う	大きな中間結果を複数の文で使う、統計を取りたい

状況	選ぶもの
1つの文の中で、同じ集計を2回以上使う	CTE（重い集計なら既定の保持のまま）
階層・連番・経路をたどる	再帰 CTE
同じ中間結果を、続く複数の文でも使う	一時テーブル（必要ならインデックスを付ける）
同じ定義を複数のプログラム・人で共有する	ビュー。集計が重く更新が少ないならマテリアライズドビュー
条件が1〜2行で済む	サブクエリ（EXISTS・IN）

一時テーブルとの違いは関連記事「SQL一時テーブルの作成｜TEMPORARY TABLEとCTEの違い」、ビューは関連記事「SQLビューとマテリアライズドビュー｜違い・REFRESH・使い分け」、サブクエリは関連記事「SQLサブクエリの使い方｜相関サブクエリ・EXISTS・JOIN書き換え」で扱っています。

UPDATE・DELETE・INSERTと組み合わせる（データ変更CTE）

更新対象を CTE で絞ってから UPDATE する

キャンセルがある会員の新規注文を「確認待ち（review）」にする例です。UPDATE の結合の書き方は DB で違い、CTE はそのまま結合相手になります。

-- PostgreSQL 16：UPDATE ... FROM と組み合わせる
WITH canceled_members AS (
  SELECT DISTINCT member_id FROM orders WHERE status = 'canceled'
)
UPDATE orders o SET status = 'review'
FROM canceled_members c
WHERE o.member_id = c.member_id AND o.status = 'new'
RETURNING o.id, o.member_id, o.status;
 id | member_id | status
----+-----------+--------
  4 |       103 | review
(1 row)

UPDATE 1

-- MySQL 8.0：UPDATE ... JOIN と組み合わせる
WITH canceled_members AS (
  SELECT DISTINCT member_id FROM orders WHERE status = 'canceled'
)
UPDATE orders o JOIN canceled_members c ON o.member_id = c.member_id
SET o.status = 'review'
WHERE o.status = 'new';
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0

SQLite 3.45 は UPDATE orders SET status = 'review' WHERE status = 'new' AND member_id IN (SELECT member_id FROM canceled_members) RETURNING id, status で、同じ id = 4 が返りました。別テーブルの値で更新する書き方の違いは、関連記事「SQLで別テーブルの値を使ってUPDATEする方法【DB別】」で扱っています。

再帰 CTE も更新に使えます。staff に depth 列を足して、全員の階層の深さを1文で入れた結果です（MySQL は UPDATE staff s JOIN tree t ON s.id = t.id SET s.depth = t.d、PostgreSQL は UPDATE staff s SET depth = t.d FROM tree t WHERE s.id = t.id）。

WITH RECURSIVE tree AS (
  SELECT id, 0 AS d FROM staff WHERE manager_id IS NULL
  UNION ALL
  SELECT s.id, t.d + 1 FROM staff s JOIN tree t ON s.manager_id = t.id
)
UPDATE staff s JOIN tree t ON s.id = t.id SET s.depth = t.d;
Query OK, 7 rows affected
Rows matched: 7  Changed: 7  Warnings: 0

DELETE で使う：MySQL のエラー 1093 を CTE で避ける

MySQL は、削除対象のテーブルを同じ文のサブクエリで直接参照するとエラーになります。CTE に入れると、先に実体化されるため通ります。

DELETE FROM orders WHERE id IN (SELECT id FROM orders WHERE amount < 4000);
ERROR 1093 (HY000): You can't specify target table 'orders' for update in FROM clause

WITH small AS (SELECT id FROM orders WHERE amount < 2500)
DELETE FROM orders WHERE id IN (SELECT id FROM small);
Query OK, 2 rows affected

複数テーブル形式の WITH big AS (...) DELETE o FROM orders o JOIN big b ON o.id = b.id; も通りました。PostgreSQL は同じ WITH small AS (...) DELETE FROM orders WHERE id IN (SELECT id FROM small) RETURNING id, amount; で id 3・5 の2行が返り、DELETE 2 でした。

INSERT で使う：MySQL は WITH の位置が違う

-- PostgreSQL 16
WITH new_rows AS (SELECT 10 AS id, 104 AS member_id, 700 AS amount, 'new' AS status)
INSERT INTO orders SELECT * FROM new_rows RETURNING *;
 id | member_id | amount | status
----+-----------+--------+--------
 10 |       104 |    700 | new
(1 row)

-- MySQL 8.0：WITH を INSERT の前に置くと構文エラー
WITH new_rows AS (SELECT 10 AS id, 104 AS member_id, 700 AS amount, 'new' AS status)
INSERT INTO orders SELECT * FROM new_rows;
ERROR 1064 (42000): You have an error in your SQL syntax; ... near 'INSERT INTO orders SELECT * FROM new_rows' at line 2

-- MySQL 8.0：INSERT INTO ... の直後に WITH を置く
INSERT INTO orders
WITH new_rows AS (SELECT 10 AS id, 104 AS member_id, 700 AS amount, 'new' AS status)
SELECT * FROM new_rows;
Query OK, 1 row affected

PostgreSQL だけ：WITH の中に DELETE・UPDATE を書く（RETURNING）

PostgreSQL は WITH の中に INSERT・UPDATE・DELETE を書け、その RETURNING の結果を本体で使えます。キャンセル済みの注文を履歴テーブルへ移す「削除と記録を1文で」の例です。

CREATE TABLE orders_archive (LIKE orders);

WITH moved AS (
  DELETE FROM orders WHERE status = 'canceled' RETURNING *
)
INSERT INTO orders_archive SELECT * FROM moved;
INSERT 0 1

SELECT * FROM orders_archive;
 id | member_id | amount |  status
----+-----------+--------+----------
  5 |       103 |   2000 | canceled
(1 row)

削除した行数と金額をその場で集計することもできます。

WITH moved AS (
  DELETE FROM orders WHERE amount < 2500 RETURNING *
)
SELECT count(*) AS deleted_rows, sum(amount) AS deleted_amount FROM moved;
 deleted_rows | deleted_amount
--------------+----------------
            1 |           1000
(1 row)

ドキュメントが定める決まりと、実際に確かめた動きです。

決まり（ドキュメント）	実行して確かめた結果
本体から参照できるのは RETURNING の出力。RETURNING が無い変更文は参照できないが、実行はされる	WITH d AS (DELETE FROM orders WHERE id = 99) SELECT * FROM d; → ERROR: WITH query "d" does not have a RETURNING clause。WITH d AS (DELETE FROM orders WHERE id = 1) SELECT 1; は通り、id = 1 は消えていた
変更文は本体が結果を読むかどうかに関係なく、必ず1回だけ最後まで実行される	上と同じ
WITH 内の文と本体は同じスナップショットで動き、互いの変更を見られない。変更を伝える手段は RETURNING だけ	WITH u AS (UPDATE orders SET amount = amount + 100 WHERE id = 1 RETURNING id, amount) SELECT u.amount, o.amount FROM u JOIN orders o ON o.id = u.id; → RETURNING 側は 3100、本体が読んだ orders は 3000（更新前）
データを変更する WITH は最上位の文にだけ書ける	サブクエリの中に書くと ERROR: WITH clause containing a data-modifying statement must be at the top level

MySQL は WITH の中に DELETE・UPDATE を書けず、WITH moved AS (DELETE FROM orders WHERE status = 'canceled') SELECT * FROM moved; は ERROR 1064 (42000): ... near 'DELETE FROM orders WHERE status = 'canceled') SELECT * FROM moved' at line 1 になりました。MySQL では UPDATE・DELETE の先頭に WITH を置く形だけが使えます。

更新・削除の前には、同じ WITH 句を SELECT にして対象行を確かめてから実行します。 BEGIN; → 変更 → 確認 → COMMIT;（戻すなら ROLLBACK;）の手順は、関連記事「SQL UPDATE文の書き方｜複数列・別テーブル・CASE・DB別構文の早見表」で扱っています。

DB対応表（MySQL・MariaDB・PostgreSQL・SQL Server・Oracle・SQLite・BigQuery）

MySQL・PostgreSQL・SQLite の欄はこの記事の実行結果、ほかは公式ドキュメントにもとづきます。

DB	対応版	RECURSIVE の書き方	再帰回数の上限と超えたとき	閉路の検出
MySQL	8.0 から（5.7 以前は構文エラー 1064）	必須	cte_max_recursion_depth（既定 1000）。超えると ERROR 3636	自作（訪問済み文字列）
MariaDB	10.2 から	必須	max_recursive_iterations（10.6 以降の既定 1000）	CYCLE 列 RESTRICT（独自の形）
PostgreSQL	8.4 から。MATERIALIZED は 12、SEARCH・CYCLE は 14	必須	上限なし。statement_timeout で止める	CYCLE 列 SET フラグ USING パス
SQL Server	対応	書かない。前の文はセミコロンで終える	OPTION (MAXRECURSION n)（既定 100、0〜32767、0 で無制限）。超えるとエラー 530	自作
Oracle	再帰は 11g R2 から	書かない。列名リストが必須	上限なし。閉路があると ORA-32044 で停止	CYCLE 列 SET フラグ TO 'Y' DEFAULT 'N'
SQLite	3.8.3 から。MATERIALIZED は 3.35.0	省略できる	上限なし。再帰部の LIMIT で止める	自作
BigQuery	対応	必須	500 回（引き上げはサポートへ依頼）。超えるとエラーで中断	自作

DB	WITH を置ける文	中間結果の保持	再帰部で使えないもの（要約）
MySQL 8.0	SELECT・UPDATE・DELETE の先頭、サブクエリ、INSERT ... WITH ... SELECT、CREATE TABLE・VIEW、EXPLAIN	最適化で自動。NO_MERGE で保持。再帰 CTE は常に保持	集計・ウィンドウ・GROUP BY・DISTINCT・ORDER BY（LIMIT は 8.0.19 から可）
PostgreSQL 16	SELECT・INSERT・UPDATE・DELETE・MERGE の先頭、サブクエリ、CREATE TABLE AS・VIEW、EXPLAIN。WITH の中に変更文も書ける	1回参照は展開、2回以上は保持。MATERIALIZED／NOT MATERIALIZED	集計、自分を2回参照、サブクエリ内の参照、外部結合の右側
SQL Server	SELECT・INSERT・UPDATE・DELETE・MERGE、CREATE VIEW	保持しない（参照ごとに再実行）	DISTINCT・GROUP BY・HAVING・集計・TOP・外部結合・サブクエリ
Oracle	SELECT、INSERT ... SELECT など	構文に指定句なし	DISTINCT・GROUP BY・集計、query_name を参照するサブクエリ、右側が query_name の外部結合
SQLite 3.45	SELECT・INSERT・UPDATE・DELETE	MATERIALIZED／NOT MATERIALIZED（3.35.0 から）	集計・ウィンドウ関数（ORDER BY・LIMIT は書ける）

SQL Server・Oracle の再帰の書き方（ドキュメントの構文）

-- SQL Server：RECURSIVE は書かない。前の文はセミコロンで終える。上限は OPTION で
WITH seq (n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM seq WHERE n < 500
)
SELECT n FROM seq
OPTION (MAXRECURSION 1000);

-- Oracle：RECURSIVE は書かず、列名リストを付ける。閉路は CYCLE 句で
WITH tree (id, name, manager_id) AS (
  SELECT id, name, manager_id FROM staff WHERE manager_id IS NULL
  UNION ALL
  SELECT s.id, s.name, s.manager_id FROM staff s JOIN tree t ON s.manager_id = t.id
)
CYCLE id SET is_cycle TO 'Y' DEFAULT 'N'
SELECT id, name, is_cycle FROM tree;

Oracle の CONNECT BY との対応

Oracle には再帰 CTE より前からある階層問い合わせ CONNECT BY があります。両者の対応です（Oracle のドキュメントにもとづく）。

やりたいこと	再帰 CTE	Oracle CONNECT BY
起点を決める	アンカーの WHERE	START WITH id = 1
親子をつなぐ	再帰部の JOIN 条件	CONNECT BY PRIOR id = manager_id（PRIOR は親の行の値）
深さを取る	depth + 1 の列	疑似列 LEVEL
パス文字列	path || ' > ' || name（MySQL は CONCAT）	SYS_CONNECT_BY_PATH(name, ' > ')
根の値を取る	根の列を持ち回る	CONNECT_BY_ROOT name
閉路	CYCLE 句（Oracle は ORA-32044 で停止）	CONNECT BY NOCYCLE ... と CONNECT_BY_ISCYCLE（付けないと ORA-01436: CONNECT BY loop in user data）
兄弟の並び順	SEARCH 句（PostgreSQL）／パス文字列	ORDER SIBLINGS BY name

WITH句のエラー原文と直し方

PostgreSQL 16.15・MySQL 8.0.46・SQLite 3.45.1 で実際に出したエラーです。SQL Server・Oracle の行はドキュメントの文面です。

書き方のエラー

エラー文（原文）	原因	直し方
PostgreSQL ERROR: syntax error at or near "WITH"／MySQL ERROR 1064 (42000): You have an error in your SQL syntax; ... near 'WITH t AS (SELECT 1 AS n) SELECT n FROM t' at line 1	WITH を文の途中（FROM の後ろなど）に書いた。または WITH を2回書いた	WITH は文の先頭か、サブクエリの括弧の直後に。2つ目以降の CTE はカンマで続ける
PostgreSQL ERROR: relation "t" does not exist／MySQL ERROR 1146 (42S02): Table 'r34_demo.t' doesn't exist／SQLite no such table: t	別の文で CTE を使った	同じ文の中で使う。複数の文ならビュー・一時テーブル
PostgreSQL ERROR: relation "seq" does not exist ＋ DETAIL: There is a WITH item named "seq", but it cannot be referenced from this part of the query. ＋ HINT: Use WITH RECURSIVE, or re-order the WITH items to remove forward references.／MySQL ERROR 1146 (42S02): Table 'r34_demo.seq' doesn't exist	自分を参照する CTE に RECURSIVE を付け忘れた。または後ろの CTE を参照した	WITH RECURSIVE にする。参照される CTE を前に書く
PostgreSQL ERROR: WITH query "t" has 1 columns available but 2 columns specified／MySQL ERROR 1353 (HY000): In definition of view, derived table or common table expression, SELECT list and column names list have different column counts／SQLite table t has 1 values for 2 columns	列名リストの数と SELECT の列数が違う	数をそろえる
PostgreSQL ERROR: WITH query name "t" specified more than once／MySQL ERROR 1066 (42000): Not unique table/alias: 't'／SQLite duplicate WITH table name: t	同じ名前の CTE を2回定義した	名前を変える
PostgreSQL ERROR: column "rn" does not exist／MySQL ERROR 1054 (42S22): Unknown column 'rn' in 'where clause'	ウィンドウ関数の別名を同じ SELECT の WHERE に書いた	ウィンドウ関数を CTE に入れ、本体の WHERE で絞る
MySQL ERROR 1064 (42000): ... near 'INSERT INTO orders SELECT * FROM new_rows' at line 2	MySQL で WITH を INSERT の前に置いた	INSERT INTO t WITH c AS (...) SELECT ... の順にする
MySQL ERROR 1064 (42000): ... near 'MATERIALIZED (SELECT 1 AS n) SELECT * FROM t' at line 1	MySQL に MATERIALIZED 句は無い	NO_MERGE(名前) ヒントを使う

再帰のエラー

エラー文（原文）	原因	直し方
PostgreSQL ERROR: recursive query "tree" column 3 has type character varying(200) in non-recursive term but type character varying overall ＋ HINT: Cast the output of the non-recursive term to the correct type.	アンカーと再帰部で列の型が違う（varchar(n) と text、integer と numeric、date と timestamp）	アンカー側を CAST して再帰部の型に合わせる
MySQL ERROR 1406 (22001): Data too long for column 'path' at row 1	列の幅がアンカーだけで決まり、再帰部で長くなった値が入らない	アンカーで CAST(name AS CHAR(200)) のように幅を確保
MySQL ERROR 3636 (HY000): Recursive query aborted after 1001 iterations. Try increasing @@cte_max_recursion_depth to a larger value.	終了条件が無い、閉路がある、または 1000 段を超える正しい再帰	終了条件・訪問済みチェックを入れる。正しい再帰なら /*+ SET_VAR(cte_max_recursion_depth = n) */
PostgreSQL ERROR: canceling statement due to statement timeout	statement_timeout を設定して無限ループを止めた	終了条件・CYCLE 句を入れる
MySQL ERROR 3024 (HY000): Query execution was interrupted, maximum statement execution time exceeded	MAX_EXECUTION_TIME ヒントで止めた	同上
MySQL ERROR 1317 (70100): Query execution was interrupted	別の接続から KILL QUERY で止めた	同上
PostgreSQL ERROR: recursive reference to query "t" must not appear within its non-recursive term／MySQL ERROR 3574 (HY000): Recursive Common Table Expression 't' should have one or more non-recursive query blocks followed by one or more recursive ones／SQLite circular reference: t	アンカー側で自分を参照した	自分を参照する SELECT は UNION ALL の後ろに置く
PostgreSQL ERROR: recursive query "t" does not have the form non-recursive-term UNION [ALL] recursive-term	UNION ALL が無い	アンカー UNION ALL 再帰部の形にする
PostgreSQL ERROR: each UNION query must have the same number of columns／MySQL ERROR 1222 (21000): The used SELECT statements have a different number of columns	アンカーと再帰部の列数が違う	列数をそろえる
PostgreSQL ERROR: aggregate functions are not allowed in a recursive query's recursive term／MySQL ERROR 3575 (HY000): Recursive Common Table Expression 't' can contain neither aggregation nor window functions in recursive query block／SQLite recursive aggregate queries not supported	再帰部に集計・ウィンドウ関数（MySQL）・GROUP BY	集計は本体の SELECT で行う
PostgreSQL ERROR: recursive reference to query "t" must not appear more than once／MySQL ERROR 3577 (HY000): In recursive query block of Recursive Common Table Expression 't', the recursive table must be referenced only once, and not in any subquery／SQLite multiple references to recursive table: t	再帰部で自分を2回参照した、またはサブクエリの中で参照した（PostgreSQL は must not appear within a subquery）	自分の参照は1回。必要な値は列として持ち回る
PostgreSQL ERROR: recursive reference to query "t" must not appear within an outer join／MySQL ERROR 3576 (HY000): In recursive query block of Recursive Common Table Expression 't', the recursive table must neither be in the right argument of a LEFT JOIN, nor be forced to be non-first with join order hints	再帰部で自分を外部結合の右側に置いた	内部結合にする。欠けた行は本体で LEFT JOIN
MySQL ERROR 1235 (42000): This version of MySQL doesn't yet support 'ORDER BY / LIMIT / SELECT DISTINCT in recursive query block of Common Table Expression'	再帰部に ORDER BY か DISTINCT	本体で並べ替え・重複除去（LIMIT は 8.0.19 以降は書ける）
SQL Server（ドキュメント）The statement terminated. The maximum recursion 100 has been exhausted before statement completion.（エラー 530）	MAXRECURSION の既定 100 を超えた	終了条件を見直す。正しい再帰なら OPTION (MAXRECURSION n)
Oracle（ドキュメント）ORA-32044: cycle detected while executing recursive WITH query	データに閉路がある	終了条件を入れるか CYCLE 句を付ける

データ変更 CTE のエラー

エラー文（原文）	原因	直し方
PostgreSQL ERROR: WITH query "d" does not have a RETURNING clause	RETURNING の無い変更文を本体から参照した	RETURNING * を付ける
PostgreSQL ERROR: WITH clause containing a data-modifying statement must be at the top level	変更文を含む WITH をサブクエリに書いた	文の最上位に書く
MySQL ERROR 1064 (42000): ... near 'DELETE FROM orders WHERE status = 'canceled') SELECT * FROM moved' at line 1	MySQL は WITH の中に変更文を書けない	WITH c AS (SELECT ...) DELETE ... の形にする
MySQL ERROR 1093 (HY000): You can't specify target table 'orders' for update in FROM clause	変更対象の表をサブクエリで直接参照した	対象の行を CTE で先に選ぶ（上の例）

やってはいけない操作と、やり直す方法

操作	起きること	代わりに
終了条件の無い再帰を本番で実行	MySQL は 1001 回で ERROR 3636。PostgreSQL は止まらず、結果の保持にメモリとディスクを使い続ける	終了条件・CYCLE 句・訪問済みチェック。試すときは statement_timeout か MAX_EXECUTION_TIME を先に設定
SET GLOBAL cte_max_recursion_depth を巨大な値にする	全接続で暴走した再帰が止まらなくなる	SET_VAR ヒントで、その文だけ上げる
巨大な CTE を2回以上参照したまま放置	PostgreSQL は既定で全件を保持し、インデックスが効かない（201 ms の例）	NOT MATERIALIZED、条件を CTE の中へ、または一時テーブル＋インデックス
同じ WITH を SELECT で確かめずに UPDATE・DELETE	想定外の行が変わる	先に SELECT で件数と中身を確認。BEGIN の中で実行し、違えば ROLLBACK
変更 CTE の結果を本体で「更新後の値」として読む	本体は更新前のスナップショットを見る（3000 の例）	RETURNING の値を使う
RETURNING の無い変更 CTE を「参照していないから動かない」と思う	必ず1回実行される（id = 1 が消えた例）	使わない変更文は書かない
depth 列のある再帰で UNION に変えて閉路対策をしたつもりになる	行が毎回違うので止まらない	CYCLE 句か訪問済み列
PostgreSQL の LIMIT だけで止める書き方を本番で使う	ソートや結合があると全行を取りに行き止まらない。他の DB では動きが違う	終了条件を書く

走り続けている再帰を止める（実行して確かめた手順）

終了条件の無い再帰を実行してしまったら、別の接続から止めます。PostgreSQL では pg_stat_activity で見つけ、pg_cancel_backend で文だけを取り消します。

SELECT pid, state, left(query, 60) AS query, now() - query_start AS running
FROM pg_stat_activity
WHERE state = 'active' AND query LIKE 'WITH RECURSIVE%';
  pid  | state  |                            query                             |     running
-------+--------+--------------------------------------------------------------+-----------------
 10343 | active | WITH RECURSIVE walk AS (SELECT src, dst FROM routes WHERE sr | 00:00:02.059743
(1 row)

SELECT pg_cancel_backend(10343);
 pg_cancel_backend
-------------------
 t
(1 row)

止められた側には ERROR: canceling statement due to user request が返りました。pg_terminate_backend は接続ごと切る関数なので、文を止めるだけなら pg_cancel_backend を使います。MySQL は information_schema.processlist（または SHOW PROCESSLIST）で id を見て KILL QUERY id; を実行します。

SELECT id, command, time, LEFT(info, 50) AS info
FROM information_schema.processlist WHERE info LIKE 'WITH RECURSIVE%';
id  command time    info
70  Query   2   WITH RECURSIVE walk AS (SELECT src, dst FROM route

KILL QUERY 70;
-- 止められた側：ERROR 1317 (70100): Query execution was interrupted

KILL QUERY は文だけを止め、KILL id は接続ごと切ります。止めたあとは、終了条件か訪問済みチェックを入れてから実行し直します。

よくある質問

Q. sql with とは何ですか？ A. SELECT などの先頭に WITH 名前 AS (SELECT ...) と書いて、中間結果に名前を付ける構文です。共通テーブル式（CTE）とも呼び、本体からテーブルのように参照できます。有効なのはその1文の中だけです。

Q. sql with句 とサブクエリはどちらを使えばよいですか？ A. 同じ結果を2回以上使う、上から順に読ませたい、再帰が要る、のどれかなら WITH 句です。1〜2行で済む条件（EXISTS・IN）はサブクエリで足ります。性能は多くの場合同じ実行計画になり、差が出るのは2回以上参照するときです（「性能」の節）。

Q. sql cte とは何ですか？WITH 句と違いますか？ A. 同じものです。CTE（Common Table Expression、共通テーブル式）は WITH 句で定義した名前付きの結果の呼び名で、「WITH 句を使う」と「CTE を使う」は同じ意味で使われます。

Q. sql with 使い方 で、複数の CTE はどう書きますか？ A. WITH a AS (...), b AS (SELECT ... FROM a) SELECT ... FROM b のように、WITH は1回だけ書き、2つ目以降はカンマで続けます。後ろの CTE は前の CTE を参照できます。

Q. with cte で再帰（with 再帰）はどう書きますか？ A. WITH RECURSIVE t AS (起点の SELECT UNION ALL SELECT ... FROM 表 JOIN t ON ...) SELECT * FROM t の形です。アンカー（起点）と、直前に増えた行から次の行を作る再帰部を UNION ALL でつなぎ、再帰部が0行になった時点で終わります。終了条件（WHERE n < 100、子がいない、CYCLE 句）を必ず入れます。

Q. MySQL 5.7 で WITH 句は使えますか？ A. 使えません。WITH 句（再帰 CTE を含む）は MySQL 8.0 からの機能で、5.7 以前では構文エラー（ERROR 1064）になります。サブクエリ・一時テーブルで書き換えます。

Q. 再帰 CTE で階層の深さに上限はありますか？ A. MySQL は cte_max_recursion_depth（既定 1000）、SQL Server は MAXRECURSION（既定 100、0 で無制限）、BigQuery は 500 回が上限です。PostgreSQL・Oracle・SQLite には回数の上限が無いため、終了条件や CYCLE 句を必ず入れます。

Q. WITH 句で作った結果を別の SQL でも使えますか？ A. 使えません。WITH 句はその1文の中だけ有効で、次の文では relation "t" does not exist（PostgreSQL）になります。複数の文で使うなら、ビュー・一時テーブル・マテリアライズドビューを使います。

Q. WITH 句は遅いと聞きましたが本当ですか？ A. 1回しか参照しない CTE は PostgreSQL 12 以降・MySQL 8.0 とも本体に展開され、サブクエリと同じ実行計画になります。遅くなるのは、巨大な CTE を2回以上参照して既定で保持されるときで、NOT MATERIALIZED（PostgreSQL）や条件を CTE の中に入れる直し方で 201 ms が 0.07 ms になりました。重い集計を2回使うなら、逆に保持される既定のほうが速くなります。

まとめ

• WITH 句（CTE）は、文の先頭で WITH 名前 AS (SELECT ...) と中間結果に名前を付ける書き方。 有効なのはその1文だけで、WITH は1回、2つ目以降の CTE はカンマで続ける
• 再帰 CTE は「アンカー UNION ALL 再帰部」。 再帰部は直前に増えた行だけを見て、0行になったら終わる。組織ツリー・連番・カレンダー・経路・閉路の検出が同じ骨組みで書ける
• PostgreSQL はアンカーと再帰部の型を一致させ、MySQL はアンカー側で CAST して幅を確保する
• 無限ループは MySQL が 1001 回で ERROR 3636、PostgreSQL は止まらない。 終了条件・CYCLE 句（14 以降）・訪問済みチェックで止め、走ってしまったら pg_cancel_backend／KILL QUERY
• 性能は「何回参照するか」で決まる。 1回なら展開、2回以上なら保持が既定。巨大な CTE の複数回参照は NOT MATERIALIZED か条件の移動で直し、重い集計の複数回参照は既定の保持のまま
• UPDATE・DELETE の対象は CTE で絞れる。 MySQL は UPDATE ... JOIN cte、PostgreSQL は UPDATE ... FROM cte。PostgreSQL は WITH の中に DELETE ... RETURNING を書いて履歴へ移せるが、本体は更新前のスナップショットを見る
• DB で違うのは RECURSIVE の要否・上限・閉路の句・MATERIALIZED の有無。 SQL Server と Oracle は RECURSIVE を書かず、Oracle は列名リストが必須

参考・出典

確認日はいずれも 2026年10月16日です。

• PostgreSQL 16 Documentation「7.8. WITH Queries (Common Table Expressions)」：https://www.postgresql.org/docs/16/queries-with.html
• MySQL 8.0 Reference Manual「WITH (Common Table Expressions)」：https://dev.mysql.com/doc/refman/8.0/en/with.html
• MySQL 8.0 Reference Manual「Optimizing Derived Tables, View References, and Common Table Expressions with Merging or Materialization」：https://dev.mysql.com/doc/refman/8.0/en/derived-table-optimization.html
• SQLite「The WITH Clause」：https://www.sqlite.org/lang_with.html
• SQLite「Release History 3.8.3」：https://sqlite.org/releaselog/3_8_3.html
• Microsoft Learn「WITH common_table_expression (Transact-SQL)」：https://learn.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql
• Microsoft Learn「Database Engine events and errors (0 to 999)」：https://learn.microsoft.com/en-us/sql/relational-databases/errors-events/database-engine-events-and-errors-0-to-999
• Oracle Database 23ai SQL Language Reference「SELECT（subquery_factoring_clause）」：https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/SELECT.html
• Oracle Database 23ai SQL Language Reference「Hierarchical Queries」：https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/Hierarchical-Queries.html
• Oracle Database Error Messages「ORA-32044」：https://docs.oracle.com/en/error-help/db/ora-32044
• MariaDB Server Documentation「WITH」：https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/selecting-data/common-table-expressions/with
• MariaDB Server Documentation「Recursive Common Table Expressions Overview」：https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/selecting-data/common-table-expressions/recursive-common-table-expressions-overview
• MariaDB Jira「MDEV-31214」（max_recursive_iterations の既定値の経緯）：https://jira.mariadb.org/browse/MDEV-31214
• BigQuery Documentation「Work with recursive CTEs」：https://docs.cloud.google.com/bigquery/docs/recursive-ctes

株式会社RJC ― SI事業・SES事業・AI駆動開発。RJCは一緒に成長を楽しめる会社です。