CTEとの出会い: WITH

プログラミングでは、コードの一部を切り出して名前をつけて関数を作ることができるよね。CTEも同じ感じ。SELECTサブクエリをメインクエリから切り離して名前をつけて、あとでその名前でSQLクエリの中で使えるんだ。

CTE（Common Table Expressions、共通テーブル式）は、ネストされたクエリに疲れた開発者にとって、まるで新鮮な空気みたいな存在。SQLコードがわかりやすくなるだけじゃなくて、めっちゃエレガントになる。もし今まで複雑なサブクエリに悩まされて目がチカチカしてたなら、そろそろCTEの“魔法”を知るタイミングだよ。

家を建てるときのことを想像してみて。普通は早く窓をつけたりドアをつけたり（つまりサブクエリをどんどん書いちゃう）したくなるけど、壁がまだできてないかもしれない。でもCTEなら違う。まずはきれいな下書きを作る——一時的なテーブルを作る感じで、家の間取りを考えるみたいなもの。そのあとで、クエリの階層を一歩ずつ積み上げていく。スタイリッシュで、信頼できて、テクニカル。

要するに、CTEはSELECTクエリでその場で作る仮想テーブルみたいなもの。サブクエリに似てるけど、もっとカッコいい。 プログラミングでロジックを関数に切り出してわかりやすい名前をつけるみたいに、SQLではCTEがその役割を担う。SELECTを書いて名前をつけて——それを大きくて複雑なクエリの一部として使える。カッコいいでしょ？もちろん！

サブクエリを使ったSQLクエリの例：

-- メインクエリ
SELECT *
FROM (
    SELECT *
    FROM students
    WHERE grade > 75
) AS filtered_students; -- filtered_studentsという別名をつけたサブクエリ

サブクエリを切り出してみた：

-- CTE/filtered_studentsという別名をつけたサブクエリ
WITH filtered_students AS (
    SELECT *
    FROM students
    WHERE grade > 75
)

-- メインクエリ
SELECT *
FROM filtered_students;

びっくりだけど、サブクエリはCTEより20年も前に登場してる！SQL-89標準にはすでにサブクエリがあったけど、CTEはSQL-2009標準でやっと登場したんだ。

WITHの構文

CTEはキーワードWITHから始まって、だいたいこんな感じ：

WITH cte_name AS (
    SELECT ... -- ここにクエリを書く
)

SELECT ...
FROM cte_name;

ここで：

• cte_name — これはCTEの名前。好きな意味のある名前をつけてOK。例えばhigh_scores、filtered_data、best_studentsとか。
• カッコ()の中に、あとで使うためのデータを準備するクエリを書く。
• CTEを定義した後は、メインクエリの中で普通のテーブルみたいに使える。

例1: シンプルなCTE

実際の例でCTEがどう動くか見てみよう。例えば、studentsというテーブルがあって、学生とその成績が入ってるとする：

目標は——成績が85より高い学生を全部選んで、そのデータを表示すること。

CTEなしバージョン：

サブクエリでやるとこうなる：

SELECT *
FROM (
    SELECT *
    FROM students
    WHERE grade > 85
) AS filtered_students;

CTEを使うと、もっと見やすくなる：

WITH filtered_students AS (
    SELECT *
    FROM students
    WHERE grade > 85
)
SELECT *
FROM filtered_students;

どう？スッキリしててわかりやすいよね。データの準備（WITH）とメインのクエリ（SELECT）をちゃんと分けてる。机の上を片付けてから作業を始めるみたいな感じで、すぐにスッキリする。

例2: 複数のCTE

1つのクエリの中で複数のCTEを定義することもできる。段階的にデータを準備したいときに特に便利。

例：gradesというテーブルがあって、学生ごとのコースの成績が入ってる：

やること：各学生の平均点を出して、その平均点が85より高い人だけ選ぶ。

複数CTEを使った解決法：

WITH student_averages AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
),
high_achievers AS (
    SELECT student_id, avg_grade
    FROM student_averages 	-- 1つ目のCTE student_averagesを使う
    WHERE avg_grade > 85
)

SELECT *
FROM high_achievers; -- 2つ目のCTE high_achieversを使う

ここで：

1. student_averagesは学生ごとの平均点を準備する。
2. high_achieversはその中から平均点が85より高い人だけ選ぶ。

CTEとサブクエリの違い

ネタバレ：CTEはサブクエリの代わりじゃないけど、場合によってはめっちゃ便利。

サブクエリはクエリの中のクエリ。サクッと結果が欲しいときは便利だけど、数が増えるとコードがカオスになる。

例：

SELECT *
FROM (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
) AS student_averages
WHERE avg_grade > 85;

サブクエリはSELECTの中、FROMの中、WHEREやHAVINGの中にも書ける。しかも外側のクエリのカラムを参照できる場合もある。でもCTEはそれがちょっと苦手。

一方でCTEを使うと、コードがめっちゃ読みやすくなる。だからメンテしやすいし、バグも減る。ネストしまくるより、サブクエリの結果に名前をつけて再利用できるのがCTEのいいところ。

WITH student_averages AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
)

SELECT *
FROM student_averages
WHERE avg_grade > 85;

CTEは、同じ準備データを1つのクエリで何回も使いたいときに特に便利。

CTEを使うタイミングは？

• 複雑なクエリをいくつかの論理的なステップに分けたいとき。
• クエリを読みやすく、メンテしやすくしたいとき。誰もスパゲッティみたいなネストだらけのコードを読みたくないよね。
• そのクエリだけで使う一時的なデータを準備したいとき。

ラスト例：コース分析

今まで学んだことを全部まとめてみよう：

1. 平均点が高い学生を探す。
2. その学生の名前と履修してるコースを表示する。

WITH student_averages AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
),
high_achievers AS (
    SELECT student_id
    FROM student_averages
    WHERE avg_grade > 85
),
student_courses AS (
    SELECT e.student_id, c.course_name
    FROM enrollments e
    JOIN courses c ON e.course_id = c.course_id
)

SELECT ha.student_id, sc.course_name
FROM high_achievers ha
JOIN student_courses sc ON ha.student_id = sc.student_id;

見てみて、全部がちゃんと構造化されてる：

1. まず平均点を準備。
2. 次に優秀な学生だけ選ぶ。
3. 最後にその学生とコースを紐付ける。

これで、CTEを使ってキレイで読みやすくてパワフルなSQLクエリが書ける準備はバッチリ！

さあ、自分のプロジェクトでどんどん使ってみよう！