#26│CTEとサブクエリはどちらが速いか ― 実行SQLコストの正しい理解

「CTEは遅い」は本当か

SQLのコードレビューでこんな指摘を受けたことはないだろうか。

「WITH句（CTE）は毎回再計算されるから、サブクエリのほうが速い」

あるいは逆に、

「CTEは結果をキャッシュするからサブクエリより速い」

どちらも「正しい場合もあるが、常に正しいわけではない」という答えになる。CTEとサブクエリのどちらが速いかは、使用するSQLエンジンと文脈によって異なる。

この記事では「CTEとは何か」「サブクエリとどう違うか」を正確に整理したうえで、Presto（Treasure Data）・BigQuery・MySQLそれぞれでの挙動の違いと、通販現場での実践的な選択基準を解説する。

CTEとサブクエリの違いを正確に理解する

まず「同じ結果を出すCTEとサブクエリ」を並べて確認しよう。

-- バージョン1：CTE（WITH句）
WITH order_summary AS (
    SELECT
        customer_id,
        COUNT(order_id)   AS order_count,
        SUM(total_amount) AS total_spent
    FROM orders
    WHERE status = 'completed'
    GROUP BY customer_id
)
SELECT
    customer_id,
    order_count,
    total_spent
FROM order_summary
WHERE total_spent >= 10000
ORDER BY total_spent DESC;


-- バージョン2：インラインサブクエリ
SELECT
    customer_id,
    order_count,
    total_spent
FROM (
    SELECT
        customer_id,
        COUNT(order_id)   AS order_count,
        SUM(total_amount) AS total_spent
    FROM orders
    WHERE status = 'completed'
    GROUP BY customer_id
) AS order_summary
WHERE total_spent >= 10000
ORDER BY total_spent DESC;

どちらも同じ結果を返す。SQL の意味論的には等価だ。では実行速度は？

エンジン別の挙動

Presto（Treasure Data）の場合

Presto では CTE は基本的にビューとして扱われる（マテリアライズされない）。つまりCTEを複数箇所で参照すると、その都度展開されて実行される。

-- ⚠ Prestoでは order_summary が2回計算される可能性がある

WITH order_summary AS (
    SELECT customer_id, SUM(total_amount) AS total_spent
    FROM orders
    WHERE status = 'completed'
    GROUP BY customer_id
)
SELECT *
FROM order_summary
WHERE total_spent >= 10000

UNION ALL

SELECT *
FROM order_summary          -- ← 同じCTEを2回参照
WHERE total_spent < 10000;

このクエリでは order_summary が2回計算される可能性がある。サブクエリを使った場合も同じだ。Presto においては CTE とインラインサブクエリのパフォーマンスはほぼ同等であり、使い分けの基準はパフォーマンスではなく可読性・保守性になる。

確実に1回だけ計算させたい場合は、一時テーブル（Treasure Data では CREATE TABLE AS SELECT）に書き出す方法が有効だ。

-- 確実に1回だけ計算させる：一時テーブルに書き出す
CREATE TABLE order_summary_tmp AS
SELECT customer_id, SUM(total_amount) AS total_spent
FROM orders
WHERE status = 'completed'
GROUP BY customer_id;

-- 以降は order_summary_tmp を参照
SELECT * FROM order_summary_tmp WHERE total_spent >= 10000;
SELECT * FROM order_summary_tmp WHERE total_spent < 10000;

BigQuery の場合

BigQuery では CTE はスロットを共有するが、再利用はされない。Presto と同様にCTEを複数回参照すると複数回実行される。ただし BigQuery のクエリオプティマイザが共通部分を自動的にキャッシュする場合もある（保証はされない）。

MySQL の場合

MySQL 8.0以降では CTE（WITH 句）がサポートされている。MySQL では CTE は一時テーブルとして実体化（マテリアライズ）されることがある。特に派生テーブル（インラインサブクエリ）よりも CTE のほうがマテリアライズされやすく、複数回参照する場合に有利になることがある。

