0Pricing
Coding Interview Prep · レッスン

一致する行がない行を検索する（アンチJOIN）

孤立行や不足データを見つけるLEFT JOIN / IS NULLパターンを学びます。

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

アンチジョインの問題

外部結合で最もよく聞かれる質問の1つに、「一度も注文していない顧客を見つけてください」があります。ほかにも、「一度も販売されていない商品を一覧にしてください」や、「一致する顧客がいない注文を見つけてください」などがあります。

これらはすべて、一方のテーブルに存在し、もう一方のテーブルには一致する行がない行という同じ形です。これには、LEFT JOINとIS NULLフィルターで構成するアンチジョインという定番の書き方を使います。

基本的な考え方

まずLEFT JOINを使います。これにより左側のすべての行が残り、左側で一致しない行の右テーブルの列にはNULLが入ります。

つまり、不一致の行とは、右テーブルの列がNULLになっている行そのものです。そこを条件で絞り込めば、一致しない行だけを取り出せます。仕組みはこれだけです。

パターンを組み立てる

これは、注文のない顧客を見つける典型的なアンチジョインです。次の2段階で読みます。LEFT JOINですべての顧客を残し、その後WHERE o.customer_id IS NULLで一致しない顧客だけを残します。

SELECT c.id, c.name
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.id
WHERE o.customer_id IS NULL;
-- only customers with zero orders

動作を順に確認する

Carolに注文がないデータを使って追ってみましょう。

  • LEFT JOINにより、Alice（2行）、Bob（1行）、そして右側の列がNULLになったCarolが生成されます。
  • WHERE o.customer_id IS NULLにより、AliceとBobが除外されます（右側の列に実際の値が入っているためです）。
  • NULLが補われたCarolの行だけが残ります。

フィルターはJOINの後に実行されるため、これらのNULLを見て、孤立した行だけを正確に選択できます。

テストする右側の列を選ぶ

実際に一致する行では正当な理由でNULLになることが決してない右テーブルの列をテストしてください。JOINキーまたは主キーが理想的です。

o.shipped_atのようにNULLを許容する右側の列をテストすると、注文は存在するものの未発送である行まで拾ってしまい、誤った結果になります。o.customer_id（JOINキー）またはo.id（主キー）をテストすれば、NULLが「一致する行がない」ことを保証できます。

-- SAFE: join key / primary key
WHERE o.id IS NULL

-- RISKY: a nullable data column
WHERE o.shipped_at IS NULL  -- catches unshipped too!

アンチジョインとNOT IN

面接官はアンチジョインとNOT INを比較することがあります。一見同じように見えますが、NULLの扱いが異なります。

サブクエリがNULLを1つでも返すと、NOT INは行を1つも返さなくなります。これは有名な、気づきにくいバグです。LEFT JOIN / IS NULLによるアンチジョインなら、この問題は起きません。

-- DANGEROUS if any customer_id is NULL
SELECT id, name FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);

-- SAFE anti-join, same intent
SELECT c.id, c.name FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

アンチジョインとNOT EXISTS

もう1つの同等な書き方は、相関サブクエリを使ったNOT EXISTSです。これもNULLを正しく扱い、多くの場合、同じ程度に高速です。

LEFT JOIN/IS NULL、NOT EXISTS、NOT INの3つはいずれもアンチジョインを表現できます。ただし面接では、NULLに安全なLEFT JOIN/IS NULLまたはNOT EXISTSを優先してください。NOT INの落とし穴に触れると評価されます。

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

よくある間違い

よくある誤りは、一致しない行を探す条件をWHEREではなくON句に置くことです。

... ON o.customer_id = c.id AND o.id IS NULLと書いても、結果は絞り込まれません。一致とみなす条件が変わるだけで、LEFT JOINによってすべての顧客が残ります。IS NULLのテストはJOINの後に適用されるWHEREに置く必要があります。この落とし穴については次のレッスンで詳しく扱います。

孤立した子行を見つける

このパターンは逆方向にも使えます。存在しない顧客を参照している注文（孤立行、データ整合性のチェック）を見つけるには、ordersを保持し、顧客側がNULLかどうかをテストします。

SELECT o.id AS order_id, o.customer_id
FROM orders o
LEFT JOIN customers c
  ON c.id = o.customer_id
WHERE c.id IS NULL;
-- orders pointing to a non-existent customer

孤立した行を数える

求められるのが単なる件数であることもよくあります。「一度も注文していない顧客は何人いますか」という質問です。アンチジョインをラップするか、直接カウントします。

アンチジョインはすでに孤立した行を1件につき1行返すため、ここでは単純なCOUNT(*)で正しく数えられます。一致しない顧客1人につき1行だけ存在するためです。

SELECT COUNT(*) AS never_ordered
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

再利用できるテンプレート

この3行の骨格を覚えておいてください。非常に多くの面接問題を解決できます。

  • FROM keep_table k
  • LEFT JOIN other o ON o.fk = k.id
  • WHERE o.id IS NULL

テーブルとキーを入れ替えれば、売れていない商品、担当者が割り当てられていないチケット、ログイン履歴のないユーザーなど、「一致するYがないX」と表現されるあらゆる対象を見つけられます。

クイックチェック

order_itemsに一度も登場していない商品が必要です。

まとめ

アンチジョインは一致する行がない行を見つけます。LEFT JOINの後にWHERE right_key IS NULLで絞り込みます。

  • NULLを許容するデータ列ではなく、JOINキーまたは主キーをテストします。
  • IS NULLのテストはONではなくWHEREに置きます。
  • NOT EXISTSと同等ですが、NULLによって問題が起きるNOT INよりもこちらを優先します。
  • テーブルの順序を逆にすると、孤立した子行を見つけられます。

1つのテンプレートで、「一致するYがないX」という多くの問題に対応できます。

よくある質問

「一致する行がない行を検索する（アンチJOIN）」レッスンは無料ですか？

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

「一致する行がない行を検索する（アンチJOIN）」で何を学びますか？

孤立行や不足データを見つけるLEFT JOIN / IS NULLパターンを学びます。 ブラウザで直接実行するハンズオンコードでCoding Interview Prepを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

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

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

「一致する行がない行を検索する（アンチJOIN）」レッスンにはどのくらい時間がかかりますか？

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

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

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

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

  1. LEFT JOINと不一致行の保持
  2. RIGHT JOINとFULL OUTER JOINの意味
  3. 一致する行がない行を検索する（アンチJOIN）
  4. 外部JOINでWHEREを使う落とし穴
← Coding Interview Prepに戻る