SQL Set Operations: UNION, UNION ALL, INTERSECT & EXCEPT

Your college has two lists: students who registered on the website, and students who registered at the physical counter. The placement cell wants one combined list. Notice this is not a join — you're not matching rows side by side, you're stacking two lists on top of each other. That vertical stacking is what set operations do: UNION combines, INTERSECT keeps the common part, EXCEPT subtracts. Venn-diagram thinking, in SQL.

UNION vs UNION ALL — the duplicate question

SELECT email FROM customers
UNION
SELECT email FROM newsletter_subscribers;

a@x.com
b@x.com
c@x.com
d@x.com

Swap in UNION ALL and you get every row from both sides, duplicates included. Two consequences worth internalising: UNION ALL is faster (no sort/hash needed to hunt duplicates), and sometimes duplicates are correct data — two genuine logins by the same user on the same day are two events, and UNION would silently merge them. Default to UNION ALL unless you specifically need de-duplication; that one sentence wins the interview follow-up about performance.

The column rules — same shape on both sides

Set operations stack rows, so both SELECTs must produce the same shape:

• Same number of columns, in the same left-to-right order — a 3-column SELECT cannot UNION a 4-column one.
• Compatible types position by position — INT with DECIMAL is fine; INT with a random VARCHAR is an error (or a nasty implicit conversion).
• Output column names come from the first SELECT — aliases in the second are politely ignored.

INTERSECT — only what's in both

SELECT product_id FROM online_orders
INTERSECT
SELECT product_id FROM store_orders;

2
3

One MySQL history note interviewers love: native INTERSECT arrived only in MySQL 8.0.31. On older versions you emulate it with IN or EXISTS plus an explicit DISTINCT:

SELECT DISTINCT o.product_id
FROM online_orders o
WHERE EXISTS (
    SELECT 1 FROM store_orders s WHERE s.product_id = o.product_id
);

EXCEPT — the first list minus the second

"Customers who ordered in 2025 but not in 2026" — churn analysis in one operator:

SELECT customer_id FROM orders_2025
EXCEPT
SELECT customer_id FROM orders_2026;

Order matters — A EXCEPT B and B EXCEPT A are different questions (lost customers vs new customers). Oracle spells this operator MINUS; same behaviour, different keyword — a near-guaranteed one-mark question. And notice EXCEPT is the third face of a pattern you already own: the anti-join (LEFT JOIN … IS NULL) from lesson 2 and NOT EXISTS from lesson 4 answer the same question with different tools.

Wait — NULLs behave differently here

You know from lesson 1 that NULL = NULL is UNKNOWN, never TRUE. But watch this: UNION two copies of the row (1, NULL) — you get one row back. For the specific purpose of duplicate elimination, set operators treat two NULLs as matching — the same special rule GROUP BY and DISTINCT use. Ordinary comparisons say "unknown"; duplicate-checks say "same". Knowing that inconsistency exists — and where — is a hard-tier interview answer delivered in one sentence.

Mixing operators — who runs first?

-- Without parentheses this means A UNION (B INTERSECT C),
-- because INTERSECT binds tighter than UNION/EXCEPT:
SELECT id FROM a
UNION
SELECT id FROM b
INTERSECT
SELECT id FROM c;

INTERSECT has higher precedence (like AND over OR); UNION and EXCEPT among themselves evaluate left to right. The professional habit: always write the parentheses — your intent should never depend on the reader remembering precedence tables.

Common mistakes

• Using UNION out of habit when UNION ALL is both correct and faster — or worse, letting UNION silently merge genuinely duplicate events.
• Expecting column matching by name — set operators match by position only.
• Putting ORDER BY on each SELECT — only one ORDER BY is allowed, at the very end, sorting the combined result.
• Writing A EXCEPT B when the question asked for B EXCEPT A — read twice, subtract once.
• Mixing UNION and INTERSECT without parentheses and getting a different query than you meant.

Quick recap

Practice Zone — PYQs from real selection rounds

Q1UNION vs UNION ALLeasy

Query A returns 3 rows, query B returns 2 rows, and exactly one row is identical in both. How many rows do UNION and UNION ALL return, respectively?

Asked in

A4 and 5B5 and 5C5 and 4D4 and 4

Q2Column ruleseasy

What happens when you UNION a SELECT returning 3 columns with a SELECT returning 4 columns?

Asked in

AThe extra column is filled with NULLsBIt's an error — column counts must matchCThe extra column is silently droppedDIt works if the 4th column is numeric

Q3INTERSECTmedium

Your MySQL version is older than 8.0.31, so INTERSECT isn't available. What's the standard emulation?

Asked in

AIN / EXISTS against the other query, plus DISTINCTBA CROSS JOIN with a WHERE filterCUNION followed by GROUP BYDIt cannot be emulated

Q4NULLs in set operationshard

Two rows (1, NULL) come from the two sides of a UNION. Does the result contain one row or two?

Asked in

ATwo — NULL never equals NULLBOne — set operators treat NULLs as matching for duplicate checksCZero — rows with NULL are droppedDIt's an error

Q5Operator precedencehard

Without parentheses, how does standard SQL evaluate A UNION B INTERSECT C?

Asked in

A(A UNION B) INTERSECT C — strictly left to rightBA UNION (B INTERSECT C) — INTERSECT binds tighterCIt's ambiguous and rejectedDWhichever produces fewer rows

FAQ

Next lesson: how a table protects itself from bad data — Lesson 7: Constraints & Keys →