深くネストされたサブクエリの問題点
プロジェクト開始から6ヶ月が経った頃、同僚から一つのSQLクエリを渡されました。それはもはや迷路としか言いようのない代物に成長していました。三重にネストされたサブクエリが互いを参照し合い、WHERE句だけで12行にも及んでいたのです。200万行のテーブルに対して実行すると、8秒かかっていました。
そのクエリは最初から悪く書かれていたわけではありません。有機的に成長していったのです。ある開発者がフィルタリングされたデータセットが必要でサブクエリを書き、後から別の人がそれを集計する必要が生じてもう一層を加えました。最終的にはクエリは技術的には正しくても、実用上はメンテナンス不可能な状態になっていました。
MySQL、PostgreSQL、SQL Serverをさまざまなプロジェクトで扱ってきましたが、それぞれのオプティマイザには独自のクセがあります。しかし共通しているのは、ネストされたサブクエリはどのデータベースでも急速に複雑になりがちだということです。オプティマイザがうまく処理してくれるかどうかはわかりませんし、さらに重要なのは、コードから数ヶ月離れた後では人間にはまったく理解できなくなるということです。
ネストされたサブクエリが扱いにくい理由
- 可読性：内側から外側に向かって読む必要があります。最も内側のクエリが最初に実行されますが、コードの中に埋もれています。
- デバッグ：特定の層をテストするために、その都度手動で抽出しなければなりません。
- 重複：クエリの2箇所が同じフィルタリングされたデータセットを必要とする場合、サブクエリを二度書く必要があります。
- オプティマイザの挙動：一部のデータベース（特にMySQL 8.0以前）では、相関サブクエリを予測しにくい方法でマテリアライズするため、フルスキャンが繰り返されることがあります。
CTEとは何か、どのように機能するか
CTE（共通テーブル式）は、WITHキーワードを使って定義され、メインクエリの前に名前付きの一時的な結果セットを定義することができます。SQLの途中にネストするのではなく、ロジックの一部に名前を付けてクリーンに参照できると考えてください。
基本構文：
WITH cte_name AS (
SELECT ...
FROM ...
WHERE ...
)
SELECT *
FROM cte_name
WHERE ...;
一つのWITH句にカンマで区切って複数のCTEを定義することができます：
WITH
active_users AS (
SELECT id, name, email
FROM users
WHERE status = 'active'
),
recent_orders AS (
SELECT user_id, COUNT(*) AS order_count
FROM orders
WHERE created_at >= NOW() - INTERVAL '30 days'
GROUP BY user_id
)
SELECT u.name, u.email, COALESCE(o.order_count, 0) AS orders
FROM active_users u
LEFT JOIN recent_orders o ON u.id = o.user_id;
PostgreSQL、MySQL 8.0以降、SQL Serverはすべてこの構文をサポートしています。古いMySQLバージョンでは、回避策として通常は一時テーブルやビューを使用します。これも、データベースのバージョンを最新に保つ理由の一つです。
マテリアライズドCTEとノンマテリアライズドCTE
すべてを変換し始める前に、CTEは実行計画の観点ではサブクエリと必ずしも等価ではないことを知っておく必要があります。
- PostgreSQL（12以前）：CTEは常にマテリアライズドされていました。一度評価され、結果が一時的に保存されます。CTEが何回参照されるかや結果セットのサイズによって、パフォーマンスに良くも悪くも影響しました。
- PostgreSQL 12以降：オプティマイザは、CTEが一度だけ参照される場合はインライン化できるようになりました。また、
WITH cte AS MATERIALIZED (...)で強制的にマテリアライズすることもできます。 - MySQL 8.0以降：CTEは一般的にノンマテリアライズド（インライン化）されますが、オプティマイザが別の判断をする場合もあります。
パフォーマンスのためにリファクタリングする際はこの点を念頭に置いてください。クエリの中で3回参照されているCTEは、マテリアライズされていない場合、一部のデータベースでは3回実行される可能性があります。
ネストされたサブクエリのリファクタリング：ステップバイステップ
アプローチは原則として単純です：最も内側のサブクエリから始めて名前を付け、外側に向かって作業していきます。各層は埋もれたコードではなく、可読性が高くテスト可能なブロックになります。
例1 — 2段階ネストのサブクエリ
会社全体の平均給与を上回る平均給与を持つ部署名を検索するクエリです：
-- 変換前：ネスト・繰り返しのあるクエリ
SELECT department_name
FROM departments
WHERE id IN (
SELECT department_id
FROM employees
WHERE salary > (
SELECT AVG(salary)
FROM employees
)
GROUP BY department_id
HAVING AVG(salary) > (
SELECT AVG(salary)
FROM employees
)
);
SELECT AVG(salary) FROM employeesが2回登場していることに注目してください。エンジンとバージョンによっては、データベースがこれを2回実行する可能性があります。
-- 変換後：CTEバージョン
WITH
company_avg AS (
SELECT AVG(salary) AS avg_salary
FROM employees
),
high_earning_depts AS (
SELECT department_id
FROM employees
CROSS JOIN company_avg
WHERE salary > company_avg.avg_salary
GROUP BY department_id
HAVING AVG(salary) > (SELECT avg_salary FROM company_avg)
)
SELECT d.department_name
FROM departments d
INNER JOIN high_earning_depts hed ON d.id = hed.department_id;
company_avg CTEは一度だけ定義されます。クエリは上から下へと読めます：会社の平均を定義し、閾値を満たす部署を見つけ、部署名を検索します。
例2 — 3段階のネスト
3段階のネスト？名前付きCTEなら難なく対応できます。サプライヤー#42の商品を含む注文を行ったお客様のうち、アカウント作成から1年以上経過しているお客様を取得する必要があるとします：
-- 変換前：3段階の深さ
SELECT customer_name
FROM customers
WHERE id IN (
SELECT customer_id
FROM orders
WHERE id IN (
SELECT order_id
FROM order_items
WHERE product_id IN (
SELECT id
FROM products
WHERE supplier_id = 42
)
)
)
AND account_created < NOW() - INTERVAL '1 year';
-- 変換後：名前付き層
WITH
supplier_products AS (
SELECT id AS product_id
FROM products
WHERE supplier_id = 42
),
qualifying_orders AS (
SELECT DISTINCT oi.order_id
FROM order_items oi
INNER JOIN supplier_products sp ON oi.product_id = sp.product_id
),
qualifying_customers AS (
SELECT DISTINCT o.customer_id
FROM orders o
INNER JOIN qualifying_orders qo ON o.id = qo.order_id
)
SELECT c.customer_name
FROM customers c
INNER JOIN qualifying_customers qc ON c.id = qc.customer_id
WHERE c.account_created < NOW() - INTERVAL '1 year';
各CTEを個別に実行して出力を確認できるようになりました。デバッグはもはや考古学的作業ではなくなります。どの層が誤った結果を生成しているかを正確に特定し、修正して先に進むことができます。
検証とパフォーマンスモニタリング
まず結果が一致することを確認する
リファクタリングしたクエリが元のクエリと等価であると仮定する前に、簡単な比較で検証してください。同じデータセットで両方のクエリを実行し、行数が一致することを確認します：
-- 行数の比較
SELECT COUNT(*) FROM ( /* 元のネストバージョン */ ) AS old_q;
SELECT COUNT(*) FROM ( /* CTEバージョン */ ) AS new_q;
-- PostgreSQL：結果セットのチェックサムを比較
SELECT md5(string_agg(customer_name::text, '' ORDER BY customer_name))
FROM ( /* クエリ */ ) q;
EXPLAIN ANALYZEで実行計画を比較する
-- PostgreSQL
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
WITH supplier_products AS (
SELECT id FROM products WHERE supplier_id = 42
)
SELECT ...;
-- MySQL 8.0+
EXPLAIN FORMAT=JSON
WITH supplier_products AS (
SELECT id FROM products WHERE supplier_id = 42
)
SELECT ...;
特に以下の点に注目してください：
- シーケンシャルスキャン vs インデックススキャン：CTEは常に述語をプッシュダウンするわけではありません。CTEが中間結果セットのフルスキャンを強制することがあります。
- 実際の行数 vs 推定行数：大きな差異は統計情報が古いことを示します。PostgreSQLでは
ANALYZE table_name、MySQLではANALYZE TABLE table_nameを実行して統計を更新してください。 - CTEスキャンノード（PostgreSQL）：大きなデータセットで一度しか参照されない「CTE Scan」が見られる場合、
NOT MATERIALIZED（PostgreSQL 12以降）を追加してオプティマイザがインライン化できるようにしてください。
-- PostgreSQL 12以降でインライン化を強制する
WITH supplier_products AS NOT MATERIALIZED (
SELECT id FROM products WHERE supplier_id = 42
)
SELECT ...;
CTEがパフォーマンス向上に役立たない場合
CTEはほぼ常に可読性を向上させます。パフォーマンスは別問題です。サブクエリがCTEよりも優れたパフォーマンスを発揮する可能性がある2つのシナリオ：
- SELECTにおける単一値の相関サブクエリ：行ごとに1つの集計値を取得していて、カバリングインデックスがある場合、オプティマイザが早期に短絡評価できることがあります。CTEのJOINは不要な作業を増やす可能性があります。
- 非常に小さいサブクエリの結果セット：設定テーブルからわずかなIDを返すサブクエリの場合、オプティマイザは何もマテリアライズせずに実行計画の深い部分までフィルタをプッシュダウンできることが多いです。CTEでラップすると、その最適化を妨げる層が追加される可能性があります。
私の経験則：まず可読性のためにリファクタリングし、次にEXPLAINで計測してください。CTEバージョンの方が遅い場合は、元に戻す前にインデックスを追加してみてください。9割のケースでボトルネックはCTE構造そのものではなく、インデックスの欠如です。
再帰CTEについて
CTEはWITH RECURSIVEを使った再帰もサポートしており、組織図、ツリー構造、グラフ探索など、アプリケーション側のループやデータベースへの複数回のラウンドトリップが必要だった階層的なクエリを可能にします。これは別のトピックです。まず非再帰CTEをマスターしましょう。再帰は同じ基礎の上に構築されます。
