SQL カーディナリティ — EXISTS・LATERAL・再帰CTEの応用

QUESTION 1

EXISTS による準結合（セミ結合） — JOINで行を増やさず「実績あり」を判定

EXISTSセミ結合相関サブクエリ行数保持

前提知識 ▼

「注文したことがある顧客」を取得する際、customers と orders を INNER JOIN すると、1:N の関係により顧客が注文件数の分だけ複製されます。重複を消すために DISTINCT が必要になり、無駄も多くバグの温床になります。

EXISTS（準結合 / セミ結合）は「右テーブルに1件でも該当行があるか」だけを判定し、左テーブルの行を一切増やさず・右の列も持ち込まずに絞り込みます。これがカーディナリティを保つ正しい存在判定です。

-- INNER JOIN: 注文数だけ顧客行が複製される → DISTINCT が必要
FROM customers c JOIN orders o ON c.customer_id = o.customer_id  -- 鈴木が3行に膨張

-- EXISTS（セミ結合）: 一致が1件でもあれば1行のまま、列も増えない
FROM customers c WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id)

SELECT 1 の意味：EXISTS の中身は「行が存在するか」だけを問うので、選ぶ列は何でもよく 1 が慣例です。最初の1件が見つかった時点で評価を打ち切る（短絡評価）ため、結合よりも軽いことが多いです。

問題 ▼

customers と orders を使って、注文実績のある顧客（customer_id, name）を取得してください。EXISTS を使い、行を重複させずに解いてください。

使用テーブル

▸ customers

customer_id	name
1	田中
2	佐藤
3	山田
4	鈴木

▸ orders

order_id	customer_id	amount
101	1	5000
102	1	3000
103	2	8000
104	4	2000
105	4	4000
106	4	1000

期待出力 ▼

customer_id	name
1	田中
2	佐藤
4	鈴木

模範解答コード ▼

-- [膨張する書き方] INNER JOIN は鈴木を3行に複製するため DISTINCT 必須
-- SELECT DISTINCT c.customer_id, c.name FROM customers c JOIN orders o ON ...

SELECT
  c.customer_id,
  c.name
FROM customers AS c
WHERE EXISTS (
  SELECT 1
  FROM orders AS o
  WHERE o.customer_id = c.customer_id  -- 相関: 外側の顧客に紐づく注文だけ探す
)
ORDER BY c.customer_id;

/*
  実行順序:
  1. FROM customers AS c           → customers の4行を1行ずつ評価対象にする
  2. WHERE EXISTS (...)            → 各顧客ごとに相関サブクエリを実行
  3. 山田(id=3)は一致する注文が0件            → FALSE で除外
  4. SELECT c.customer_id, c.name  → 各顧客は最大1行のまま射影
  5. ORDER BY c.customer_id        → 昇順で出力
  */

解説（テーブル変化・ポイント） ▼

SELECT
  c.customer_id,
  c.name
FROM customers AS c
WHERE EXISTS (
  SELECT 1
  FROM orders AS o
  WHERE o.customer_id = c.customer_id
)
ORDER BY c.customer_id;

LEGEND ▼

データ取得・読込対象

① 左テーブル — customers ▼

FROM customers AS c判定対象となる customers（4行）です。この各行について「注文があるか」を1行ずつ調べていきます。EXISTS はこの4行を増やしも減らしもせず、フィルタするだけです。

1 / 6

customer_id	name
1	田中
2	佐藤
3	山田
4	鈴木

customers 4行（この行数は EXISTS では膨張しない）

学習ポイント

SEMI JOIN

EXISTS = 準結合 — 右に一致があるかで左を絞るだけ、行は増えない

存在判定はカーディナリティを保ったまま行う
customers(N行) WHERE EXISTS(orders) → 左の行数のまま絞り込み

セミ結合がカーディナリティを保つ理由：通常の JOIN は右テーブルの一致行をすべて掛け合わせる（直積）ため左行が複製されます。EXISTS は「1件でもあるか」という真偽だけを返すので、何件一致しようと左の1行は1行のまま。右の列を結果に持ち込まないからこそ膨張しません。これが「準（セミ）結合」と呼ばれる所以です。

