Coding Interview Prep · レッスン

相関サブクエリの構造

Q: 「相関サブクエリの構造」レッスンは無料ですか？

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

Q: 「相関サブクエリの構造」で何を学びますか？

内部クエリが外部の行を参照する仕組みと、行ごとの実行モデルを学びます。 ブラウザで直接実行するハンズオンコードでCoding Interview Prepを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

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

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

Q: 「相関サブクエリの構造」レッスンにはどのくらい時間がかかりますか？

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

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

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

内部クエリが外部の行を参照する仕組みと、行ごとの実行モデルを学びます。

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

サブクエリが相関する条件

面接官はサブクエリを2種類に分けます。通常の（非相関）サブクエリは単独で実行できます。一方、相関サブクエリは外側のクエリの列を参照するため、単独では実行できません。

非相関: 1回評価され、結果が外側の各行で再利用されます。
相関: 外側の行に依存するため、外側の各行につき1回再評価されます。

見分けるポイントは、外側のテーブルの列が内部クエリ内に現れることです。これを見つければ、すぐにこのパターンだと判断できます。

行ごとの実行モデル

エンジンが外側の行を順番に処理しているところを想像してください。外側の各行について、その行の値を内側のクエリに組み込み、クエリを実行して、その結果を使って何かを判定したり計算したりします。

面接官が説明してほしいのは、まさにこのメンタルモデルです。「内側のクエリは外側の行ごとに1回実行される」ということです。

この表現から、定番の追加質問も予想できます。相関サブクエリは、内側のクエリが何千回も実行される可能性があるため、遅くなることがあります。これについてはレッスン4で解決します。

外側の参照を見抜く

ここでは employees と給与を扱う外側のエイリアス e1 が、e1.dept_id を読み取る内側のクエリを動かしています。外側の行へのこの参照が相関です。

エイリアスの接頭辞を取り除くと、内側のクエリは単独ではコンパイルできなくなります。この依存関係こそが、相関サブクエリにしているものです。

SELECT e1.name, e1.salary
FROM employees e1
WHERE e1.salary > (
    SELECT AVG(e2.salary)
    FROM employees e2
    WHERE e2.dept_id = e1.dept_id
);

そのクエリを声に出して読む

前のクエリを、面接で説明するように平易な言葉に置き換えてみてください。

「各従業員 e1 について、その従業員自身の部署の平均給与を求め、その部署の平均を上回る給与を得ている従業員だけを残します。」

内側のクエリにある WHERE e2.dept_id = e1.dept_id が、平均をこの従業員の部署に結び付けています。この行がなければ、全員を会社全体の平均と比較することになります。

エイリアスは必須

内側と外側のクエリが同じテーブルにアクセスする場合、どの行に属する列なのかをエンジンが判別できるように、両方にエイリアスを付ける必要があります。

e1 = 判定対象の外側の行です。
e2 = テーブルに対する内側のスキャンです。

エイリアスを省略すると、dept_id は曖昧になります。その結果、多くのエンジンはそれを暗黙に内側のテーブルへ結び付けてしまい、相関が壊れます。面接官はまさにこのミスを仕掛けてきます。

SELECT内の相関サブクエリ

相関サブクエリは WHERE に限られません。SELECT リスト内では、外側の各行について評価される計算列を生成できます。

次の例では、各注文について、同じ顧客が他に何件の注文をしたかを表示します。内側のカウントは o.customer_id を通じて相関しています。

SELECT o.order_id,
       o.customer_id,
       (SELECT COUNT(*)
        FROM orders o2
        WHERE o2.customer_id = o.customer_id) AS customer_order_count
FROM orders o;

スカラーとは値がちょうど1つであること

SELECT で使う相関サブクエリや、=、>、< と比較する相関サブクエリは、外側の各行について単一のスカラー値を返す必要があります。

複数の行を返すと、データベースは 「サブクエリが複数の行を返しました」 などのエラーを発生させます。

COUNT、MAX、AVG などの集約関数は1つの値を保証するため、スカラーの相関サブクエリ内でよく使われます。このルールを知っていれば、実行時に起きやすい予想外のエラーを防げます。

サブクエリがNULLを返す場合

スカラーの相関サブクエリは、内側の行に1行も一致しないことがあります。その場合、集約関数は NULL を返します（ただし COUNT は 0 を返します）。

この NULL は外側の式にそのまま流れ込みます。NULL との比較結果は UNKNOWN になるため、外側の行が暗黙に除外されることがあります。

代替値が必要な場合は、サブクエリを COALESCE で囲んでください。面接官は、内側の行が一致しない場合に何が起きるかをよく質問します。その際は NULL の動作に触れることが期待されています。

SELECT c.customer_id,
       COALESCE((SELECT MAX(o.amount)
                 FROM orders o
                 WHERE o.customer_id = c.customer_id), 0) AS biggest_order
FROM customers c;

実例：最新の注文日

よくある課題は、各顧客とその顧客の最新の注文日を表示することです。SELECT 内の相関サブクエリを使えば、直接実現できます。

顧客の各行について、内側のクエリが o.customer_id = c.customer_id を使い、その顧客の注文日の MAX を求めます。

SELECT c.customer_id,
       c.name,
       (SELECT MAX(o.order_date)
        FROM orders o
        WHERE o.customer_id = c.customer_id) AS last_order_date
FROM customers c;

遅くなる理由

内側のクエリは外側の行ごとに1回実行されるため、大きな外側のテーブルに対する相関サブクエリでは、内側の処理が何百万回も実行される可能性があります。

相関に使う列（ここでは orders.customer_id）にインデックスがあれば、各回の内側の処理をすばやく完了できます。
インデックスがない場合、毎回テーブル全体をスキャンする可能性があり、処理量はおおよそ O(n*m) になります。

面接では、性能改善の手段としてインデックスと JOIN への書き換えに必ず触れてください。

相関ありと相関なしの比較

違いは1行だけです。相関なしのバージョンは全員を会社全体の平均と比較し、相関ありのバージョンは各人を自分の部署の平均と比較します。

両方を読み、WHERE e2.dept_id = e1.dept_id という1行が意味全体をどのように変えているかに注目してください。

-- Uncorrelated: one global average, computed once
SELECT name FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- Correlated: per-department average, recomputed per row
SELECT e1.name FROM employees e1
WHERE e1.salary > (
    SELECT AVG(e2.salary) FROM employees e2
    WHERE e2.dept_id = e1.dept_id
);

理解度チェック

相関サブクエリを定義する要素を理解できているか確認しましょう。

まとめ：相関サブクエリの構造

重要なポイント：

相関サブクエリは外側の行を参照し、外側の行ごとに1回実行されます。
同じテーブルを使う場合は、相関を曖昧にしないために両方のテーブルにエイリアスを付けます。
スカラーとして使う場合は値を必ず1つ返す必要があります。一致が0件の場合は NULL になるため、COALESCE で対処します。
SELECT にも WHERE にも置くことができ、性能は相関に使う列へのインデックスに大きく左右されます。

面接で「外側の行ごとに1回実行される」と言えれば、核心となる概念を押さえたことになります。

よくある質問