SQL
#04 SQLをちゃんと理解する

CTEで読みやすくする

CTEとは

CTE（共通テーブル式）? WITH句で定義する一時的な名前付きクエリ。`WITH monthly_sales AS (SELECT ...)` のように使い、複雑なサブクエリを読みやすく整理できる。再帰CTEでツリー構造も扱える。 （Common Table Expression、共通テーブル式）は、WITH 句を使ってSQLの冒頭に「一時的な名前付きクエリ」を定義する機能です。サブクエリをネストして書くのと結果は同じですが、コードの読みやすさが大きく向上します。

-- WITH句の基本構文
WITH cte_name AS (
    SELECT ...
    FROM ...
    WHERE ...
)
SELECT *
FROM cte_name
WHERE ...;

サブクエリ vs CTE

同じクエリをサブクエリとCTEで書き比べてみましょう。

サブクエリ版（ネストが深くなりがち）

SELECT dept_name, avg_salary
FROM (
    SELECT
        d.name AS dept_name,
        AVG(e.salary) AS avg_salary
    FROM employees e
    JOIN departments d ON e.department_id = d.id
    WHERE e.hire_date >= '2020-01-01'
    GROUP BY d.name
) AS dept_stats
WHERE avg_salary > 400000
ORDER BY avg_salary DESC;

CTE版（流れが上から下に読める）

WITH dept_stats AS (
    SELECT
        d.name AS dept_name,
        AVG(e.salary) AS avg_salary
    FROM employees e
    JOIN departments d ON e.department_id = d.id
    WHERE e.hire_date >= '2020-01-01'
    GROUP BY d.name
)
SELECT dept_name, avg_salary
FROM dept_stats
WHERE avg_salary > 400000
ORDER BY avg_salary DESC;

CTEはクエリの意図を「まず部門ごとの平均給与を計算し、その後条件でフィルタリングする」という形で上から下に自然に読めます。

複数のCTEを定義する

CTE（WITH句）の構造と流れ CTE 1: orders_2025 SELECT customer_id, SUM(amount) AS total FROM orders WHERE year=2025 CTE 2: high_value SELECT customer_id FROM orders_2025 WHERE total >= 100000 CTE 3: final_result SELECT c.name, h.total FROM customers c JOIN high_value h ON ... メインクエリ SELECT * FROM final_result ORDER BY total DESC; WITH句の構文 WITH cte1 AS ( SELECT ... -- 1つ目のCTE ), cte2 AS ( SELECT ... FROM cte1 -- 前のCTEを参照 ) SELECT * FROM cte2; -- メインクエリ CTEのメリット ✓ 複雑なクエリをステップごとに分割 ✓ 意味のある名前でクエリの意図を明確化 ✓ 同じ結果セットを複数回参照できる ✓ デバッグ時にステップ単位で実行可能 　（CTEだけ実行して結果を確認）
図1: 複数のCTEを定義してメインクエリで組み合わせる構造

WITH句には複数のCTEをカンマ区切りで定義でき、後のCTEは前のCTEを参照できます。

WITH
-- Step 1: 2025年の注文を抽出
orders_2025 AS (
    SELECT customer_id, SUM(amount) AS total_amount
    FROM orders
    WHERE YEAR(created_at) = 2025
    GROUP BY customer_id
),
-- Step 2: 高額顧客を判定（前のCTEを参照）
high_value_customers AS (
    SELECT customer_id, total_amount
    FROM orders_2025
    WHERE total_amount >= 100000
),
-- Step 3: 顧客マスタと結合
final_result AS (
    SELECT c.name, c.email, h.total_amount
    FROM customers c
    JOIN high_value_customers h ON c.id = h.customer_id
)
-- メインクエリ
SELECT *
FROM final_result
ORDER BY total_amount DESC;

このように複数のCTEを使うことで、複雑なクエリをステップごとに分解して書くことができます。各CTEに意味のある名前をつけることで、コードレビューや後からの修正がしやすくなります。

再帰CTE

CTE（共通テーブル式）? WITH句で定義する一時的な名前付きクエリ。`WITH monthly_sales AS (SELECT ...)` のように使い、複雑なサブクエリを読みやすく整理できる。再帰CTEでツリー構造も扱える。 の強力な機能のひとつが再帰CTEです。ツリー構造（組織図・カテゴリ階層・部品表など）の再帰的なデータを扱うのに適しています。

