0Pricing
Coding Interview Prep · レッスン

相関サブクエリをJOINに書き換える

パフォーマンス向上のため、相関ロジックをJOINやウィンドウ関数に平坦化します。

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

そもそも書き換える理由

相関サブクエリは読みやすい一方で、遅くなることがあります。内部クエリが外側の各行に対して1回ずつ実行される可能性があるためです。面接では、パフォーマンスを改善するために、それをJOINまたはウィンドウ関数に書き換えるよう求められることがよくあります。

目的は、内部スキャンを繰り返すのではなく、データを1回の走査で処理して同じ結果を得ることです。

2〜3種類の書き換えパターンと、それぞれで正しさを保てる条件を知っていることは、中級レベルの重要なスキルです。

パターン1：EXISTSからINNER JOINへ

少なくとも1件の一致を確認する相関EXISTSは、多くの場合INNER JOINに書き換えられます。

ただし注意が必要です。複数の内部行が一致すると、JOINによって外側の行が重複する可能性があります。外側のキーごとに1行へ戻すには、DISTINCTを追加するか、集約してください。

-- Correlated EXISTS
SELECT c.customer_id, c.name
FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o
             WHERE o.customer_id = c.customer_id);

-- Join rewrite (DISTINCT avoids dupes from fan-out)
SELECT DISTINCT c.customer_id, c.name
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id;

ファンアウトの落とし穴

最も一般的な書き換えのバグは、ファンアウトを忘れることです。EXISTSは注文数にかかわらず各顧客を1回だけ返します。一方、単純なJOINは注文ごとに1行を返すため、件数が膨らみます。

その結合結果に対して、後続の処理でグループ化を適切に行わずにCOUNT(*)やSUM(amount)を実行すると、数値が誤ります。

常に、JOINによって行が増える可能性があるかを確認してください。可能性がある場合は、DISTINCTまたはGROUP BYを使って元に戻します。

パターン2：NOT EXISTSからLEFT JOIN / IS NULLへ

アンチ結合への書き換えは、面接で必ずと言ってよいほど出るパターンです。相関NOT EXISTSは、右側がNULLになるLEFT JOINに書き換えられます。

一致しない外側の行では右側の列がNULLになります。そのNULLで絞り込むと、一致する行がない行だけが残ります。

-- Correlated NOT EXISTS
SELECT c.customer_id FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o
                  WHERE o.customer_id = c.customer_id);

-- LEFT JOIN / IS NULL rewrite
SELECT c.customer_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.customer_id IS NULL;

NULLにならない列をテストする

LEFT JOIN / IS NULLへの書き換えでは、実際に一致した行では決してNULLにならない右側の列をテストしてください。理想的なのは、結合キーまたは主キーです。

NULLを許容する列をテストすると、本当に一致する行がない場合と、一致した行のその列が単にNULLである場合を区別できません。このバグによって誤った行が返されます。

結合キー（ここではo.customer_id）またはo.order_idを使えば、NULLが「一致する行がない」ことを保証できます。

パターン3：スカラー集約からJOIN + GROUP BYへ

SELECT内の相関集約は、グループ化したサブクエリ（派生テーブル）とのJOINに書き換えられます。

グループごとの集約値を一度だけ計算し、それを詳細行に結合し直します。内部クエリが行ごとではなく1回だけ実行されます。

-- Correlated scalar aggregate
SELECT e1.name,
       (SELECT MAX(e2.salary) FROM employees e2
        WHERE e2.dept_id = e1.dept_id) AS dept_max
FROM employees e1;

-- Join + GROUP BY rewrite
SELECT e.name, m.dept_max
FROM employees e
JOIN (SELECT dept_id, MAX(salary) AS dept_max
      FROM employees GROUP BY dept_id) m
  ON m.dept_id = e.dept_id;

パターン4：ウィンドウ関数への書き換え

多くの場合、最もすっきりした書き換えはウィンドウ関数を使う方法です。MAX(salary) OVER (PARTITION BY dept_id)を使えば、相関集約を完全に置き換えられ、JOINも必要ありません。

グループの値を1回の走査で計算し、すべての詳細行を保持できます。分析クエリでは、面接官が最も見たい解答になることが多い方法です。

