EXCEPT & INTERSECT

"Who is a pure batter (bats but doesn't bowl)?" and "Who are the all-rounders (in both lists)?" These are difference and overlap questions — exactly what EXCEPT and INTERSECT answer.

Definitions

EXCEPT (called MINUS in Oracle) returns the distinct rows of the first query that do not appear in the second query.

INTERSECT returns the distinct rows that appear in both queries.

• EXCEPT — Venn: the left circle minus the overlap. Order matters: A EXCEPT B ≠ B EXCEPT A.
• INTERSECT — Venn: only the overlap. Order does not matter.
• Both compare whole rows (all selected columns) and both return distinct rows.

Example — EXCEPT: in batters but NOT in bowlers

SELECT name, team FROM batters
EXCEPT
SELECT name, team FROM bowlers;

How it works: take the 5 batters, remove every row that also appears in bowlers (Jadeja, Pandya) → 3 pure batters.

Example — order matters: bowlers EXCEPT batters

SELECT name, team FROM bowlers
EXCEPT
SELECT name, team FROM batters;

Same tables, swapped order, completely different answer: now we get the pure bowlers.

Example — INTERSECT: rows common to both (all-rounders)

SELECT name, team FROM batters
INTERSECT
SELECT name, team FROM bowlers;

Whole-row comparison — a subtle trap

Imagine a bowler row ('Hardik Pandya', 'GT') is added (he played for GT earlier). Then:

Choose your columns carefully: the more columns you select, the stricter the comparison.

SET operators at a glance

EXCEPT / INTERSECT vs JOINs

Set operators are shorter and treat NULLs as equal; joins are more flexible (you can show columns from both tables).

Common mistakes

• ❌ Assuming A EXCEPT B = B EXCEPT A. ✅ EXCEPT is directional; always ask "rows of which query?".
• ❌ Expecting duplicates from EXCEPT / INTERSECT. ✅ They always return distinct rows (SQL Server has no EXCEPT ALL / INTERSECT ALL).
• ❌ Mixing operators without brackets. ✅ In SQL Server INTERSECT is evaluated before UNION and EXCEPT; use parentheses to make your intent clear.

SQL Server vs other databases

SQL Server and PostgreSQL use EXCEPT; Oracle uses MINUS. MySQL supports EXCEPT and INTERSECT only from 8.0.31. PostgreSQL additionally supports EXCEPT ALL / INTERSECT ALL; SQL Server does not.

Interview tip

Q: Find players who are batters but not bowlers — two ways. A: SELECT name FROM batters EXCEPT SELECT name FROM bowlers; or SELECT b.name FROM batters b WHERE NOT EXISTS (SELECT 1 FROM bowlers w WHERE w.name = b.name);