👑 CTE (共通テーブル式)
前回、FROM句の中で一時テーブルを作る導出テーブルを学びましたね。今回は、それと並ぶ、あるいはそれ以上に強力で、プロのSQLエンジニアが最も愛用するテクニックの一つ「CTE（Common Table Expression：共通テーブル式）」を紹介します！
💡 第1章：CTEとは何か？なぜ使うのか？
1-1. CTEの正体
CTEは、クエリの先頭でWITH句を使って定義する一時的な名前付きの結果セットです。導出テーブルと同様に一時的な仮想テーブルですが、構文が大きく異なります。
1-2. CTEの基本構文
CTEは、必ずSELECT文の直前に定義します。
-- ① まず、「WITH [CTEの名前] AS」で定義を始める
WITH SalesDetails AS (
-- ② 内部クエリ：この結果が一時テーブルとなる
SELECT
YEAR(OrderDate) AS OrderYear,
SalesOrderID
FROM
Sales.SalesOrderHeader
)
-- ③ メインクエリ：FROM句でCTEの名前をテーブルのように呼び出す
SELECT
OrderYear,
COUNT(*) AS SalesCount
FROM
SalesDetails -- ★ FROM句でSalesDetailsをテーブル名のように使う
WHERE
OrderYear = 2006
GROUP BY
OrderYear;
⚠️ 実行時の注意点:
CTEを定義した後、必ず続けてメインクエリ（SELECT文）を実行する必要があります。WITHからSELECTの最後までをまとめてハイライトして実行しましょう。
🔄 第2章：CTEの最大メリット：再利用と自己結合
導出テーブルでは、同じ処理を複数回使いたい場合、その定義全体を何度も書く必要がありました。しかし、CTEなら一度定義すれば何度でも参照できます。
2-1. 自己結合（Self Join）がスッキリ！
「今年の売上」と「去年の売上」を同じ行で比較したい場合、自分自身とテーブルを結合する自己結合が必要です。
【CTEを使った解決策】
SalesByYearというCTEを一度だけ定義し、「年ごとの合計売上」を計算します。メインクエリで、その
SalesByYearを「CurrentYearSales」と「PriorYearSales」という別々の別名で二回参照します。
WITH SalesByYear AS (
-- ★ 定義はここだけ！年と合計売上を計算
SELECT
YEAR(OrderDate) AS SalesYear,
SUM(TotalDue) AS TotalSales
FROM
Sales.SalesOrderHeader
GROUP BY
YEAR(OrderDate)
)
SELECT
CY.SalesYear,
CY.TotalSales AS AnnualSales,
PY.TotalSales AS PriorYearSales -- 前年の売上
FROM
SalesByYear AS CY -- 💡 1回目：Current Yearとして参照
LEFT OUTER JOIN
SalesByYear AS PY -- 💡 2回目：Prior Yearとして参照
ON CY.SalesYear - 1 = PY.SalesYear; -- 今年の年 - 1 = 前年の年で結合
👉 結果: 導出テーブルで同じクエリを二度書く必要がなくなり、非常にシンプルで間違いのないコードになりました！
2-2. 複数のCTEを連続で定義（処理の段階分け）
CTEは、コンマ（,）で区切ることで、複数の定義を連続で書くことができます。さらに、後のCTEは前のCTEを参照できます。これにより、複雑な処理をまるでプログラムのステップのように段階的に実行できます。
WITH S1 AS ( -- ステップ1: 年を切り出す
SELECT
YEAR(OrderDate) AS SalesYear, TotalDue
FROM Sales.SalesOrderHeader
),
S2 AS ( -- ステップ2: S1の結果を使って集計する
SELECT
SalesYear,
COUNT(*) AS SalesCount,
SUM(TotalDue) AS AnnualSales
FROM
S1 -- ★ S1をFROM句で参照！
GROUP BY SalesYear
)
-- 最終ステップ: S2の結果をフィルタリングして表示
SELECT
SalesYear, SalesCount, AnnualSales
FROM
S2 -- ★ S2をFROM句で参照！
WHERE
SalesCount > 5000;
この「処理の段階分け」こそが、CTEがクエリの可読性を高める最大の理由です。複雑なクエリを書く際は、まずCTEを使う習慣をつけましょう！