SELECT name,
       dept_id,
       salary,
       MAX(salary) OVER (PARTITION BY dept_id) AS dept_max
FROM employees;

グループごとの上位N件への書き換え

グループごとに最上位の行を選ぶ相関サブクエリ（salary = MAX per dept）は、ROW_NUMBERを使ってきれいに書き換えられます。

グループでパーティション分割し、指標の順に並べ、順位1だけを残します。同率の最上位行をすべて取得したい場合は、代わりにRANKを使います。

SELECT name, dept_id, salary
FROM (
    SELECT name, dept_id, salary,
           ROW_NUMBER() OVER (PARTITION BY dept_id
                              ORDER BY salary DESC) AS rn
    FROM employees
) t
WHERE rn = 1;

書き換えないほうがよい場合

書き換えれば必ず改善するとは限りません。次の場合は相関サブクエリをそのまま使用してください。

  • 外側の集合が非常に小さく、行ごとのコストが無視できる場合。
  • 相関に使う列に適切なインデックスがあり、オプティマイザーがすでに効率的なセミ結合に変換している場合。
  • 保守するコードでは、わずかな最適化よりも可読性が重要な場合。

最新のオプティマイザーは、EXISTSを自動的にセミ結合へ変換することがよくあります。書き換えが役立つと決めつける前に、EXPLAINで計測すると伝えてください。

同等性の検証

書き換え後は、元のクエリと同じ行および同じカーディナリティが返ることを確認してください。

  • 行数が一致することを確認します。
  • JOINのファンアウトによって重複が生じていないことを確認します。
  • NULLや空のグループに対するエッジケースの動作が変わっていないことを確認します。

簡単な方法は、両方のバージョンを実行し、相互にEXCEPTを適用することです。結果が空なら、両者は一致しています。思い込みではなく検証する姿勢は、面接官から高く評価されます。

SELECT customer_id FROM query_a
EXCEPT
SELECT customer_id FROM query_b;
-- and the reverse; both empty => equivalent

INをJOINに書き換える

相関のないINサブクエリも、多くの場合JOINに書き換えられます。ただし、同じくファンアウトに注意が必要です。INはメンバーシップを重複排除しますが、JOINは重複排除しません。

内部のリストに重複したキーがあると、JOINによって外側の行が繰り返されます。INと同じ意味にするには、内部側または最終結果にDISTINCTを使用してください。

-- IN subquery
SELECT c.name FROM customers c
WHERE c.customer_id IN (SELECT o.customer_id FROM orders o);

-- Join rewrite, de-duplicated to match IN
SELECT DISTINCT c.name
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id;

確認問題

相関NOT EXISTSアンチ結合に対する正しいJOINへの書き換えを選んでください。

まとめ：相関サブクエリをJOINに書き換える

重要なポイント：

  • EXISTS → INNER JOIN（ファンアウトによる重複を避けるため、DISTINCTを追加します）。
  • NOT EXISTS → LEFT JOIN ... WHERE key IS NULL（NULLにならない列をテストします）。
  • 相関スカラー集約 → グループ化した派生テーブルをJOINします。または、よりよい方法としてウィンドウ関数を使います。
  • グループごとの上位行 → ROW_NUMBER（同率を扱う場合はRANK）。
  • 書き換えが高速だと決めつける前に、同等性を検証し、EXPLAINで確認します。

両方の形式とファンアウトの落とし穴を理解しているかどうかは、中級レベルの面接でまさに確認されるポイントです。

よくある質問

「相関サブクエリをJOINに書き換える」レッスンは無料ですか？

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

「相関サブクエリをJOINに書き換える」で何を学びますか？

パフォーマンス向上のため、相関ロジックをJOINやウィンドウ関数に平坦化します。 ブラウザで直接実行するハンズオンコードでCoding Interview Prepを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

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

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

「相関サブクエリをJOINに書き換える」レッスンにはどのくらい時間がかかりますか？

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

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

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

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

  1. 相関サブクエリの構造
  2. GROUP BYなしでグループごとに集計する
  3. 相関EXISTSとNOT EXISTS
  4. 相関サブクエリをJOINに書き換える
← Coding Interview Prepに戻る