再帰CTEのイメージ（組織ツリー） 代表取締役（depth=0） id=1, manager_id=NULL 部長A（depth=1） id=2, manager_id=1 部長B（depth=1） id=3, manager_id=1 課長C（depth=2） id=4, manager_id=2 課長D（depth=2） id=5, manager_id=2 課長E（depth=2） id=6, manager_id=3 実行ステップ Step 1: アンカー manager_id IS NULL Step 2: 再帰 1回目 depth=1 の社員 Step 3: 再帰 2回目 depth=2 の社員 Step 4: 終了 0行返ったら終了 再帰CTEの構文 WITH RECURSIVE org_tree AS ( SELECT id, name, 0 AS depth FROM employees WHERE manager_id IS NULL UNION ALL SELECT e.id, e.name, ot.depth+1 FROM employees e JOIN org_tree ot ON e.manager_id=ot.id )
図2: 再帰CTEによるツリートラバースの仕組み

再帰CTEの構造

WITH RECURSIVE cte_name AS (
    -- アンカーメンバー（再帰の起点）
    SELECT ... FROM table WHERE 起点条件

    UNION ALL

    -- 再帰メンバー（前のステップの結果を参照）
    SELECT ... FROM table
    JOIN cte_name ON 結合条件  -- CTEを自己参照
)
SELECT * FROM cte_name;

実例：組織の全階層を取得

-- employeesテーブル
-- id | name     | manager_id
-- 1  | 代表取締役| NULL
-- 2  | 部長A    | 1
-- 3  | 部長B    | 1
-- 4  | 課長C    | 2

WITH RECURSIVE org_tree AS (
    -- アンカー: 最上位（社長）から始める
    SELECT id, name, manager_id, 0 AS depth
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- 再帰: 前のステップの部下を取得
    SELECT e.id, e.name, e.manager_id, ot.depth + 1
    FROM employees e
    JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT
    REPEAT('  ', depth) || name AS hierarchy,
    depth
FROM org_tree
ORDER BY id;

実行結果：

| hierarchy       | depth |
|-----------------|-------|
| 代表取締役       | 0     |
|   部長A          | 1     |
|     課長C        | 2     |
|   部長B          | 1     |

再帰CTEの処理の流れ：

  1. アンカーメンバーで manager_id IS NULL の行（社長）を取得
  2. 再帰メンバーで前のステップの結果に JOIN して部下を取得
  3. 返される行が0になったら終了

無限ループへの注意

再帰CTEはデータが循環参照を持つと無限ループになります。多くのDBMSには最大再帰回数の上限があり（デフォルトは100〜1000回）、超えるとエラーになります。

-- PostgreSQL での最大再帰回数の指定
WITH RECURSIVE ...
SELECT * FROM cte_name
-- LIMIT を設けるか、SET max_recursive_iterations を調整

CTEはマテリアライズされるか

CTEが複数回参照される場合、「毎回クエリを再実行するのか、1回だけ実行して結果をキャッシュするのか」という疑問があります。

DBMSデフォルト動作
PostgreSQL 12以降オプティマイザが判断（MATERIALIZED / NOT MATERIALIZED ヒントで制御可）
MySQL 8.0以降基本的にインライン展開（マテリアライズしない）
SQL Serverオプティマイザが判断

PostgreSQLではCTEが複数回参照される場合、明示的にマテリアライズを指示できます。

-- PostgreSQL: キャッシュを強制
WITH expensive_cte AS MATERIALIZED (
    SELECT ... -- 重い集計クエリ
)
SELECT * FROM expensive_cte WHERE ...
UNION ALL
SELECT * FROM expensive_cte WHERE ...;

-- PostgreSQL: キャッシュを無効化（インライン展開）
WITH simple_cte AS NOT MATERIALIZED (
    SELECT * FROM products WHERE active = true
)
SELECT * FROM simple_cte;

CTEを使うべきタイミング

状況使うべき構文
一度しか使わない単純な条件サブクエリ
複数ステップで処理を分けたいCTE
複数回同じ結果を参照したいCTE（マテリアライズを検討）
ツリー構造の再帰処理再帰CTE

まとめ

  • CTE（WITH句）はサブクエリに名前をつけて読みやすくする機能
  • 複数のCTEをカンマ区切りで定義でき、後のCTEは前のCTEを参照できる
  • 再帰CTEは UNION ALL で自己参照し、ツリー構造のトラバースに使う
  • マテリアライズの挙動はDBMSやバージョンによって異なる
  • 複雑なクエリをステップごとに分解するとメンテナンスしやすい

次回は GROUP BY? 指定した列の値が同じ行をまとめてグループ化する句。COUNT・SUM・AVGなどの集計関数と組み合わせて使う。「カテゴリ別の件数を集計」などに使用する。 と HAVING句? GROUP BY後のグループに対して絞り込み条件を適用する句。`HAVING COUNT(*) >= 5` のように集計結果でフィルタする。行レベルの絞り込みはWHERE、グループへの絞り込みはHAVING。 を正しく使う方法を解説します。