0Pricing
SQL Interview Prep · Lesson

Emulating Set Operations With Joins

Rewriting EXCEPT and INTERSECT in dialects that lack them.

Emulating Set Operations With Joins is a free SQL Interview Prep lesson on CoddyKit — lesson 4 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Interview Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Why Emulate Set Operations

Not every database supports INTERSECT and EXCEPT. Older MySQL versions, for instance, lacked them entirely. Interviewers test whether you can reproduce set logic with joins and subqueries when the operator is unavailable.

Knowing both the set operator and its join equivalent proves you understand what the operator actually computes.

INTERSECT as an INNER JOIN

INTERSECT finds rows common to both sets. The join equivalent is an INNER JOIN on all the compared columns, plus DISTINCT to match the dedup behavior.

Every column in the comparison becomes part of the join predicate.

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

Why DISTINCT Is Needed for INTERSECT

A plain INNER JOIN can fan out: if a value appears multiple times on either side, the join multiplies rows. Standard INTERSECT returns each common row once, so you add DISTINCT to collapse the duplicates the join introduces.

Forgetting DISTINCT here is a common interview slip.

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

EXCEPT as a LEFT JOIN / IS NULL

EXCEPT (A but not B) is the anti-join. The portable form is a LEFT JOIN from A to B on all columns, keeping only rows where the B side is NULL (no match), then DISTINCT.

This LEFT JOIN / IS NULL pattern is one of the most reused tricks in SQL interviews.

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;

EXCEPT With NOT EXISTS

An equally portable EXCEPT uses NOT EXISTS. It reads as "keep each A row for which no matching B row exists" and handles NULLs robustly.

Many engineers prefer NOT EXISTS because its intent is explicit and it sidesteps the NOT IN + NULL trap.

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
);

INTERSECT With EXISTS

Symmetrically, INTERSECT can be written with EXISTS: keep each distinct A row for which a matching B row exists.

EXISTS short-circuits on the first match, so it can be efficient and it avoids the join fan-out, sometimes removing the need for DISTINCT on the join side.

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

The NOT IN NULL Trap

A tempting EXCEPT emulation is NOT IN, but it is dangerous: if the subquery returns any NULL, NOT IN yields no rows at all because the comparison becomes UNKNOWN.

This is a heavily tested gotcha. Prefer NOT EXISTS or LEFT JOIN / IS NULL, which are NULL-safe.

-- 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
);

Matching on Multiple Columns

When the set comparison spans several columns, every column joins in the predicate. For an anti-join you must also handle the possibility of NULLs in those columns, which is where NOT EXISTS shines.

Spell out each column in the ON clause; missing one quietly changes what "equal row" means.

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;

Emulating UNION Without the Operator

UNION ALL is just concatenation, which every dialect supports directly. To emulate distinct UNION where needed, concatenate with UNION ALL inside a subquery and wrap it with SELECT DISTINCT or GROUP BY all columns.

This shows UNION is simply UNION ALL plus a dedup step.

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

Choosing the Right Emulation

Decision guide:

  • INTERSECT → EXISTS or INNER JOIN + DISTINCT.
  • EXCEPT → NOT EXISTS or LEFT JOIN / IS NULL.
  • Avoid NOT IN when NULLs are possible.
  • UNION → UNION ALL wrapped in DISTINCT.

EXISTS / NOT EXISTS are the most portable and NULL-safe, making them the safest interview answers.

Tying It Together

Being able to translate set operators into joins demonstrates that you understand them as set logic, not just syntax. The anti-join (LEFT JOIN / IS NULL or NOT EXISTS) is the highest-value pattern: it appears in EXCEPT emulation, finding orphans, and missing-record questions alike.

Lead with NOT EXISTS for correctness, then mention the join form for performance discussion.

Quick Check

Your database does not support EXCEPT. You need customer_ids in orders_2023 that are not in orders_2024, and the column can contain NULLs.

Recap

Key takeaways:

  • INTERSECT → INNER JOIN + DISTINCT, or EXISTS.
  • EXCEPT → LEFT JOIN / IS NULL, or NOT EXISTS (anti-join).
  • Add DISTINCT to match the set operators' dedup behavior and tame join fan-out.
  • Avoid NOT IN with possible NULLs; prefer NOT EXISTS.
  • UNION = UNION ALL wrapped in DISTINCT.

Frequently asked questions

Is the “Emulating Set Operations With Joins” lesson free?

Yes — the full text of “Emulating Set Operations With Joins” is free to read here on the web, and the SQL Interview Prep course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Interview Prep course, upgrade to CoddyKit PRO.

What will I learn in “Emulating Set Operations With Joins”?

Rewriting EXCEPT and INTERSECT in dialects that lack them. You practise SQL Interview Prep with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Interview Prep?

No prior experience is required. SQL Interview Prep on CoddyKit is structured for beginners through advanced learners; this is — lesson 4 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Emulating Set Operations With Joins” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Interview Prep lesson?

Yes. Every SQL Interview Prep lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. UNION vs UNION ALL
  2. Column Count and Type Compatibility
  3. INTERSECT and EXCEPT for Comparison
  4. Emulating Set Operations With Joins
← Back to SQL Interview Prep