一方でマテリアライズは「一時テーブルへの書き込みコスト」を伴うため、1回しか参照しない場合はインラインサブクエリのほうが速い場合もある。

エンジン別まとめ表

エンジン	CTEのデフォルト挙動	複数参照時の挙動	パフォーマンスの選択基準
Presto（TD）	ビューとして展開（非マテリアライズ）	複数回実行の可能性あり	1回参照ならCTE/サブクエリ同等。複数参照なら一時テーブルへ
BigQuery	非マテリアライズが基本	複数回実行の可能性あり	同上
MySQL 8.0+	マテリアライズされやすい	1回だけ計算（有利）	複数参照ならCTEが有利な場合あり
PostgreSQL	実体化（MATERIALIZED）or非実体化を選択可能	MATERIALIZED指定で1回	`MATERIALIZED` / `NOT MATERIALIZED` を明示指定

パフォーマンスよりも可読性を優先する場面

Prestoにおいて「CTEかサブクエリか」の選択は、パフォーマンスの差がほぼないため可読性・保守性で決めてよい。

CTE が有利な場面は明確だ。

場面1：同じサブクエリを複数回使う

-- ❌ サブクエリだと同じロジックを2回書く
SELECT a.customer_id
FROM (
    SELECT customer_id, SUM(total_amount) AS total
    FROM orders WHERE status = 'completed' GROUP BY customer_id
) a
JOIN (
    SELECT customer_id, SUM(total_amount) AS total
    FROM orders WHERE status = 'completed' GROUP BY customer_id
) b ON a.customer_id = b.customer_id
WHERE a.total > b.total;  -- これは意味をなさないが、同じロジックを書かざるをえないケースがある

-- ✅ CTEなら1箇所だけ書く（ロジックの重複がない）
WITH customer_totals AS (
    SELECT customer_id, SUM(total_amount) AS total
    FROM orders WHERE status = 'completed' GROUP BY customer_id
)
SELECT a.customer_id
FROM customer_totals a
JOIN customer_totals b ON a.customer_id = b.customer_id;

場面2：複雑なクエリを段階的に書く

-- ❌ サブクエリのネストが深くなりすぎる（読めない）
SELECT *
FROM (
    SELECT *, ROW_NUMBER() OVER (...) AS rn
    FROM (
        SELECT *, LAG(order_date) OVER (...) AS prev_date
        FROM (
            SELECT * FROM orders WHERE status = 'completed'
        )
    )
) WHERE rn = 1;

-- ✅ CTEで段階的に書く（各ステップの意図が明確）
WITH completed_orders AS (
    SELECT * FROM orders WHERE status = 'completed'
),
with_prev_date AS (
    SELECT *, LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS prev_date
    FROM completed_orders
),
ranked AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
    FROM with_prev_date
)
SELECT * FROM ranked WHERE rn = 1;

CTE 版は「何をしているか」が各ステップで明確だ。3ヶ月後に見返しても理解できる。

サブクエリが有利な場面

逆にサブクエリのほうが適している場面もある。

場面1：1回しか使わない単純な絞り込み

-- CTE を使うほどではないシンプルなケース
-- ✅ インラインで書くほうがコンパクト
SELECT *
FROM orders
WHERE customer_id IN (
    SELECT customer_id FROM customers WHERE channel = 'organic'
)
AND status = 'completed';

CTE で organic_customers と名前をつけるほどでもない場合は、インラインサブクエリのほうがコードが短くてすむ。

場面2：スカラサブクエリで1つの値を取る

-- 全体の平均注文金額と各注文を比較する
SELECT
    order_id,
    customer_id,
    total_amount,
    (SELECT AVG(total_amount) FROM orders WHERE status = 'completed')  AS overall_avg,
    total_amount - (SELECT AVG(total_amount) FROM orders WHERE status = 'completed')  AS diff_from_avg
FROM orders
WHERE status = 'completed';

