Detailed explanation
INTERSECT and EXCEPT are SQL set operators that combine the results of two SELECT statements by comparing complete result rows. INTERSECT returns rows found in both results, while EXCEPT returns rows found in the first result but absent from the second. Oracle traditionally uses MINUS for this difference operation; newer releases also support EXCEPT. These operators are useful for identifying overlaps, checking eligibility, reconciling datasets, and finding missing records without writing an explicit join.
For example, SELECT id FROM active_users INTERSECT SELECT id FROM paying_users returns IDs that appear in both tables. SELECT id FROM all_users EXCEPT SELECT id FROM banned_users returns IDs from all_users that do not appear in banned_users. Direction matters for EXCEPT: reversing its inputs answers a different question. INTERSECT is symmetric for compatible inputs. Both queries must return the same number of columns, with compatible data types in corresponding positions. If you select id and status, SQL compares the entire pair, not just id.
By default, both operators remove duplicate rows. If an ID occurs several times in either input, it appears at most once in the result. Unlike ordinary equality comparisons, set operations treat NULL values as equal when identifying duplicate or matching rows. Therefore, a NULL returned by both inputs can appear in an INTERSECT result, and a NULL in the second input removes a matching NULL from an EXCEPT result. Some databases support INTERSECT ALL and EXCEPT ALL, which preserve multiplicities according to counts rather than simply returning distinct rows; availability depends on the database and version.
Under the hood, the optimizer may use sorting, hashing, or other execution strategies to compare rows and eliminate duplicates. Large inputs can require substantial memory or temporary storage, so inspect execution plans rather than assuming a set operator is always faster than a join. Interviewers usually check your understanding of direction, whole-row matching, duplicates, NULL handling, and database support. Results have no guaranteed order unless you apply a final ORDER BY. When combining several set operators, use parentheses where supported to make the intended grouping explicit.
Key points to remember
- INTERSECT returns distinct rows shared by both query results.
- EXCEPT, traditionally MINUS in Oracle, returns distinct rows from the first result that are absent from the second.
- Inputs need equal column counts and compatible types in corresponding positions.
- Matching compares complete selected rows and treats corresponding NULL values as equal.
- ALL variants retain count-based multiplicities where supported; final ORDER BY is needed for guaranteed ordering.
Common mistakes candidates make
- Reversing EXCEPT inputs and assuming the result stays the same.
- Assuming only the ID column is compared when additional columns are selected.
- Replacing EXCEPT with NOT IN without accounting for NULL behaviour and duplicate elimination.
Likely follow-up questions
How does INTERSECT differ from INNER JOIN?
INTERSECT compares complete selected rows and returns distinct shared rows by default. INNER JOIN matches rows using a join condition, can return columns from both inputs, and can multiply rows when several matches exist.
Can NOT EXISTS replace EXCEPT?
Often, but matching EXCEPT semantics requires comparing every selected column, handling NULLs as equal, and eliminating duplicates with DISTINCT. A straightforward NOT EXISTS query using ordinary equality does not automatically provide those semantics.
How do INTERSECT ALL and EXCEPT ALL handle duplicates?
If a row appears m times in the first input and n times in the second, INTERSECT ALL returns it min(m, n) times. EXCEPT ALL returns it max(m − n, 0) times; check whether your database version supports these variants.
Interview tip: Explain intersection versus directional difference, walk through the two ID examples, and then mention duplicates, compatible columns, and NULL handling.