EXISTS と IN の使い分け：「実績ありの顧客」は c.customer_id IN (SELECT customer_id FROM orders) でも書けます。多くのDBで両者は同等に最適化されますが、サブクエリ側が NULL を含みうる場合や、複数列で相関させたい場合は EXISTS が安全で表現力も高いです（NULLの罠は次問で扱います）。

アンチパターン

存在判定なのに JOIN + DISTINCT で済ませる：SELECT DISTINCT c.* FROM customers c JOIN orders o ... は結果は合いますが、いったん膨張させてから重複排除するため無駄が多く、SELECT に orders の列を足した瞬間に DISTINCT の効きが崩れて重複が再発します。目的が「存在するか」なら最初から EXISTS で書きましょう。

EXISTS の中で COUNT(*) を数える：WHERE (SELECT COUNT(*) FROM orders o WHERE ...) > 0 は動きますが、全件を数え切るため短絡評価が効かず非効率です。「あるか/ないか」だけなら EXISTS が最適。件数が必要なときだけ COUNT を使うのが原則です。

実務コラム

セミ結合は実務の絞り込みで最頻出です。例: レビュー投稿済みの商品だけ表示、過去90日にログインのあるアクティブユーザー抽出、在庫のある倉庫のみ一覧。いずれも「子テーブルに該当があるか」で親を絞る場面で、JOINで膨張させずに EXISTS を使うと集計の二重計上を未然に防げます。WHERE EXISTS を見たら「行は増えない絞り込み」と即読み替えられるようにしておきましょう。

QUESTION 2

NOT EXISTS による反結合と NOT IN の NULL 罠 — 一度も注文していない顧客

NOT EXISTSアンチ結合NOT INNULL三値論理

前提知識 ▼

この問題の逆で「一度も注文していない顧客」を探すのが反結合（アンチ結合）です。書き方は主に3つあります。

① NOT EXISTS（相関サブクエリ・NULL安全）、② LEFT JOIN ... WHERE 右キー IS NULL（マッチしなかった行＝NULL行を残す）、③ NOT IN (サブクエリ)。このうち ③ は対象列に NULL が1件でも混ざると結果が常に0件になるという有名な罠を抱えています。

-- ✗ NOT IN: サブクエリに NULL が1件でもあると結果が全滅
WHERE c.customer_id NOT IN (SELECT customer_id FROM orders)   -- orders に NULL → 0件

-- ✓ NOT EXISTS: NULL があっても相関で個別判定するため安全
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id)

なぜ NOT IN は NULL で全滅するか：2 NOT IN (1, 4, NULL) は 2<>1 AND 2<>4 AND 2<>NULL。最後の 2<>NULL は UNKNOWN になり、AND に UNKNOWN が混ざると全体が UNKNOWN。SQL は TRUE の行しか返さないため、どの顧客も残りません。

問題 ▼

customers と orders を使って、一度も注文していない顧客（customer_id, name）を取得してください。NOT EXISTS を使った NULL 安全なアプローチで解いてください。

使用テーブル

▸ customers

customer_id	name
1	田中
2	佐藤
3	山田
4	鈴木

▸ orders

order_id	customer_id	product
101	1	商品A
102	1	商品B
103	4	商品C
104	NULL	商品D（ゲスト購入）

期待出力 ▼

customer_id	name
2	佐藤
3	山田

模範解答コード ▼

-- ✗ NOT IN: orders.customer_id に NULL があるため常に0件（罠）
-- WHERE c.customer_id NOT IN (SELECT customer_id FROM orders)

SELECT
  c.customer_id,
  c.name
FROM customers AS c
WHERE NOT EXISTS (
  SELECT 1
  FROM orders AS o
  WHERE o.customer_id = c.customer_id  -- 相関: この顧客の注文が「無い」ことを確認
)
ORDER BY c.customer_id;

/*
  実行順序:
  1. FROM customers AS c           → 顧客を1行ずつ評価
  2. WHERE NOT EXISTS (...)        → 注文ゼロの顧客のみ通過
  3. SELECT c.customer_id, c.name  → 2列を射影
  4. ORDER BY c.customer_id        → 並び替えて出力
  */

