SQL共通テーブル式(CTE)とWITH句
WITH句を使ったSQL CTEで、読みやすく再利用可能なクエリブロックをマスター。組織図やツリー構造などの階層データに対応する再帰CTEも解説します。
Advanced
詳細な説明
SQL共通テーブル式(CTE)とWITH句
共通テーブル式(CTE)は WITH キーワードで定義される名前付きの一時的な結果セットです。CTEは複雑なクエリを論理的な名前付きステップに分割することで可読性を向上させます。
基本構文
WITH active_customers AS (
SELECT id, name, email
FROM customers
WHERE status = 'active'
)
SELECT ac.name, COUNT(o.id) AS order_count
FROM active_customers ac
JOIN orders o ON ac.id = o.customer_id
GROUP BY ac.name;
複数のCTE
カンマで区切って複数のCTEを連結できます:
WITH monthly_sales AS (
SELECT DATE_TRUNC('month', sale_date) AS month, SUM(amount) AS total
FROM sales
GROUP BY DATE_TRUNC('month', sale_date)
),
avg_sales AS (
SELECT AVG(total) AS avg_monthly FROM monthly_sales
)
SELECT ms.month, ms.total,
CASE WHEN ms.total > a.avg_monthly THEN 'Above' ELSE 'Below' END AS performance
FROM monthly_sales ms
CROSS JOIN avg_sales a;
再帰CTE
再帰CTEは階層型またはツリー構造のデータに対して非常に有効です:
WITH RECURSIVE org_chart AS (
SELECT id, name, manager_id, 1 AS level
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.name, e.manager_id, oc.level + 1
FROM employees e
JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT * FROM org_chart ORDER BY level, name;
CTE vs サブクエリ vs 一時テーブル
- CTE: 可読性に優れ、単一クエリのスコープに最適。ほとんどのデータベースでインラインに最適化される
- サブクエリ: シンプルで一度きりのネストロジックに適している
- 一時テーブル: 中間結果が複数のクエリで繰り返し使用される場合や、インデックスが必要な場合に適している
パフォーマンスに関する注意
- PostgreSQL 12以降では、CTEはオプティマイザによってインライン化される(常にマテリアライゼーション境界ではなくなった)
- MySQL 8.0以降では、非再帰CTEは外部クエリにマージされる
- 再帰CTEは無限ループを防ぐための終了条件が必要
- 安全策として、再帰CTEには深度制限を追加する
CTEは複雑なSQLを書くためのモダンで読みやすいアプローチであり、すべての主要なデータベースシステムでサポートされています。
ユースケース
プロジェクト管理ツールが、再帰CTEを使ってタスクの依存関係ツリーを走査し、サブタスクの完全な階層構造とその完了状況を表示する場面。