最初のCTEを書く
基本的なWITH構文と、サブクエリよりCTEのほうが読みやすくなる場合を学びます。
「最初のCTEを書く」はCoddyKit上の無料SQL Interview Prepレッスンです。 これはレッスン1/4です。 下記で完全なレッスンを無料で読むことができます。その後、ブラウザ内の組み込みコードエディタと24時間対応のAIチューターでハンズオン演習できます。 これはSQL Interview Prep学習パスの一部であり、ウェブとCoddyKitアプリ全体で進捗が同期されます。 SQL Interview Prepコースには全4レッスンが含まれています。
CTEとは実際には何か
共通テーブル式（CTE）とは、WITHキーワードで定義する名前付きの一時的な結果セットで、1つのクエリの実行中だけ存在します。面接官がCTEを好んで質問するのは、ロジックを明確に構成できるかどうかが分かるためです。
CTEは、サブクエリに名前を付け、後続のメインステートメントでテーブルのように参照できるものだと考えてください。永続的なオブジェクトは作成せず、クエリが終了した時点で消えます。
基本的なWITH構文
すべてのCTEは、WITH、名前、キーワードAS、そして括弧で囲んだクエリから始まります。閉じ括弧の後に、CTEを名前で使用する通常のステートメントを書きます。
WITH cte_name AS ( ... )でブロックを定義します。- 括弧の直後にあるクエリがメインクエリです。
- CTE名は、SELECT元にできるテーブルのように扱われます。
WITH recent_orders AS (
SELECT *
FROM orders
WHERE order_date >= '2024-01-01'
)
SELECT *
FROM recent_orders;サブクエリだけではいけない理由
同じロジックは、FROM句内のインラインサブクエリとしても書けます。それでは、なぜ面接官はCTEについて質問するのでしょうか。
- 可読性：名前付きの手順は、レシピのように上から下へ読めます。
- 再利用性：サブクエリを繰り返す代わりに、同じCTEを複数回参照できます。
- デバッグのしやすさ：CTEだけをSELECTして内容を確認できます。
面接での適切な答えは通常、クエリを読みやすく保守しやすくする場合にCTEを使う、というものです。
実践例：フィルタリングしてから集約する
問題が、今年注文された注文の合計売上を求めてくださいだとします。CTEを使うと、フィルタリングの手順を切り出し、その名前付きの結果に対して集約できます。
メインクエリではrecent_ordersを実際のテーブルであるかのように扱えるため、集約処理がすっきりして分かりやすくなります。
WITH recent_orders AS (
SELECT amount
FROM orders
WHERE order_date >= '2024-01-01'
)
SELECT SUM(amount) AS total_revenue
FROM recent_orders;出力列に名前を付ける
CTE名の直後に列を列挙すると、CTEが公開する列の名前を変更できます。内部クエリが式を生成する場合や、後続処理で分かりやすい名前を使いたい場合に便利です。
列リストを指定する場合、その数は内部クエリが返す列数と一致しなければなりません。一致しないと、データベースがエラーを発生させます。
WITH revenue (region, total) AS (
SELECT region, SUM(amount)
FROM orders
GROUP BY region
)
SELECT region, total
FROM revenue
ORDER BY total DESC;CTEは名前付きクエリにすぎない
面接官に好印象を与える考え方の1つは、CTEは論理的には定義をインラインに置き換えたものと同等だと理解することです。データベースはCTEをインライン化することもマテリアライズすることもできますが、意味的にはサブクエリをそのまま貼り付けた場合と同じ結果になります。
つまり、通常のSELECTで使用できるものはすべてCTE内でも使用できます。JOIN、GROUP BY、WHERE、ウィンドウ関数などが該当します。
詳しい例：JOINを使うCTE
JOINの片側をあらかじめ整形したい場合に、CTEの利点が発揮されます。ここではまず顧客ごとの注文数を作成し、それを顧客テーブルに結合し直して、各顧客行に合計値を持たせます。
メインクエリがほぼ英語の文章のように読める点に注目してください。顧客から、その注文数を結合するという流れです。
WITH order_counts AS (
SELECT customer_id, COUNT(*) AS num_orders
FROM orders
GROUP BY customer_id
)
SELECT c.name, oc.num_orders
FROM customers c
JOIN order_counts oc
ON oc.customer_id = c.id;クエリ内でのCTEの位置
WITHブロックは、それを使用するステートメントの前に置く必要があります。CTEが参照できるのは、定義の直後に続く1つのステートメント内だけです。
- 後続の別のクエリでCTEを参照することはできません。
- SELECT用に定義したCTEを、その後に実行する別のSELECTで再利用することはできません。
- スコープは、ステートメントを終了させるセミコロンで終わります。
CTEはINSERT、UPDATE、DELETEでも使える
よくある追加質問として、CTEはSELECTだけに限定されないという点があります。最新のデータベースの多くでは、データ変更ステートメントにもWITH句を付けられます。
これにより、行の集合を一度計算してから処理できるため、WHERE句内にネストしたサブクエリを書くよりも、はるかに明確になります。
WITH stale AS (
SELECT id
FROM sessions
WHERE last_seen < NOW() - INTERVAL '30 days'
)
DELETE FROM sessions
WHERE id IN (SELECT id FROM stale);初心者によくある間違い
面接官は次のようなミスに注目します。
- CTEの後にメインクエリを書くのを忘れること。
WITHブロックだけでは完全なステートメントになりません。 - CTEとメインクエリの間にセミコロンを置くこと。
- CTEが複数のステートメントにわたって保持されると考えること。
- 省略可能な列名リストと内部クエリの列数を一致させないこと。
面接でCTEについて説明する方法
複雑なサブクエリのリファクタリングを求められたら、考え方を説明してください。このフィルタリング用サブクエリをrecent_ordersというCTEに切り出して、集約処理を読みやすくします。
CTEを盲目的に使うのではなく、可読性と再利用性のために選んでいることを示すと、中級レベルの成熟度を伝えられます。CTE自体がクエリを高速化するわけではなく、主な価値は可読性にあることにも触れてください。
クイックチェック
基本的なCTEの構文とスコープを理解できているか確認しましょう。
最初のCTEを振り返る
WITH name AS ( ... )を使って一時的な結果セットに名前を付け、その後の文ではテーブルと同じように参照する方法を学びました。
- CTEを使うと、インラインサブクエリよりも可読性、再利用性、デバッグのしやすさが向上します。
- スコープは1つの文に限られ、その後は消えます。
- SELECTだけでなく、INSERT/UPDATE/DELETEでも使用できます。
- CTEにパフォーマンスを向上させる効果が本質的に備わっているわけではありません。
次は、複数のCTEをパイプラインとして連結します。
よくある質問
「最初のCTEを書く」レッスンは無料ですか？
はい。「最初のCTEを書く」の完全なテキストはこのウェブで無料で読めます。インタラクティブに演習し（組み込みコードエディタと24時間対応のAIチューター）、SQL Interview Prepコースの残りをアンロックするには、CoddyKit PROにアップグレードしてください。 SQL Interview Prepコースには全4レッスンが含まれています。
「最初のCTEを書く」で何を学びますか？
基本的なWITH構文と、サブクエリよりCTEのほうが読みやすくなる場合を学びます。 ブラウザで直接実行するハンズオンコードでSQL Interview Prepを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。
SQL Interview Prepを始めるのに経験は必要ですか？
事前経験は必要ありません。CoddyKitのSQL Interview Prepは初級者から上級者向けに構成されているため、ここから始めるか最初から始めて、自分のペースで進むことができます。 これはレッスン1/4です。
「最初のCTEを書く」レッスンにはどのくらい時間がかかりますか？
ほとんどのCoddyKitレッスンは約5～10分かかります。各レッスンはコンパクトでインタラクティブなので、着実に進歩し、ウェブとアプリ全体で正確に前回の場所から再開できます。
このSQL Interview Prepレッスンでコードを書いて実行できますか？
はい。すべてのSQL Interview Prepレッスンに組み込みコードエディタが含まれているため、ブラウザでリアルコードを書いて実行し、即座のAIフィードバックを取得できます。ローカル設定は不要です。