SQL共通テーブル式（CTE）— WITH句

共通テーブル式（CTE）

CTEは単一のSQL文内で参照できる名前付きの一時的な結果セットを作成します。CTEは複雑なクエリをより読みやすくモジュラーにします。

基本的なCTE

WITH high_earners AS (
  SELECT name, salary, department_id
  FROM employees
  WHERE salary > 80000
)
SELECT h.name, h.salary, d.name AS department
FROM high_earners h
JOIN departments d ON h.department_id = d.id;

複数のCTE

WITH dept_stats AS (
  SELECT department_id,
    AVG(salary) AS avg_salary,
    COUNT(*) AS headcount
  FROM employees
  GROUP BY department_id
),
top_depts AS (
  SELECT * FROM dept_stats WHERE avg_salary > 70000
)
SELECT d.name, td.avg_salary, td.headcount
FROM top_depts td
JOIN departments d ON td.department_id = d.id;

再帰CTE

再帰CTEはアンカー（基本ケース）と再帰メンバーの2つの部分があります。

WITH RECURSIVE org_chart AS (
  -- アンカー：トップレベルのマネージャー
  SELECT id, name, manager_id, 1 AS depth
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- 再帰：各人の直属の部下を見つける
  SELECT e.id, e.name, e.manager_id, oc.depth + 1
  FROM employees e
  JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT * FROM org_chart ORDER BY depth, name;

再帰CTEのユースケース

• 組織図 / 管理階層
• 部品表（サブパーツを含む部品）
• カテゴリツリー / ネストメニュー
• グラフ走査

データベースサポート

データベース	基本CTE	再帰CTE
PostgreSQL	あり（8.4+）	あり（8.4+）
MySQL	あり（8.0+）	あり（8.0+）
SQLite	あり（3.8.3+）	あり（3.8.3+）
SQL Server	あり（2005+）	あり（2005+）

CTEとサブクエリの比較

CTEは名前が付いており、複数回参照でき、手続き的なコードのように上から下へ読めるため、深くネストされたサブクエリよりも一般的に好まれます。ただし、一部のデータベースではインラインサブクエリほど積極的にCTEを最適化しない場合があります。