共通テーブル式に名前を付ける - CTE (WITH 句)

WITH 句で名前付きの一時結果を定義。可読性向上、再帰 CTE で階層構造、MATERIALIZED ヒントにも触れる

概念図

構文

sql

WITH name AS (SELECT ...) [, name2 AS (SELECT ...)] SELECT ... FROM name

サンプル

複数の CTE を連ねて「最近の注文 → ユーザー集計 → ユーザー結合」と段階的に構築

sql

WITH recent_orders AS (
  SELECT *
  FROM orders
  WHERE created_at >= DATE '2026-01-01'
),
user_totals AS (
  SELECT user_id, SUM(amount) AS total
  FROM recent_orders
  GROUP BY user_id
)
SELECT u.name, ut.total
FROM users u
JOIN user_totals ut ON ut.user_id = u.id
ORDER BY ut.total DESC;

CTE とは

CTE（Common Table Expression、共通テーブル式）は WITH 名前 AS (SELECT ...) で名前付きの一時結果セットを定義し、その後の SELECT で参照できる仕組みです。

派生テーブル（FROM 内サブクエリ）と役割は近いですが、クエリの先頭に段階的な定義を書けるため、長いクエリの可読性が大きく上がります。同じ CTE を同じ SELECT 内で複数回参照することもできます。

再帰 CTE で階層構造

WITH RECURSIVE は自分自身を参照できる CTEです。組織図、コメントの親子、フォルダ階層、グラフの到達判定など、ループを含む探索を 1 本のクエリで書けます。

構造は「初期行（アンカー）」と「繰り返し部（再帰ステップ）」を UNION ALL で繋ぎます。停止条件を入れ忘れると無限ループになるので注意してください。

sql

-- 従業員と上司を辿って、全配下を階層深度つきで取得
WITH RECURSIVE subordinates AS (
  SELECT id, name, manager_id, 0 AS depth
  FROM employees
  WHERE id = :root_id                      -- アンカー: 起点

  UNION ALL

  SELECT e.id, e.name, e.manager_id, s.depth + 1
  FROM employees e
  JOIN subordinates s ON e.manager_id = s.id   -- 再帰ステップ
)
SELECT * FROM subordinates ORDER BY depth, name;

MATERIALIZED ヒント (PostgreSQL)

PostgreSQL 12 以降は CTE をオプティマイザがインライン展開するようになりました（以前は必ず実体化）。挙動を明示的に制御したいときに MATERIALIZED / NOT MATERIALIZED ヒントを使います。

WITH x AS MATERIALIZED (...): 必ず実体化し、1 回だけ評価して使い回す
WITH x AS NOT MATERIALIZED (...): インライン展開を許可し、参照先の条件とまとめて最適化させる
MySQL / SQL Server などはインライン展開が一般的で、ヒント構文は RDBMS 依存

落とし穴: CTE は銀の弾丸ではない

可読性は上がるが性能は上がるとは限らない。実体化される RDBMS / モードではインデックスが活きにくくなることがある
再帰の無限ループ: 親子関係にサイクルがあるとデータ量が爆発する。深さ制限 (depth < 100) や訪問済み配列で防御する
標準の差異: MySQL 5.7 以前、SQLite 古いバージョンは CTE 非対応。WITH RECURSIVE キーワードの要否も RDBMS で違う（PostgreSQL / SQLite は必須、SQL Server は RECURSIVE なしで OK）