解説（テーブル変化・ポイント） ▼

SELECT
  c.customer_id,
  c.name
FROM customers AS c
WHERE NOT EXISTS (
  SELECT 1
  FROM orders AS o
  WHERE o.customer_id = c.customer_id
)
ORDER BY c.customer_id;

LEGEND ▼

データ取得・読込対象

① 左テーブル — customers ▼

FROM customers AS c判定対象の customers（4行）です。このうち「注文が1件も無い」顧客を残します。アンチ結合も EXISTS 同様に左の行数を増やしません。

1 / 5

customer_id	name
1	田中
2	佐藤
3	山田
4	鈴木

customers 4行

学習ポイント

ANTI JOIN & NULL

NOT EXISTS = 反結合 — 「右に一致が無い」左行だけを残す

NOT IN は NULL 1件で全滅、NOT EXISTS は NULL 安全
2 <> NULL = UNKNOWN → AND全体が UNKNOWN → 0件

反結合の3つの書き方と等価性：① NOT EXISTS、② LEFT JOIN orders o ON ... WHERE o.order_id IS NULL、③ NOT IN はいずれも「一致しない左行」を狙います。① と ② は NULL 安全で結果も一致しますが、③ だけがサブクエリ側の NULL で破綻します。迷ったら NOT EXISTS が最も安全で意図も明確です。

三値論理（TRUE / FALSE / UNKNOWN）：SQL の比較は NULL が絡むと「不明（UNKNOWN）」を返します。x = NULL も x <> NULL も UNKNOWN です。WHERE は TRUE の行だけを通すため、UNKNOWN は実質 FALSE 扱い。NOT IN の AND 連鎖はこの UNKNOWN 伝播で全滅します。NULL の有無を常に意識することがアンチ結合の肝です。

アンチパターン

NULL を許す列に対して NOT IN を使う：最大の罠です。サブクエリ列に NULL が1件でもあると結果が0件になり、しかもエラーは出ないため気づきにくい。どうしても NOT IN を使うなら WHERE customer_id IS NOT NULL をサブクエリに足すか、素直に NOT EXISTS に置き換えましょう。

LEFT JOIN の IS NULL 判定で「値が NULL の列」を選ぶ：LEFT JOIN ... WHERE o.product IS NULL のように結合キー以外の NULL になりうる列で判定すると、元から NULL の正規データまで拾ってしまいます。アンチ結合では必ず結合キー（または主キー）の IS NULLで「マッチしなかった行」を判定してください。

実務コラム

反結合は「離脱した会員（直近の注文が無い）」「未回答者（アンケート未提出）」「孤立データ（親が存在しない子レコード）」の検出など、データ品質チェックやリテンション分析で多用されます。本番のサブクエリは外部システム連携で NULL を含みがちなので、反結合は原則 NOT EXISTSと覚えておくと事故を防げます。

QUESTION 3

条件付き集計（FILTER / CASE） — 複数回JOINせず1行で多指標を集計

条件付き集計FILTER / CASEピボットN:1集約

前提知識 ▼

「顧客ごとに、完了・キャンセル・保留の注文件数と、完了金額を1行にまとめたい」。ステータスごとに orders を3回 JOIN したり、サブクエリを3本書くと複雑で遅く、結合の膨張リスクも生じます。

条件付き集計を使えば、1回の GROUP BY で N 行を 1 行に畳み込みながら、条件ごとに別々の列へ振り分け（ピボット）できます。PostgreSQL の 集計関数 FILTER (WHERE 条件) が最も読みやすく、移植性重視なら SUM(CASE WHEN ...) が等価です。

-- FILTER: 集計関数に「どの行を対象にするか」を後置で指定（標準SQL）
COUNT(*) FILTER (WHERE status = 'completed') AS completed_count
SUM(amount) FILTER (WHERE status = 'completed') AS completed_amount

-- 移植性重視なら CASE 版（等価）
SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END)

COUNT と SUM の0件時の違い：COUNT(*) FILTER(...) は対象0行でも 0 を返しますが、SUM(...) FILTER(...) は対象0行だと NULL を返します。金額側は COALESCE(..., 0) でゼロ埋めしておくのが安全です。

