EXCEPT

EXCEPT: rows in the first query but not the second

EXCEPT (called MINUS in Oracle) returns the distinct rows from the first query that are not in the second.

SELECT customer_id FROM AllCustomers
EXCEPT
SELECT customer_id FROM CustomersWhoOrdered;   -- customers who never ordered

Availability: standard/PostgreSQL always; MySQL added EXCEPT in 8.0.31. The robust emulation — and the one to prefer even when EXCEPT exists, because of a NULL trap — is NOT EXISTS:

-- Preferred: NOT EXISTS (NULL-safe)
SELECT a.customer_id FROM AllCustomers a
WHERE NOT EXISTS (SELECT 1 FROM CustomersWhoOrdered b WHERE b.customer_id = a.customer_id);

-- Also works: LEFT JOIN ... IS NULL (the "anti-join")
SELECT a.customer_id FROM AllCustomers a
LEFT JOIN CustomersWhoOrdered b ON b.customer_id = a.customer_id
WHERE b.customer_id IS NULL;

Traced NOT IN disaster vs EXCEPT

Tables:

AllCustomers.id	CustomersWhoOrdered.customer_id
1 Alice	1
2 Bob	NULL
3 Cara	3

Intent: customers who never ordered → expect Bob (2) only.

Approach	What happens to Alice	Bob	Cara	Result
EXCEPT	in ordered set → excluded	not in ordered set → kept	in ordered set → excluded	{2} correct
NOT EXISTS / anti-join	match exists → drop	no match → keep	match exists → drop	{2} correct
WHERE id NOT IN (SELECT customer_id FROM …)	1 NOT IN (1,NULL,3) → FALSE (1=1) → drop	2 NOT IN (1,NULL,3) → UNKNOWN (2≠1, 2=NULL is UNKNOWN, 2≠3) → drop	3 NOT IN (1,NULL,3) → FALSE (3=3) → drop	{} empty — Bob wrongly excluded

Why: once the subquery set contains a NULL, x NOT IN (…, NULL) is never TRUE for any x — it is FALSE for a value that matches a real member (Alice, Cara) and UNKNOWN for one that doesn't (Bob), because x <> NULL is UNKNOWN and the AND-chain can never be all-TRUE. Either way the row is dropped, so the "never ordered" rows the filter was meant to keep — Bob — vanish. One NULL zeros out the whole NOT IN filter. EXCEPT and NOT EXISTS do not use that equality chain — Bob survives.

Anti-join plan shape (why NOT EXISTS often wins)

-- Logical anti-join: for each outer row, prove no matching inner row
SELECT a.customer_id
FROM AllCustomers a
WHERE NOT EXISTS (
  SELECT 1 FROM CustomersWhoOrdered b
  WHERE b.customer_id = a.customer_id
);

With an index on CustomersWhoOrdered(customer_id), the engine can nested-loop or hash-anti-join with an early exit on first match. EXCEPT is often planned as sort/hash set-difference of two projections — fine for distinct sets, sometimes heavier if you only needed a semi/anti pattern on keys. Prefer NOT EXISTS when you are filtering a driving table against "has no child row."

The NOT IN / NULL trap: WHERE customer_id NOT IN (SELECT customer_id FROM B) returns zero rows if any value in B is NULL — because x NOT IN (…, NULL) evaluates to UNKNOWN, never true. NOT EXISTS and the anti-join don't have this problem; prefer them.

Takeaways

• EXCEPT = rows in the first query not in the second, de-duplicated; native in PostgreSQL and MySQL ≥ 8.0.31 (Oracle: MINUS).
• Emulate with NOT EXISTS or a LEFT JOIN … IS NULL anti-join.
• Avoid NOT IN with a possibly-NULL subquery — one NULL makes it return nothing (Bob disappears in the trace above).

---

Re-authored for correctness and a worked NOT IN vs EXCEPT row trace for this guide. Per the ISO SQL standard, MySQL 8.0.31 notes & the well-known NOT IN/NULL pitfall. See also: INTERSECT, Handle NULLs, Subqueries.

I just read a lesson on **EXCEPT** (Databases) and want to truly understand it. Explain EXCEPT from first principles using ONE vivid real-world analogy and a visual mental model — draw it as ASCII art or a clear step-by-step diagram — with a concrete example using real numbers. Then ask me one question to check I got the mental picture, and wait for my reply. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Teach me **EXCEPT** interactively. Ask me ONE guiding question at a time, wait for my answer, and adapt to my confusion — build the idea with me step by step instead of explaining it all at once. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Quiz me on **EXCEPT** with 5 questions, easy to tricky, ONE at a time. Tell me if each answer is right; at the end, explain clearly what I got wrong and why. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Help me remember **EXCEPT** for the long term: give the one-sentence intuition, a memorable hook/mnemonic, a tiny worked example, and 3 active-recall flashcards (Q -> A). If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.