Emulating Set Operations With Joins
Rewriting EXCEPT and INTERSECT in dialects that lack them.
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;All lessons in this course
- UNION vs UNION ALL
- Column Count and Type Compatibility
- INTERSECT and EXCEPT for Comparison
- Emulating Set Operations With Joins