問題 ▼

customers と orders を使い、顧客ごとに完了件数（completed_count）・キャンセル件数（cancelled_count）・保留件数（pending_count）・完了金額合計（completed_amount）を1行ずつで取得してください。条件付き集計（FILTER）を使って解いてください。

使用テーブル

▸ customers

customer_id	name
1	田中
2	佐藤
3	山田

▸ orders

order_id	customer_id	status	amount
101	1	completed	5000
102	1	completed	3000
103	1	cancelled	2000
104	2	completed	8000
105	2	pending	1000
106	3	cancelled	4000
107	3	pending	2000
108	3	pending	1500

期待出力 ▼

name	completed_count	cancelled_count	pending_count	completed_amount
田中	2	1	0	8000
佐藤	1	0	1	8000
山田	0	1	2	0

模範解答コード ▼

SELECT
  c.name,
  COUNT(*) FILTER (WHERE o.status = 'completed') AS completed_count,  -- 状態別に条件カウント
  COUNT(*) FILTER (WHERE o.status = 'cancelled') AS cancelled_count,
  COUNT(*) FILTER (WHERE o.status = 'pending')   AS pending_count,
  COALESCE(SUM(o.amount) FILTER (WHERE o.status = 'completed'), 0) AS completed_amount  -- 完了分の金額合計（無ければ0）
FROM customers AS c
INNER JOIN orders AS o  -- 1:N で展開
  ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name
ORDER BY c.customer_id;

/*
  実行順序:
  1. FROM customers AS c     → 顧客を読み込む
  2. INNER JOIN orders AS o  → 結合（1:N で展開）
  3. GROUP BY c.customer_id  → グループ化
  4. COUNT/SUM FILTER (...)  → 状態別に条件集計（ピボット）
  5. SELECT                  → 列を射影
  6. ORDER BY c.customer_id  → 並び替えて出力
  */

解説（テーブル変化・ポイント） ▼

SELECT
  c.name,
  COUNT(*) FILTER (WHERE o.status = 'completed') AS completed_count,
  COUNT(*) FILTER (WHERE o.status = 'cancelled') AS cancelled_count,
  COUNT(*) FILTER (WHERE o.status = 'pending') AS pending_count,
  COALESCE(SUM(o.amount) FILTER (WHERE o.status = 'completed'), 0) AS completed_amount
FROM customers AS c
INNER JOIN orders AS o
  ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name
ORDER BY c.customer_id;

LEGEND ▼

データ取得・読込対象

① 左テーブル — customers ▼

FROM customers AS c集計の軸となる customers（3行）です。最終的に1顧客=1行へ畳み込みます。

1 / 6

customer_id	name
1	田中
2	佐藤
3	山田

customers 3行

学習ポイント

CONDITIONAL AGGREGATION

条件付き集計 — 縦に並んだ status を横の列へ畳み込む（N→1）

複数JOINを避け、1回の走査で多指標を同時集計
COUNT(*) FILTER (WHERE status = ...) ≡ SUM(CASE WHEN ...)

FILTER と CASE は等価 — どちらもN→1の畳み込み：COUNT(*) FILTER (WHERE status='completed') は SUM(CASE WHEN status='completed' THEN 1 ELSE 0 END) と同じ結果です。FILTER は標準SQL（PostgreSQL等）で可読性が高く、CASE はほぼ全DBで動く移植性が利点。どちらも「グループ内の特定行だけを対象にした集計」を1つの GROUP BY 内で実現します。

WHERE と FILTER の違い：外側の WHERE status='completed' はクエリ全体から completed 以外の行を消してしまい、他ステータスの件数が取れません。FILTER は集計関数ごとに対象行を個別指定するため、1回のグループ化で完了・キャンセル・保留を同時に数えられます。「行は残しつつ、集計対象だけ絞る」のが FILTER です。

アンチパターン

ステータスごとにテーブルを複数回JOINする：完了用・キャンセル用…と orders を別名で繰り返し JOIN すると、グループ内で直積が起き件数・金額が二重計上されます。さらに該当0件のステータスがあると INNER JOIN では顧客が丸ごと消えます。同一テーブルの多指標は1回のJOIN＋条件付き集計が定石です。

