CTE / サブクエリ

WITH 句（CTE）でクエリを段階的に組み立てる、再帰 CTE でツリーを辿る、サブクエリの種類と使い分け。

サブクエリの分類

スカラーサブクエリ: SELECT 句や WHERE で 1 値返す
インラインビュー: FROM 句で表として使う
相関サブクエリ: 外側の列を参照
EXISTS / IN サブクエリ: 存在判定

スカラーサブクエリ

SELECT
  id, title,
  (SELECT count(*) FROM comments WHERE comments.post_id = posts.id) AS comment_count
FROM posts;

各行ごとにサブクエリ実行。便利だが大量の行で遅くなりがち。Window 関数 / JOIN GROUP BY の方が速いことが多い。

インラインビュー（FROM 句）

SELECT category, avg_views FROM (
  SELECT category, AVG(views) AS avg_views
  FROM posts
  GROUP BY category
) t
WHERE avg_views > 100;

相関サブクエリ

-- 各ユーザの「最新投稿のタイトル」
SELECT
  u.id, u.email,
  (SELECT title FROM posts
   WHERE user_id = u.id
   ORDER BY created_at DESC LIMIT 1) AS latest_title
FROM users u;

EXISTS

SELECT * FROM users u
WHERE EXISTS (
  SELECT 1 FROM posts WHERE user_id = u.id AND status = 'published'
);

IN より1 件見つけた時点で停止するので速いことが多い。

NOT EXISTS で「持っていない」

SELECT * FROM users u
WHERE NOT EXISTS (SELECT 1 FROM posts WHERE user_id = u.id);

CTE（Common Table Expressions）

WITH で名前付きの中間結果を定義してから本体クエリで使う。長いクエリを段階的に書ける。

WITH active_users AS (
  SELECT id, email FROM users WHERE is_active
),
their_posts AS (
  SELECT u.email, p.id, p.title
  FROM active_users u
  JOIN posts p ON p.user_id = u.id
)
SELECT * FROM their_posts WHERE title ILIKE '%react%';

CTE の利点

長いクエリを段階分解できる
同じ中間結果を複数箇所で使える
再帰クエリが書ける
SELECT 以外も書ける（INSERT / UPDATE / DELETE 連鎖）

CTE の注意

古い Postgres（12 以前）は CTE が常に「マテリアライズ」される
13+ はインライン化される（速くなる）
意図的にマテリアライズしたいなら WITH ... AS MATERIALIZED (...)

CTE での DML 連鎖

WITH inserted AS (
  INSERT INTO logs (user_id, event)
  VALUES (1, 'login')
  RETURNING id, user_id
)
UPDATE users SET last_login_log_id = inserted.id
FROM inserted
WHERE users.id = inserted.user_id;

再帰 CTE

ツリー構造（コメント、組織、カテゴリ）を辿る:

WITH RECURSIVE comment_tree AS (
  -- 起点（base case）: 親なしのトップレベル
  SELECT id, body, parent_id, 0 AS depth
  FROM comments
  WHERE post_id = 42 AND parent_id IS NULL

  UNION ALL

  -- 再帰: 既に集めたコメントの子
  SELECT c.id, c.body, c.parent_id, ct.depth + 1
  FROM comments c
  JOIN comment_tree ct ON c.parent_id = ct.id
)
SELECT * FROM comment_tree
ORDER BY depth, id;

無限ループ防止

循環参照のあるグラフでは停止しない可能性
深さ上限を WHERE depth < 10 等で付ける
Postgres 14+ には CYCLE 構文で循環検出が組込み

CYCLE 構文

WITH RECURSIVE g(id, parent_id, path) AS (
  SELECT id, parent_id, ARRAY[id] FROM categories WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.parent_id, path || c.id
  FROM categories c JOIN g ON c.parent_id = g.id
)
CYCLE id SET is_cycle USING cycle_path
SELECT * FROM g;

パスや階層番号を持たせる

