こんにちは、かつコーチです。
サブクエリを何段もネストしたSQLを書いて、自分で見返したときに「これ何をやっているんだっけ」と迷った経験はありませんか。
今回はそんなときに役立つCTE（Common Table Expression、共通テーブル式）を解説します。
WITH句を使ってクエリに名前を付け、段階的に処理を積み上げる書き方です。
PostgreSQL 16系を前提に、基本の構文からつまずきやすいポイント、更新系クエリとの組み合わせまで一気に押さえます。
CTEとは？
用語の定義：CTE（Common Table Expression）
CTEとは、WITH句を使ってクエリの一部に一時的な名前を付け、あとから通常のテーブルのように参照できるようにする仕組みです。
一時テーブルのように見えますが、実体はクエリの中でだけ有効な「名前付きのサブクエリ」です。
以下のような形で書きます。
WITH cte_name AS (
SELECT column_a, column_b
FROM some_table
WHERE column_a > 100
)
SELECT *
FROM cte_name
WHERE column_b = 'active';
cte_nameという名前で定義したサブクエリを、後続のSELECT文からテーブルのように使えるのがポイントです。
なぜCTEが必要なのか
サブクエリをそのままネストして書くと、SQLはどんどん横に長く、内側に深くなっていきます。
たとえば「注文金額の合計が1万円以上の顧客のうち、直近30日以内に注文がある人」を抽出するクエリをサブクエリだけで書くと、次のようになりがちです。
SELECT c.customer_id, c.customer_name
FROM customers c
WHERE c.customer_id IN (
SELECT o.customer_id
FROM orders o
WHERE o.customer_id IN (
SELECT customer_id
FROM orders
GROUP BY customer_id
HAVING SUM(amount) >= 10000
)
AND o.ordered_at >= CURRENT_DATE - INTERVAL '30 days'
);
内側から読まないと処理の流れが分からず、条件を1つ追加するだけでも該当箇所を探すのに苦労します。
CTEを使うと、処理のステップごとに名前を付けて上から順に読める形に整理できます。
基本の書き方
手順1：単純なCTEを1つ定義する
先ほどの例をCTEで書き直すと、以下のようになります。
WITH high_spenders AS (
SELECT customer_id
FROM orders
GROUP BY customer_id
HAVING SUM(amount) >= 10000
)
SELECT c.customer_id, c.customer_name
FROM customers c
JOIN high_spenders h ON c.customer_id = h.customer_id
JOIN orders o ON o.customer_id = c.customer_id
WHERE o.ordered_at >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY c.customer_id, c.customer_name;
「合計1万円以上使っている顧客」をhigh_spendersという名前で切り出したことで、後半のクエリが何をしているかが一目で分かります。
手順2：複数のCTEをつなげる
CTEはカンマ区切りで複数定義でき、後から定義したCTEの中で先に定義したCTEを参照することもできます。
WITH monthly_sales AS (
SELECT
customer_id,
DATE_TRUNC('month', ordered_at) AS sales_month,
SUM(amount) AS monthly_amount
FROM orders
GROUP BY customer_id, DATE_TRUNC('month', ordered_at)
),
top_customers AS (
SELECT customer_id
FROM monthly_sales
WHERE monthly_amount >= 50000
)
SELECT c.customer_name, m.sales_month, m.monthly_amount
FROM top_customers t
JOIN monthly_sales m ON m.customer_id = t.customer_id
JOIN customers c ON c.customer_id = t.customer_id
ORDER BY c.customer_name, m.sales_month;
月次売上の集計（monthly_sales）と、そこから優良顧客を抽出する処理（top_customers）を分けて書くことで、集計ロジックの見直しも1つのCTEブロックの中だけで完結します。
つまずきやすい設定・注意点
CTEに名前を付けるときは、既存のテーブル名と被らないようにしましょう。
被っていても構文エラーにはなりませんが、CTEの定義が優先されるため「テーブルの実データを見ているつもりがCTEの結果を見ていた」という混乱の元になります。
私は実際にordersという名前でCTEを定義してしまい、本来のテーブルより絞り込まれた結果しか出ないバグに30分ほど悩んだことがあります。
orders_filteredのように、CTEだと分かる接尾辞を付ける習慣をつけてから、この手の事故はなくなりました。
よくあるつまずきポイント・エラー対処
Before/After：ネストしたサブクエリ vs CTE
CTEを使わないサブクエリの書き方は、条件が増えるほど読みにくくなります。
-- ❌ Before：サブクエリをネストして条件を積み上げる
SELECT product_id, product_name
FROM products
WHERE product_id IN (
SELECT product_id
FROM order_items
WHERE order_id IN (
SELECT order_id
FROM orders
WHERE status = 'completed'
AND ordered_at >= CURRENT_DATE - INTERVAL '7 days'
)
);
-- ✅ After：CTEで処理のステップを分けて名前を付ける
WITH recent_completed_orders AS (
SELECT order_id
FROM orders
WHERE status = 'completed'
AND ordered_at >= CURRENT_DATE - INTERVAL '7 days'
),
recent_order_items AS (
SELECT DISTINCT oi.product_id
FROM order_items oi
JOIN recent_completed_orders r ON r.order_id = oi.order_id
)
SELECT p.product_id, p.product_name
FROM products p
JOIN recent_order_items i ON i.product_id = p.product_id;
Afterの形なら、「直近7日間の完了注文」と「その注文に含まれる商品」という2つのステップが名前だけで理解できます。
マテリアライズの挙動に注意する
PostgreSQL 12より前のバージョンでは、CTEは常にマテリアライズ（一度結果を確定させてから外側のクエリで使う挙動）される仕様でした。
PostgreSQL 12以降は、CTEが1回しか参照されず副作用もない場合、オプティマイザが自動的にインライン化（外側のクエリに展開して最適化）するようになっています。
意図的に一度結果を確定させたい場合は、CTE名の後にAS MATERIALIZEDを付けます。
WITH heavy_calc AS MATERIALIZED (
SELECT customer_id, SUM(amount) AS total
FROM orders
GROUP BY customer_id
)
SELECT * FROM heavy_calc WHERE total > 100000;
逆にインライン化させたい場合はAS NOT MATERIALIZEDを明示できます。
CTEを使ったのに実行計画が期待通り最適化されない、と感じたら、このマテリアライズの有無を確認するのが最初のチェックポイントです。
応用・一歩先の使い方
データ更新を伴うCTE（Data-Modifying CTE）
PostgreSQLではWITH句の中にINSERT・UPDATE・DELETEを書き、RETURNINGで結果を後続のクエリに渡すことができます。
在庫が0になった商品を非公開にしつつ、対象の商品名一覧を1回のクエリで取得する例です。
WITH out_of_stock AS (
UPDATE products
SET is_published = false
WHERE stock_count = 0
AND is_published = true
RETURNING product_id, product_name
)
SELECT product_id, product_name
FROM out_of_stock
ORDER BY product_name;
更新処理と結果確認を1回の往復で済ませられるため、アプリケーション側で「更新→再取得」の2クエリを書いていた処理をシンプルにできます。
ただし複数のデータ変更CTEを組み合わせる場合、実行順序は保証されない点に注意してください。
順序に依存する処理は、CTEを分けずに素直にトランザクション内で逐次実行する方が安全です。
まとめ
この記事のポイント
- CTEは
WITH句でクエリに名前を付け、段階的に処理を積み上げる書き方 - ネストしたサブクエリより読みやすく、修正箇所も特定しやすい
- PostgreSQL 12以降はCTEが自動でインライン化されることがあり、
MATERIALIZEDで挙動を制御できる INSERT・UPDATE・DELETEとRETURNINGを組み合わせたデータ変更CTEも使える
次に読むべき記事
CTEの基本を押さえたら、自分自身を参照する再帰CTEで階層データを扱う方法に進みましょう。
- 再帰CTEで階層データを扱う（近日公開）
- ウィンドウ関数の基本：ランキングと集計を両立する（近日公開）
タグ: PostgreSQL, 中級者向け, クエリ