SUM 系の0件 NULL を放置する：SUM(amount) FILTER(...) は対象0行で NULL を返すため、そのまま計算に使うと NULL 伝播で結果が NULL になります。金額・比率を出すときは COALESCE(..., 0) でゼロ埋めしましょう（COUNT は0を返すので不要）。

実務コラム

条件付き集計はダッシュボードの定番です。ステータス別の注文内訳、性別・年代別の人数を1行に展開、月別売上を横持ちにしたピボット表など、「縦持ちデータを横持ちの集計表に変換する」場面で活躍します。BIツールに渡す前の整形クエリとして、複数JOINやUNIONより圧倒的に高速・簡潔です。本格的な動的ピボットが必要なら PostgreSQL の crosstab 拡張も選択肢になります。

QUESTION 4

LATERAL JOIN で各グループの Top-N — 相関結合による制御された1:N展開

LATERAL相関結合Top-N per group1:N制御

前提知識 ▼

「カテゴリごとに価格の高い商品 Top2 を取りたい」。通常の JOIN ではFROM句のサブクエリから外側テーブルの列を参照できないため、グループごとの絞り込みが書けません。

LATERAL を付けると、サブクエリが「左の各行の列」を参照できる相関結合になります。これにより「左の1行（カテゴリ）ごとにサブクエリを実行し、その中で ORDER BY ... LIMIT N」という Top-N per group が簡潔に書けます。ROW_NUMBER で各グループの1件（Top-1）を選ぶ形とは異なり、これは任意の N 件に制御された 1:N展開です。

-- LATERAL: サブクエリが左テーブル c の列を参照できる相関結合
FROM categories c
CROSS JOIN LATERAL (
  SELECT ... FROM products p
  WHERE p.category_id = c.category_id   -- ← 左の c を参照（通常サブクエリでは不可）
  ORDER BY p.price DESC LIMIT 2         -- ← 各カテゴリ上位2件に制限
) t

CROSS と LEFT の使い分け：CROSS JOIN LATERAL はサブクエリが0行を返した左行（＝商品ゼロのカテゴリ）を結果から落とします。商品ゼロのカテゴリも残したいなら LEFT JOIN LATERAL (...) ON true を使います。

問題 ▼

categories と products を使い、カテゴリごとに価格の高い商品 Top2（category_name, product_name, price, 順位 rn）を取得してください。LATERAL JOIN を使った相関結合で解いてください。

使用テーブル

▸ categories

category_id	category_name
1	ドリンク
2	フード

▸ products

product_id	category_id	product_name	price
1	1	コーヒー	500
2	1	紅茶	450
3	1	ジュース	400
4	1	水	200
5	2	ケーキ	600
6	2	サンド	500

期待出力 ▼

category_name	product_name	price	rn
ドリンク	コーヒー	500	1
ドリンク	紅茶	450	2
フード	ケーキ	600	1
フード	サンド	500	2

模範解答コード ▼

SELECT
  c.category_name,
  t.product_name,
  t.price,
  t.rn
FROM categories AS c
CROSS JOIN LATERAL (
  SELECT
    p.product_name,
    p.price,
    ROW_NUMBER() OVER (ORDER BY p.price DESC) AS rn
  FROM products AS p
  WHERE p.category_id = c.category_id   -- 相関: 外側カテゴリの商品だけに限定
  ORDER BY p.price DESC
  LIMIT 2                          -- 各カテゴリ上位2件に制限（1:N を 1:2 に）
) AS t
ORDER BY c.category_id, t.rn;

/*
  実行順序:
  1. FROM categories AS c          → categories の2行を1行ずつ取り出す
  2. CROSS JOIN LATERAL (...)      → 各カテゴリ c ごとにサブクエリを実行
  3. categories(2行) と 各2件 を結合      → 計4行（制御された1:N）
  4. SELECT で4列を射影
  5. ORDER BY c.category_id, t.rn  → カテゴリ・順位の昇順で出力
  */

解説（テーブル変化・ポイント） ▼

SELECT
  c.category_name,
  t.product_name,
  t.price,
  t.rn
