SQL Interview Prep · レッスン

CTE、サブクエリ、一時テーブルの比較

Q: 「CTE、サブクエリ、一時テーブルの比較」で何を学びますか？

マテリアライズ、再利用、オプティマイザーの挙動に関するトレードオフを学びます。 ブラウザで直接実行するハンズオンコードでSQL Interview Prepを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

マテリアライズ、再利用、オプティマイザーの挙動に関するトレードオフを学びます。

「CTE、サブクエリ、一時テーブルの比較」はCoddyKit上の無料SQL Interview Prepレッスンです。これはレッスン3/4です。下記で完全なレッスンを無料で読むことができます。その後、ブラウザ内の組み込みコードエディタと24時間対応のAIチューターでハンズオン演習できます。これはSQL Interview Prep学習パスの一部であり、ウェブとCoddyKitアプリ全体で進捗が同期されます。 SQL Interview Prepコースには全4レッスンが含まれています。

ロジックを組み立てる3つの方法

クエリで中間結果が必要な場合、一般的な手段はサブクエリ、CTE、一時テーブルの3つです。面接官がこれらの比較を求めるのは、選択からマテリアライズとオプティマイザの動作を理解しているかどうかがわかるためです。

このレッスンでは、プレッシャーのかかる場でも説明できる判断の枠組みを身につけます。

サブクエリ

サブクエリとは、別のクエリの中にネストされたインラインクエリで、多くの場合、FROM、WHERE、またはSELECTの中に記述します。同じ文の一部であり、オプティマイザからは1つの単位として扱われます。

名前は必要ありません（ただし派生テーブルにはエイリアスが必要です）。
オプティマイザは外側のクエリに自由に統合できます。
深くネストすると冗長になり、読みにくくなります。

SELECT *
FROM (
    SELECT customer_id, SUM(amount) AS total
    FROM orders
    GROUP BY customer_id
) t
WHERE t.total > 1000;

CTE

CTEは、WITHブロック内に記述する名前付きサブクエリで、1つの文にスコープが限定されます。深くネストしたサブクエリより読みやすく、複数回参照できます。

名前が付くため、意図が明確に記録されます。
同じ文の中で複数回参照できます。
それでもスコープは1つの文に限られ、その後は消えます。

WITH spend AS (
    SELECT customer_id, SUM(amount) AS total
    FROM orders
    GROUP BY customer_id
)
SELECT *
FROM spend
WHERE total > 1000;

一時テーブル

一時テーブルは、セッション（またはトランザクション）の間存在する、実体のある物理テーブルです。ある文でデータを投入し、後続の別の文でクエリできます。

セッション中は複数の文にまたがって保持されます。
インデックスを作成でき、統計情報を収集できます。
ディスクI/Oが発生し、明示的なクリーンアップも必要です。

CREATE TEMP TABLE spend AS
SELECT customer_id, SUM(amount) AS total
FROM orders
GROUP BY customer_id;

SELECT * FROM spend WHERE total > 1000;

マテリアライズ：核心となる違い

面接官が確認する重要な概念はマテリアライズです。これは、中間結果が物理的にどこかへ書き込まれるかどうかを意味します。

サブクエリとCTEは通常マテリアライズされず、オプティマイザによってインライン化されることがよくあります。
一時テーブルは必ずストレージにマテリアライズされます。
データベースによっては、ヒントを使ってCTEのマテリアライズを強制または禁止できます。

最適化フェンスとPostgresの旧来の落とし穴

歴史的に、PostgreSQLはすべてのCTEを最適化フェンスとして扱い、マテリアライズして述語のプッシュダウンを妨げていました。Postgres 12以降では、1回だけ参照される単純な非再帰CTEはデフォルトでインライン化されます。MATERIALIZEDとNOT MATERIALIZEDのヒントでこの動作を上書きできます。

この微妙な点に触れると、シニアレベルの強いアピールになります。

WITH spend AS NOT MATERIALIZED (
    SELECT customer_id, SUM(amount) AS total
    FROM orders GROUP BY customer_id
)
SELECT * FROM spend WHERE total > 1000;

1つの文の中での再利用

同じ中間結果を1つの文の中で何度も参照する場合、サブクエリを繰り返すよりもCTEを使ったほうがすっきりすることがあります。ただし、インライン化されたCTEは参照するたびに再計算される可能性があるため注意が必要です。

