サブクエリを WITH 句（CTE）で読みやすくする

SQL で複数のテーブルを組み合わせたり、集計結果をさらに集計する場面では「サブクエリ」がよく使われます。ところが、クエリが大きくなるとネストが深くなり、可読性や保守性が落ちることもしばしば。

そんなときに便利なのが WITH 句（Common Table Expression: CTE） です。今回はサブクエリと CTE の基本的な使い方・比較と、主要な RDBMS でのサポート状況をまとめます。

題材とするデータ

• users: ユーザ情報（user_id, name）
• order_items: 注文明細（user_id, order_date, quantity, price）
• DB-Fiddle

「過去 30 日 のみを対象」として、ユーザごとの購入金額合計を算出し、ランキング上位 2 名を取得してみます。

• users テーブル

user_id	name
1	Alice
2	Bob
3	Charlie

• order_items テーブル

user_id	order_date	qty	price
1	'2025-01-10'	2	1200.00
1	'2025-01-15'	1	3000.00
2	'2025-01-12'	3	800.00
2	'2025-01-20'	5	500.00
3	'2025-01-05'	1	10000.00

サブクエリとは？

サブクエリは、SELECT 文の中でさらに別の SELECT 文を入れ子にして利用するやり方です。今回の問題であれば、以下のようにクエリを書けます。

SELECT
    u.user_id,
    u.name,
    (
      SELECT SUM(oi.quantity * oi.price)
      FROM order_items AS oi
      WHERE oi.user_id = u.user_id
        AND oi.order_date >= DATE('2025-01-27') - INTERVAL '30' DAY
    ) AS total_spent_30days
FROM users AS u
ORDER BY total_spent_30days DESC
LIMIT 2;

ポイント

• (SELECT SUM(…)) の部分がサブクエリ
• users テーブルの各行ごとにサブクエリが実行され、該当ユーザの購入合計を算出
• クエリ自体は短いですが、条件や追加計算が増えるとネストが深くなって可読性が下がりがち

WITH 句（CTE）とは？

サブクエリは簡潔に書けるものの複雑化しやすいです。そこで登場するのが WITH 句 です。サブクエリを別名の「仮想テーブル」として事前に定義し、それを最終的な SELECT 文の中で扱えるようにします。いわば「一時的な名前付き結果セット」を作るイメージです。

WITH
  last_30days_spending AS (
    SELECT
      user_id,
      SUM(quantity * price) AS total_spent
    FROM order_items
    WHERE order_date >= DATE('2025-01-27') - INTERVAL '30' DAY
    GROUP BY user_id
  )
SELECT
  u.user_id,
  u.name,
  COALESCE(s.total_spent, 0) AS total_spent_30days
FROM users AS u
LEFT JOIN last_30days_spending AS s
  ON u.user_id = s.user_id
ORDER BY s.total_spent DESC
LIMIT 2;

ポイント

• last_30days_spending: 過去 30 日分の購入金額をユーザ単位で集計し、仮想テーブル（CTE）として定義
• 最終の SELECT 文で、users と CTE を結合する
• NULL 回避のために COALESCE を使い、購入がない場合は 0 円で表示

この書き方なら、「まずは 30 日分の集計テーブルを作り、それを使って最終的なユーザリストを表示」 というステップが見えやすく、可読性・保守性が上がります。

CTE には以下のようなメリットがあります。

1. 可読性向上
   ネストが深くならないので、クエリの全体像を把握しやすい。

2. 再利用性
   同じ集計結果を最終クエリ内で複数回使いたいときに便利。

3. 段階的にデバッグしやすい
   CTE の部分を単独で SELECT 文として検証することも可能。

主な RDBMS での CTE サポート状況

CTE は比較的新しい機能のため、古いバージョンでは未対応の場合もあります。以下に主要な RDBMS での対応状況をざっくりまとめました。

RDBMS	CTE 対応バージョン
MySQL	8.0 以降（5.7 までは未対応）
MariaDB	10.2 以降
PostgreSQL	8.4 以降（比較的早くから対応）
Oracle	9i 以降（バージョン 9.2 から）
SQL	Server 2005 以降（CTE は比較的早期対応）
SQLite	3.8.3 以降（Recursive CTE もサポート）

• 現在は大半のクラウド DB や最新バージョンの RDBMS が対応済み
• 古いバージョンの MySQL (5.7 以前) や MariaDB (10.2 未満) などを使っている場合は注意

WITH 句の使いどころ

• サブクエリ: 短いクエリや、一時的・単発でしか使わない場合にシンプルに書ける
• CTE: 同じサブクエリを何度も使う、またはクエリが複雑化して可読性が落ちるようになってきたら導入を検討。バージョンの互換性を要チェック

まとめ

• サブクエリはクエリを入れ子にできて便利だが、可読性が落ちがち
• WITH 句（CTE）を使うと、一時的な名前付き結果セットを作って 複雑な集計をシンプルに書ける
• ただし、使いたい環境が CTE に対応しているか 事前に確認を。特に MySQL 5.7 以前などは注意

CTE は覚えてしまえば簡単で、複雑なクエリの見通しをよくしてくれる強力な機能です。プロジェクトや分析業務で複雑なサブクエリに苦労している方は、この機会にぜひ試してみてください。

ここまで読んで頂きありがとうございました！