EXCEPT

Definition

This topic revisits `EXCEPT` — already introduced conceptually in relational algebra as Difference (7.6) — as concrete SQL syntax: returning rows in the FIRST query but NOT the second. Operand order matters.

Running example — enrolled_2025(student_id) and enrolled_2026(student_id), tracking which students were enrolled each year:

enrolled_2025: 1, 2, 3, 4 enrolled_2026: 2, 3, 5

(Students 2 and 3 stayed enrolled both years; 1 and 4 left after 2025; 5 is a new student in 2026.)

sql
SELECT student_id FROM enrolled_2025
EXCEPT
SELECT student_id FROM enrolled_2026
ORDER BY student_id;

This returns 1 and 4 — students enrolled in 2025 who did NOT return in 2026. Reversing the order (enrolled_2026 EXCEPT enrolled_2025) would instead return just 5 — the new student who wasn't enrolled the year before — a completely different, equally valid question.

Edge Cases and Pitfalls

• As covered in 7.6, Oracle historically spells this operation MINUS instead of EXCEPT — same concept, different keyword, a classic example of dialect fragmentation for an otherwise-standard operation.
• EXCEPT also deduplicates by default, and requires the same union-compatibility as UNION/INTERSECT.
• A very common real use: finding "what's missing" by taking a full reference set EXCEPT an actual/completed set — e.g. all_required_courses EXCEPT completed_courses finds exactly the courses still needed (this exact pattern was introduced in Chapter 7 and connects directly to relational Division, 7.12, for more complex per-group versions of the same question).

Key Takeaways

• EXCEPT returns rows in the first query but not the second — the SQL syntax for relational algebra's Difference (7.6).
• Operand order changes the result entirely — A EXCEPT B is not the same as B EXCEPT A.
• Oracle spells the same operation MINUS — same concept, different vendor keyword.