再計算にコストがかかる場合は、マテリアライズを強制するか、一時テーブルを使うことで同じ処理を2回行わずに済みます。

文をまたいだ再利用

CTEとサブクエリの有効期間は1つの文だけです。同じ結果を複数の別々のクエリで使う必要がある場合は、一時テーブルが適しています。

典型的な例は、複数段階のETLやレポートです。ステージング用の集合を一度構築し、それに対して複数の分析を実行します。一時テーブルにインデックスを作成すれば、その後のすべてのクエリを高速化できます。

インデックスと統計情報

インデックスを保持し、新しい統計情報を持てるのは一時テーブルだけです。何度も結合する巨大な中間結果では、これが決定的な違いになることがあります。

CTE/サブクエリ：オプティマイザは基になるテーブルから見積もります。
一時テーブル：ANALYZEを実行し、後続の結合に合わせてインデックスを追加できます。

そのため、大規模で頻繁に再利用する結果では、追加の手順があっても一時テーブルのほうがパフォーマンスで勝る場合があります。

判断の枠組み

面接での簡潔な回答は次のとおりです。

サブクエリ：1回限りの浅い処理で、可読性に問題がない場合。
CTE：可読性を高めたい場合、または1つの文の中で数回参照する場合。
一時テーブル：複数の文で再利用する場合、非常に大規模な場合、またはインデックスや統計情報が必要な場合。

明確さを優先してまずCTEを選び、マテリアライズや文をまたいだ再利用が本当に役立つ場合に一時テーブルを使います。

トレードオフの伝え方

「CTEは常に遅い」のような断定は避けてください。代わりに、次のように説明します。CTEとサブクエリは通常インライン化されるため、主に可読性の問題です。一時テーブルはマテリアライズされるので、文をまたいで大きな結果を再利用する場合やインデックスが必要な場合に価値があります。

その動作がエンジン固有であり、Postgresではバージョンによっても異なることを認めると、深い理解を示せます。

クイックチェック

一時テーブルを選ぶほうが明らかに適している状況を選んでください。

CTE、サブクエリ、一時テーブルを振り返る

選択のポイントはマテリアライズとスコープです。

サブクエリとCTE：通常はインライン化され、スコープは1つの文に限られ、可読性を理由に選択します。
CTEには、名前付けと文の中での再利用という利点があります。
一時テーブル：必ずマテリアライズされ、複数の文にまたがって保持され、インデックスを作成できます。
Postgres 12以降では単純なCTEがインライン化されます。MATERIALIZEDヒントで制御できます。

次は、複雑にネストしたクエリを整理されたCTEにリファクタリングします。

よくある質問

「CTE、サブクエリ、一時テーブルの比較」レッスンは無料ですか？

はい。「CTE、サブクエリ、一時テーブルの比較」の完全なテキストはこのウェブで無料で読めます。インタラクティブに演習し（組み込みコードエディタと24時間対応のAIチューター）、SQL Interview Prepコースの残りをアンロックするには、CoddyKit PROにアップグレードしてください。 SQL Interview Prepコースには全4レッスンが含まれています。

「CTE、サブクエリ、一時テーブルの比較」で何を学びますか？

マテリアライズ、再利用、オプティマイザーの挙動に関するトレードオフを学びます。ブラウザで直接実行するハンズオンコードでSQL Interview Prepを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

SQL Interview Prepを始めるのに経験は必要ですか？

事前経験は必要ありません。CoddyKitのSQL Interview Prepは初級者から上級者向けに構成されているため、ここから始めるか最初から始めて、自分のペースで進むことができます。これはレッスン3/4です。

「CTE、サブクエリ、一時テーブルの比較」レッスンにはどのくらい時間がかかりますか？

ほとんどのCoddyKitレッスンは約5～10分かかります。各レッスンはコンパクトでインタラクティブなので、着実に進歩し、ウェブとアプリ全体で正確に前回の場所から再開できます。

このSQL Interview Prepレッスンでコードを書いて実行できますか？

はい。すべてのSQL Interview Prepレッスンに組み込みコードエディタが含まれているため、ブラウザでリアルコードを書いて実行し、即座のAIフィードバックを取得できます。ローカル設定は不要です。

このコースのすべてのレッスン

← SQL Interview Prepに戻る