スカラサブクエリ（1値を返すサブクエリ）は SELECT 句の中に自然に書けるため、CTE より直感的な場合がある。ただしこの例では AVG が2回計算されているため、CTE にまとめるほうが効率的だ。

-- ✅ CTEでまとめて1回の計算にする
WITH avg_stats AS (
    SELECT AVG(total_amount) AS overall_avg
    FROM orders WHERE status = 'completed'
)
SELECT
    o.order_id,
    o.customer_id,
    o.total_amount,
    a.overall_avg,
    o.total_amount - a.overall_avg  AS diff_from_avg
FROM orders       o
CROSS JOIN avg_stats  a
WHERE o.status = 'completed';

実務での判断基準まとめ

			
CTEを使う場面：
  ✅ 同じサブクエリを2回以上参照する
  ✅ クエリが複数のステップに分かれていて、各ステップに名前をつけたい
  ✅ チームでコードをレビューする。他者が読む可能性がある
  ✅ 後から条件を変更・追加する可能性がある
サブクエリを使う場面：
  ✅ 1回しか参照しない単純な絞り込み
  ✅ SELECT句の中でスカラ値を1つ取得する
  ✅ 短くてシンプルに済む場合
一時テーブルを使う場面（Presto / Treasure Data）：
  ✅ 同じサブクエリを確実に1回だけ実行したい
  ✅ 複数のクエリからまたいで参照したい中間結果がある
  ✅ 計算コストが非常に高く、再計算を避けたい

		

よくある誤解：「CTEはビューと同じ」

CTEをビュー（CREATE VIEW）と混同する人がいるが、両者は異なる。

	CTE	ビュー
スコープ	そのクエリ内のみ	データベース全体
永続性	クエリ実行中のみ	永続的に保存される
パラメータ	なし	なし
利用頻度	1クエリの中で繰り返し使う	複数のクエリから繰り返し使う

CTE は「このクエリの中だけで使う一時的な定義」、ビューは「データベース全体から使える永続的な定義」だ。同じロジックを複数のクエリで使い回す場合はビューが適している。

実務での運用ヒント

① CTEの命名は「何をしているか」を動詞+名詞で

WITH tmp AS (...) という命名は最悪だ。WITH active_customers_last_month AS (...) のように「何のためのデータか」を明示する命名にすると、後から読んだ人がクエリ全体の構造を把握しやすくなる。

② PrestoではCTEを分割して実行するとデバッグが楽

巨大なクエリをデバッグするとき、CTEを途中で切り取って SELECT * FROM cte_name LIMIT 100 と実行できる。サブクエリのネストが深いと中間結果を見るのが大変だが、CTEなら途中の段階を確認しやすい。これは開発効率の観点でCTEを使う重要な理由の一つだ。

③ 「CTEがパフォーマンスを悪化させた」ケースへの対処

「CTEに書き換えたら遅くなった」という報告を受けることがある。多くの場合、CTEへの書き換えによってオプティマイザが行ったプッシュダウン最適化（WHERE句の条件をスキャン段階まで押し下げる処理）が阻害されたことが原因だ。EXPLAINで確認し、CTEを解体してインラインサブクエリに戻すか、一時テーブルに変更するかを判断する。

まとめ

観点	結論
Prestoでの速度差	CTEとサブクエリはほぼ同等。複数回参照するなら一時テーブルへ
選択の基準	パフォーマンスではなく可読性・保守性で決める
CTEが向く場面	複数参照・複雑な多段変換・チームでのコードレビュー
サブクエリが向く場面	単純な1回使い切り・スカラ値の取得
共通の大原則	意図が明確に伝わるほうを選ぶ

「CTEは遅い」「サブクエリのほうが速い」という俗説に振り回されないためには、エンジンごとの挙動を正確に理解することが重要だ。Prestoにおいては、パフォーマンスを理由にCTEを避ける必要はほぼなく、むしろ可読性のためにCTEを積極的に使うことが現場では推奨される。