WITH RECURSIVE org AS (
  SELECT id, name, parent_id, ARRAY[name] AS path FROM departments WHERE parent_id IS NULL
  UNION ALL
  SELECT d.id, d.name, d.parent_id, org.path || d.name
  FROM departments d JOIN org ON d.parent_id = org.id
)
SELECT id, array_to_string(path, ' > ') AS hierarchy FROM org;

連番生成 / 範囲

-- generate_series（Postgres）
SELECT generate_series(1, 10);
SELECT generate_series('2026-01-01'::date, '2026-12-31'::date, '1 day'::interval);

-- 再帰 CTE で（標準 SQL）
WITH RECURSIVE nums(n) AS (
  SELECT 1 UNION ALL SELECT n + 1 FROM nums WHERE n < 10
)
SELECT * FROM nums;

欠落日付の補完

-- 集計データに 0 件の日も含める
WITH date_range AS (
  SELECT generate_series(
    date_trunc('day', NOW() - INTERVAL '30 days'),
    date_trunc('day', NOW()),
    '1 day'
  )::date AS day
)
SELECT
  d.day,
  COALESCE(count(p.id), 0) AS posts
FROM date_range d
LEFT JOIN posts p ON date_trunc('day', p.created_at) = d.day
GROUP BY d.day
ORDER BY d.day;

UPSERT 風（INSERT ... ON CONFLICT）と CTE

WITH new_data AS (
  SELECT * FROM staging_users
)
INSERT INTO users (id, email, name)
SELECT id, email, name FROM new_data
ON CONFLICT (id) DO UPDATE
  SET email = EXCLUDED.email,
      name = EXCLUDED.name;

ビュー vs CTE

ビュー: 永続化される名前付きクエリ
CTE: 1 つの SQL の中だけ
共有する → ビュー、その場限り → CTE
マテビューは結果をテーブル化（更新は手動）

サブクエリの代替

JOIN への書き換え

-- IN サブクエリ
SELECT * FROM users WHERE id IN (SELECT user_id FROM posts);

-- JOIN（DISTINCT が必要）
SELECT DISTINCT u.* FROM users u JOIN posts p ON p.user_id = u.id;

-- EXISTS（推奨）
SELECT * FROM users u WHERE EXISTS (SELECT 1 FROM posts WHERE user_id = u.id);

典型的な書き分け

用途	選択
「持っている」判定	EXISTS
「持っていない」判定	NOT EXISTS
関連取得	JOIN
段階分解	CTE
ツリー / グラフ	再帰 CTE
列ごとの個別集計	スカラーサブクエリ or LATERAL

失敗パターン

症状	対処
サブクエリで遅い	EXISTS / JOIN に書き換え
CTE が遅い (古い PG)	サブクエリ化、12 以前は注意
再帰 CTE が止まらない	循環あり。WHERE depth で抑制
結果が NULL に化ける	NOT IN + NULL の罠 → NOT EXISTS へ
長すぎる読めない SQL	CTE で段階分け

NOT IN + NULL の罠

-- 危険
SELECT * FROM a WHERE id NOT IN (SELECT id FROM b);
-- b.id に NULL が 1 つでもあると、結果が空になる！

-- 安全
SELECT * FROM a WHERE NOT EXISTS (SELECT 1 FROM b WHERE b.id = a.id);

長いクエリを書く流儀

WITH
  active_users AS (
    SELECT id, email FROM users WHERE is_active
  ),
  recent_posts AS (
    SELECT * FROM posts WHERE created_at >= NOW() - INTERVAL '7 days'
  ),
  user_stats AS (
    SELECT u.id, u.email, count(p.id) AS post_count
    FROM active_users u
    LEFT JOIN recent_posts p ON p.user_id = u.id
    GROUP BY u.id, u.email
  )
SELECT * FROM user_stats WHERE post_count > 0 ORDER BY post_count DESC;

各 CTE は1 つの責務。プログラミングと同じ感覚で関数化する。

習慣化したい

複雑な SQL は CTE で段階的に。再帰 CTE が書けると、ツリー / グラフ系の問題が一気に解ける。 1 度書けるようになると、もう手放せない技術。