FROM categories AS c
CROSS JOIN LATERAL (
  SELECT
    p.product_name,
    p.price,
    ROW_NUMBER() OVER (ORDER BY p.price DESC) AS rn
  FROM products AS p
  WHERE p.category_id = c.category_id
  ORDER BY p.price DESC
  LIMIT 2
) AS t
ORDER BY c.category_id, t.rn;

LEGEND ▼

データ取得・読込対象

① 左テーブル — categories ▼

FROM categories AS cTop-N を取りたい軸となる categories（2行）です。LATERAL はこの各行ごとにサブクエリを実行します。

1 / 6

category_id	category_name
1	ドリンク
2	フード

categories 2行

学習ポイント

LATERAL / TOP-N PER GROUP

相関結合 — 左の各行ごとにサブクエリを実行し Top-N を取る

1:N を「各グループ N 件」に制御して展開する
categories ⋈ LATERAL(products ... LIMIT 2) → 1:2 に制御

LATERAL がなぜ必要か：通常の FROM句サブクエリは独立に評価され、外側テーブルの列を参照できません。そのため「左の行に応じて中身が変わるサブクエリ」（＝グループごとの Top-N）が書けません。LATERAL は左の各行に対して順にサブクエリを実行するため、WHERE p.category_id = c.category_id のような相関と LIMIT N が同居できます。

ROW_NUMBER 方式との比較：ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY price DESC) を計算し WHERE rn <= 2 で絞っても同じ結果になります。違いは、ROW_NUMBER 方式は全商品に番号を振ってから絞るのに対し、LATERAL はカテゴリごとに2件取ったら打ち切れる点。対象グループが多くインデックスがある場合、LATERAL が効率的になりやすいです。

アンチパターン

相関条件を書き忘れて全商品が出る：サブクエリ内の WHERE p.category_id = c.category_id を忘れると、各カテゴリに「全カテゴリの Top2」が付き、意味のない結果になります。LATERAL の本質は相関条件なので、左の列を参照する WHERE を必ず入れてください。

商品ゼロのカテゴリが消えることに気づかない：CROSS JOIN LATERAL はサブクエリが0行のカテゴリを結果から落とします。「商品が無いカテゴリも一覧に出したい」要件では LEFT JOIN LATERAL (...) ON true を使い、商品側が NULL になる行を保持します（LEFT JOIN と同じ発想）。

実務コラム

Top-N per group は実務頻出です。各ユーザーの直近3注文、店舗ごとの売上Top5商品、記事ごとの新着コメント3件など。MySQL 8.0+ や PostgreSQL は LATERAL に対応し、SQL Server では CROSS APPLY、一部DBでは ROW_NUMBER 方式で代替します。「グループごとに上位 N 件」という要件を見たら、LATERAL か ROW_NUMBER <= N を反射的に思い浮かべられるようにしておきましょう。

QUESTION 5

再帰CTE で階層を展開 — 自己参照 1:N（組織図）を1段ずつたどる

再帰CTEWITH RECURSIVE自己参照1:N階層展開

前提知識 ▼

組織図のように「社員が上司（manager_id）を指す」テーブルは、同じテーブルが自分自身を参照する自己参照 1:Nです。何階層あるか不定のため、固定回数の JOIN では全階層をたどれません。

再帰CTE（WITH RECURSIVE）は、① アンカー（起点）を作り、② 再帰項でその結果に結合して次の階層を生成し、③ 新しい行が出なくなるまで繰り返します。各ステップでは「今回追加された行だけ」が次の入力（駆動表）になる点が核心です。

WITH RECURSIVE org AS (
  <アンカー>                 -- 起点（例: 最上位の社長 level=1）
  UNION ALL
  <再帰項 ... JOIN org>      -- 直前の結果(org)に結合し次の階層を生成
)                              -- 再帰項が0行を返したら停止

「今回追加分」が次を駆動する：再帰項が参照する org は「全累積ではなく、直前のステップで新たに生成された行」です。社長 → その部下 → さらにその部下…と、追加分だけを起点に1段ずつ深く潜っていきます。

問題 ▼

employees（manager_id で上司を指す自己参照テーブル）から、各社員の階層レベル（level: 最上位=1）と経路（path: 社長からの肩書きを > で連結）を取得してください。再帰CTEで解いてください。

