SQL Interview Prep · レッスン

外部JOINでWHEREを使う落とし穴

Q: 「外部JOINでWHEREを使う落とし穴」で何を学びますか？

外部JOINした列をWHEREでフィルタリングすると、気付かないうちにINNER JOINになる理由を学びます。 ブラウザで直接実行するハンズオンコードでSQL Interview Prepを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

外部JOINした列をWHEREでフィルタリングすると、気付かないうちにINNER JOINになる理由を学びます。

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

誰もが引っかかる落とし穴

これは、面接官が仕掛ける外部結合のバグとして最もよくあるものです。「2024年の注文とともにすべての顧客を表示してください。2024年に注文がない顧客も含めます」という問題です。

候補者はLEFT JOINを書いた後、WHEREに日付フィルターを追加しがちです。すると、2024年に注文がない顧客がひそかに消えてしまいます。LEFT JOINが静かにINNER JOINへ変わってしまうのです。なぜそうなるかを理解していることは、シニアレベルの実力を示します。

誤ったクエリ

これが誤りの例です。一見もっともらしく見えます。すべての顧客を残し、その注文を結合して、2024年に絞り込んでいます。

しかし、注文がない顧客や、2024年の注文がない顧客は結果から消えてしまいます。顧客を含めるという要件を満たしていません。

-- BUG: drops customers with no 2024 order
SELECT c.name, o.id, o.order_date
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.order_date >= '2024-01-01';

なぜ壊れるのか

処理の順序を思い出してください。まずJOINが実行され、一致しない顧客のすべての注文列にNULLが入った行が生成されます。その後でWHEREが実行されます。

一致しない顧客ではo.order_dateがNULLなので、o.order_date >= '2024-01-01'の評価結果はtrueではなくUNKNOWNになります。WHEREはtrueの行だけを残すため、NULLの行が除外されます。これは、LEFT JOINが残そうとしていた行そのものです。

NULLがフィルターに打ち勝つ

NULLとの比較はすべてUNKNOWNになります。NULL >= '2024-01-01'はUNKNOWN、NULL = 5もUNKNOWNで、NULL <> 5でさえUNKNOWNです。

WHEREはTRUEと評価された行だけを通すため、保持された不一致の行はすべて破棄されます。右テーブルの列に対するたった1つのWHERE述語によって、外部結合の目的全体が失われます。

解決策：ONでフィルターする

フィルターをON句に移します。そこではフィルターが一致条件の一部になり、行が保持される前に適用されます。そのため、一致しない顧客もNULL付きで残ります。

-- CORRECT: filter lives in ON
SELECT c.name, o.id, o.order_date
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.id
  AND o.order_date >= '2024-01-01';
-- customers with no 2024 order: kept, NULL order

ONとWHEREの違いを一言で説明する

面接でそのまま説明できるルールは次のとおりです。

保持する（外側の）テーブルについては、もう一方のテーブルに対する条件をONに置き、保持するテーブル自体に対する条件をWHEREに置きます。

ONは何を一致とみなすかを決めます（JOIN中に実行されます）。
WHEREは最終的な行をフィルターします（JOINの後に実行され、NULLの行を除外します）。

結果を並べて比較する

同じデータでも、条件を置く場所によって結果が変わります。Carolには2024年の注文がないとします。

WHEREにフィルターを置く：Carolが消えます。実質的にはINNER JOINです。
ONにフィルターを置く：Carolが注文列をNULLにした状態で1行表示され、要件を満たします。

この出力の違いこそが、落とし穴の本質です。

-- ON version output
-- Alice | 50 | 2024-03-01
-- Bob   | 20 | 2024-05-02
-- Carol | NULL | NULL   <-- preserved

WHEREが正しい場合

外部結合でWHEREを使うことが常に誤りとは限りません。保持するテーブルをフィルターするのは問題ありません。JOINによるNULLを扱わないためです。

また、前のレッスンのアンチジョインでは、この挙動を意図的に利用するためにWHERE o.id IS NULLを使っています。重要なのは、どちらのケースなのかを見極めることです。

-- Fine: filtering the preserved (left) table
SELECT c.name, o.id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE c.country = 'US';

検出のための判断基準

外部結合を確認するときは、WHERE句の中に非保持側のテーブルに対する述語がないか調べてください（IS NULLによるアンチジョインのテストは除きます）。

o.someColumn = ...や、外側のテーブルに対する範囲条件・等価条件がWHEREにあれば、この落とし穴を疑います。「これでLEFT JOINがINNER JOINに変わっていないか」と考えてください。通常は変わっています。

複数の条件

両方の場所に条件を置くこともできます。右側のテーブルに対する一致条件はONに置き、JOIN後に左側のテーブルを絞り込む本来のフィルターはWHEREに置きます。両者は問題なく共存します。

SELECT c.name, o.id, o.amount
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.id
  AND o.amount > 100          -- match condition
WHERE c.signup_year = 2023;    -- preserved-table filter

声に出して説明する

面接では、修正方法だけでなく仕組みを説明してください。

「JOINが先に実行され、一致しない右側の列にはNULLが入ります。それらの列に対するWHERE述語は、NULLの行ではUNKNOWNと評価されます。WHEREはtrueでない行を除外するため、外部結合がINNER JOINに縮退します。述語をONに置けば一致条件として扱われるため、不一致の行が保持されます。」この説明なら、毎回要点を押さえられます。

クイックチェック

すべての顧客と、その顧客の2024年の注文だけを一覧にする必要があります。2024年に注文がなかった顧客も残します。

まとめ

非保持側テーブルの列をWHEREでフィルターすると、外部結合は静かにINNER JOINへ変わります

外側のテーブルに対する一致条件はONに置きます。
保持するテーブルに対するフィルターはWHEREに置きます。
WHEREのIS NULLは意図的なアンチジョインであり、今回の落とし穴ではありません。
処理の順序を説明して、理解していることを示します。

よくある質問

「外部JOINでWHEREを使う落とし穴」レッスンは無料ですか？

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

「外部JOINでWHEREを使う落とし穴」で何を学びますか？

外部JOINした列をWHEREでフィルタリングすると、気付かないうちにINNER JOINになる理由を学びます。ブラウザで直接実行するハンズオンコードでSQL Interview Prepを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

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

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

「外部JOINでWHEREを使う落とし穴」レッスンにはどのくらい時間がかかりますか？

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

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

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

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

← SQL Interview Prepに戻る