What are the INTERSECT and EXCEPT Operators in SQL?
Learn what SQL INTERSECT and EXCEPT operators do, how order affects EXCEPT, and when to use them over joins.
Expected Interview Answer
INTERSECT returns only the rows that appear in both of two SELECT result sets, while EXCEPT (also called MINUS in some databases) returns the rows from the first result set that do not appear in the second, and both operators remove duplicates from their output by default.
Like UNION, both operators require the two queries to return the same number of columns with compatible types, and comparison is done row-by-row across the entire row, not just one column. INTERSECT is useful for finding overlap between two sets, such as customers who appear in both a mailing list and a purchase log, while EXCEPT is useful for finding what is missing, such as products listed in a catalog but never sold. Because both operators are order-sensitive for EXCEPT (the first query's rows are what gets filtered), swapping the query order changes the result for EXCEPT but not for INTERSECT.
- INTERSECT finds common rows across two queries
- EXCEPT finds rows unique to the first query
- Both simplify comparisons that would otherwise need complex joins
- Useful for data reconciliation and auditing tasks
AI Mentor Explanation
Think of two touring squads announced for separate series, and a selector wants to know which players are on both lists. INTERSECT is like laying the two squad sheets side by side and circling only the names present on both, giving the overlap. EXCEPT is different: it is like taking squad one and crossing off any name that also appears on squad two, leaving only the players unique to squad one. Reversing which sheet you start from changes the EXCEPT result but not the INTERSECT result.
Step-by-Step Explanation
Step 1
Write two compatible SELECT statements
Ensure both queries return the same number of columns with compatible types.
Step 2
Apply INTERSECT for common rows
Place INTERSECT between the queries to return only rows present in both result sets.
Step 3
Apply EXCEPT for unique rows
Place EXCEPT between the queries to return rows from the first query absent from the second.
Step 4
Mind operator order
Remember EXCEPT is order-sensitive (A EXCEPT B differs from B EXCEPT A), unlike INTERSECT which is symmetric.
What Interviewer Expects
- Correct definition of INTERSECT as common rows between two queries
- Correct definition of EXCEPT/MINUS as rows unique to the first query
- Awareness that EXCEPT is order-dependent while INTERSECT is not
- Knowledge of the same column-compatibility requirement as UNION
Common Mistakes
- Confusing EXCEPT with a simple NOT IN without considering NULLs
- Assuming EXCEPT is symmetric like INTERSECT
- Forgetting the underlying database may call EXCEPT "MINUS" (e.g. Oracle)
- Not realizing both operators deduplicate the output by default
Best Answer (HR Friendly)
“INTERSECT gives me only the rows that show up in both queries, which is great for finding overlap. EXCEPT gives me the rows from the first query that are not in the second, which is great for finding what is missing. The order matters for EXCEPT but not for INTERSECT, so I always double check which query I put first.”
Code Example
-- INTERSECT: customers who are both active and have made a purchase
SELECT customer_id FROM ActiveCustomers
INTERSECT
SELECT customer_id FROM Purchases;
-- EXCEPT: active customers who have never made a purchase
SELECT customer_id FROM ActiveCustomers
EXCEPT
SELECT customer_id FROM Purchases;Follow-up Questions
- How does EXCEPT differ from a LEFT JOIN with a NULL check?
- Why is A EXCEPT B different from B EXCEPT A?
- Does INTERSECT remove duplicates by default?
- What is the equivalent of EXCEPT in Oracle SQL?
MCQ Practice
1. What does INTERSECT return?
INTERSECT returns only the rows that appear in both of the combined result sets.
2. Which statement about EXCEPT is true?
EXCEPT returns rows from the first query that do not appear in the second, and it is order-sensitive.
3. What must be true of the two queries combined with INTERSECT or EXCEPT?
Like UNION, INTERSECT and EXCEPT require matching column counts and compatible types across both queries.
Flash Cards
What does INTERSECT return? — Rows that appear in both combined result sets.
What does EXCEPT return? — Rows from the first query that do not appear in the second.
Is EXCEPT order-sensitive? — Yes, A EXCEPT B can differ from B EXCEPT A; INTERSECT is symmetric.
What is EXCEPT called in Oracle? — MINUS.