使用テーブル

▸ employees

emp_id	name	manager_id
1	社長	NULL
2	部長A	1
3	部長B	1
4	課長A	2
5	担当A	4

期待出力 ▼

emp_id	name	level	path
1	社長	1	社長
2	部長A	2	社長 > 部長A
3	部長B	2	社長 > 部長B
4	課長A	3	社長 > 部長A > 課長A
5	担当A	4	社長 > 部長A > 課長A > 担当A

模範解答コード ▼

WITH RECURSIVE org AS (
  -- アンカー: 上司を持たない最上位（社長）から開始
  SELECT
    emp_id, name, manager_id,
    1 AS level,
    name AS path
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  -- 再帰項: 直前の結果(org)の部下を1段ずつ追加
  SELECT
    e.emp_id, e.name, e.manager_id,
    o.level + 1,
    o.path || ' > ' || e.name
  FROM employees AS e
  INNER JOIN org AS o ON e.manager_id = o.emp_id
)
SELECT emp_id, name, level, path
FROM org
ORDER BY level, emp_id;

/*
  実行順序（再帰の流れ）:
  1. アンカー実行               → 起点行を生成（社長）
  2. 再帰項 1回目              → 子を展開
  3. 再帰項 2回目              → 子を展開
  4. 再帰項 3回目              → 子を展開
  5. 再帰項 4回目              → 追加行なしで終了
  6. SELECT ... ORDER BY  → 並び替えて出力
  */

解説（テーブル変化・ポイント） ▼

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

LEGEND ▼

データ取得・読込対象

① 元データ — employees（自己参照） ▼

FROM employeesmanager_id が同じテーブルの emp_id を指す自己参照 1:N 構造です。社長は上司を持たないため manager_id = NULL。ここから階層を1段ずつ展開していきます。

1 / 7

emp_id	name	manager_id
1	社長	NULL
2	部長A	1
3	部長B	1
4	課長A	2
5	担当A	4

employees 5行（manager_id → emp_id の自己参照）

学習ポイント

RECURSIVE CTE

WITH RECURSIVE — アンカーから1段ずつ階層を展開する

「今回追加された行」が次の駆動表になり深く潜る
アンカー → 再帰項(JOIN org) → 0行で停止

再帰CTEの3要素：① アンカー（起点を1回だけ評価）、② UNION ALL、③ 再帰項（CTE自身 org を参照して次を生成）。再帰項は「直前に追加された行」だけを入力に取り、それを employees に結合して次の階層を作ります。新しい行が出なくなった（0行を返した）瞬間に自動的に停止します。

「累積結果」と「今回追加分」の区別：最終的な org は全階層の累積（5行）ですが、各再帰ステップを駆動するのはその回に新しく生成された行だけです。社長(1行)→部長(2行)→課長(1行)→担当(1行)→0行、と追加分が次々にバトンを渡し、累積へ積み上がっていくイメージを持つと再帰CTEの挙動が読めるようになります。

アンチパターン

UNION ALL を UNION にする：UNION は毎ステップで重複排除（ソート）を行うため無駄に重くなります。階層展開では重複は通常出ないので UNION ALL が原則です。ただし循環（A→B→A のような参照ループ）があり得るデータでは、訪問済みを記録する列を持たせて無限ループを防ぐ必要があります。

終了条件を作り込まず無限ループ：再帰項が必ず行を減らしていく（いずれ0行になる）構造でないと無限に回り続けます。多くのDBには再帰の最大回数制限がありますが、設計段階で「親をたどり切れば必ず止まる」ことを確認しましょう。データに循環があるなら path に自分が含まれていないかチェックする等の防御が必要です。

実務コラム

再帰CTEは階層・グラフ構造の万能ツールです。組織図の上下関係、部品表（BOM: 製品→部品→素材）、カテゴリツリーの全子孫取得、コメントのスレッド展開、連番・日付シーケンスの生成など応用は多彩。WHERE manager_id = 特定ID をアンカーにすれば「ある社員の配下全員」だけを取り出すこともできます。自己参照テーブルを見たら再帰CTEを思い出せるようにしておきましょう。