0Pricing
Coding Interview Prep · レッスン

JOINで集合演算を再現する

INTERSECTやEXCEPTがない方言で、それらをJOINに書き換えます。

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

集合演算を再現する理由

すべてのデータベースが INTERSECT と EXCEPT をサポートしているわけではありません。たとえば、古いバージョンのMySQLには、これらがまったくありませんでした。面接官は、演算子を利用できない場合にJOINやサブクエリで集合のロジックを再現できるかを確認します。

集合演算子と、それに相当するJOINの両方を理解していれば、その演算子が実際に何を計算しているかを理解していると示せます。

INTERSECTをINNER JOINで再現する

INTERSECT は、両方の集合に共通する行を見つけます。これに相当するJOINは、比較対象のすべての列を条件にした INNER JOIN に、重複排除の動作を合わせるための DISTINCT を加えたものです。

比較対象となるすべての列を、JOIN条件の一部にします。

-- A INTERSECT B emulated:
SELECT DISTINCT a.customer_id
FROM orders_2023 a
JOIN orders_2024 b
  ON a.customer_id = b.customer_id;

INTERSECTにDISTINCTが必要な理由

単純なINNER JOINでは、行が増殖することがあります。どちらか一方に同じ値が複数回現れると、JOINによって行数が増えるためです。標準のINTERSECTは共通する各行を1回だけ返すため、JOINによって生じた重複をまとめる DISTINCT を追加します。

ここでDISTINCTを忘れるのは、面接でよくあるミスです。

-- without DISTINCT, a customer with 3 orders in each year
-- would appear 9 times from the join

LEFT JOIN / IS NULL としての EXCEPT

EXCEPT（A にはあるが B にはない）はアンチジョインです。移植性の高い形式は、すべての列を条件に A から B への LEFT JOIN を行い、B 側が NULL（一致なし）の行だけを残してから、DISTINCT を適用する方法です。

この LEFT JOIN / IS NULL パターンは、SQL の面接で最も頻繁に使われるテクニックの一つです。

SELECT DISTINCT a.customer_id
FROM orders_2023 a
LEFT JOIN orders_2024 b
  ON a.customer_id = b.customer_id
WHERE b.customer_id IS NULL;

NOT EXISTS を使った EXCEPT

同じく移植性の高い EXCEPT は NOT EXISTS を使って記述できます。これは「一致する B の行が存在しない A の各行を残す」と読め、NULL も堅牢に扱えます。

多くのエンジニアが NOT EXISTS を好むのは、意図が明確で、NOT IN と NULL の落とし穴を回避できるためです。

SELECT DISTINCT a.customer_id
FROM orders_2023 a
WHERE NOT EXISTS (
  SELECT 1 FROM orders_2024 b
  WHERE b.customer_id = a.customer_id
);

EXISTS を使った INTERSECT

同様に、INTERSECT は EXISTS を使って記述できます。一致する B の行が存在する、重複のない A の各行を残します。

EXISTS は最初の一致が見つかると処理を打ち切るため効率的な場合があり、JOIN による行数の増加も避けられます。そのため、JOIN 側で DISTINCT が不要になることもあります。

SELECT DISTINCT a.customer_id
FROM orders_2023 a
WHERE EXISTS (
  SELECT 1 FROM orders_2024 b
  WHERE b.customer_id = a.customer_id
);

NOT IN と NULL の落とし穴

EXCEPT のエミュレーションには NOT IN を使いたくなりますが、危険です。サブクエリが一つでも NULLを返すと、比較結果が UNKNOWN になるため、NOT IN は結果を一行も返しません。

これは面接でよく問われる重要な落とし穴です。NULL を安全に扱える NOT EXISTS または LEFT JOIN / IS NULL を使用してください。

-- RISKY if orders_2024.customer_id can be NULL:
SELECT DISTINCT customer_id FROM orders_2023
WHERE customer_id NOT IN (
  SELECT customer_id FROM orders_2024
);

複数列での一致判定

集合の比較が複数列にまたがる場合は、述語で各列をすべて結合条件に含めます。アンチジョインでは、さらにそれらの列に NULL が含まれる可能性も扱う必要があり、ここで NOT EXISTS が力を発揮します。

ON 句では各列を明示してください。列を一つでも省略すると、「等しい行」の意味が気付かないうちに変わってしまいます。

SELECT DISTINCT a.id, a.city
FROM a
LEFT JOIN b
  ON a.id = b.id AND a.city = b.city
WHERE b.id IS NULL;

演算子を使わずに UNION をエミュレートする

UNION ALL は単なる連結なので、どの方言でも直接サポートされています。必要に応じて重複を除く UNION をエミュレートするには、サブクエリ内で UNION ALL によって連結し、外側で SELECT DISTINCT または全列を対象にした GROUP BY で囲みます。

これは、UNION が単に UNION ALL に重複除去の手順を加えたものだと示しています。

SELECT DISTINCT * FROM (
  SELECT city FROM a
  UNION ALL
  SELECT city FROM b
) combined;

適切なエミュレーションの選択

選択の指針：

  • INTERSECT → EXISTS または INNER JOIN + DISTINCT
  • EXCEPT → NOT EXISTS または LEFT JOIN / IS NULL
  • NULL の可能性がある場合は NOT IN を避ける
  • UNION → DISTINCT で囲んだ UNION ALL

EXISTS / NOT EXISTS は最も移植性が高く、NULL も安全に扱えるため、面接で最も安全な回答です。

総仕上げ

集合演算子を JOIN に変換できることは、単なる構文ではなく集合論理として理解していることの証明になります。アンチジョイン（LEFT JOIN / IS NULL または NOT EXISTS）は最も価値の高いパターンです。EXCEPT のエミュレーション、孤立レコードの検出、欠落レコードに関する問題など、さまざまな場面で登場します。

まず正しさを重視して NOT EXISTS を示し、その後、性能について議論する際に JOIN 形式にも触れてください。

確認問題

使用しているデータベースは EXCEPT をサポートしていません。orders_2023 にあるが orders_2024 にはない customer_ids が必要で、その列には NULL が含まれる可能性があります。

まとめ

重要なポイント：

  • INTERSECT → INNER JOIN + DISTINCT、または EXISTS
  • EXCEPT → LEFT JOIN / IS NULL、または NOT EXISTS（アンチジョイン）
  • 集合演算子の重複除去の動作に合わせ、JOIN による行数の増加を抑えるために DISTINCT を追加する
  • NULL の可能性がある場合は NOT IN を避け、NOT EXISTS を使用する
  • UNION = DISTINCT で囲んだ UNION ALL

よくある質問

「JOINで集合演算を再現する」レッスンは無料ですか？

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

「JOINで集合演算を再現する」で何を学びますか？

INTERSECTやEXCEPTがない方言で、それらを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. UNIONとUNION ALLの違い
  2. 列数とデータ型の互換性
  3. 比較に使うINTERSECTとEXCEPT
  4. JOINで集合演算を再現する
← Coding Interview Prepに戻る