Level 4 · Lapsed customers with EXCEPT

EXCEPT returns the rows of the first query that do not appear in the second. It is the “subtract” of set operations, and the order of the two queries matters.

The query

SELECT customer_id FROM orders
WHERE status = 'completed' AND order_date >= '2024-01-01' AND order_date < '2025-01-01'
EXCEPT
SELECT customer_id FROM orders
WHERE status = 'completed' AND order_date >= '2025-01-01' AND order_date < '2026-01-01';

Of the 997 customers who completed an order in 2024, 752 also bought in 2025 (the previous level), and 245 did not. Like UNION and INTERSECT, plain EXCEPT returns distinct rows, so each lapsed customer appears once. Swap the two queries and you get a different question: 2025 buyers who did not buy in 2024.

Putting the set operation inside a CTE and joining or filtering afterwards is the clean pattern: the comparison happens on customer_id alone, and names and emails are added once the list is final.

EXCEPT ALL

EXCEPT ALL (supported in DuckDB and Postgres) works on counts instead of distinct rows: each row on the right cancels one matching row on the left. On these branches, which are one row per order, it returns 1,564 rows. That number is neither customers nor orders of lapsed customers, it is “2024 orders left over after cancelling one per 2025 order”. You rarely want it for people lists. It is useful for reconciling two copies of the same table, where duplicates are real rows.

EXCEPT, NOT EXISTS and NOT IN

The same “in A but not in B” can be written three ways, and they differ on NULLs. Take customers who never had a web session. web_sessions.customer_id is NULL for anonymous visits:

SELECT customer_id FROM customers
EXCEPT
SELECT customer_id FROM web_sessions;          -- 205 rows

NOT EXISTS with a correlated subquery also returns 205. But WHERE customer_id NOT IN (SELECT customer_id FROM web_sessions) returns 0: once the list contains a NULL, NOT IN can never be true. EXCEPT treats NULLs as equal to each other and simply compares rows, so it is immune to that trap. Chapter 11 has a full level on the NOT IN bug.

Common mistakes

• Wrong direction. Read it as “A, except the ones also in B” and check the first query is A.
• Different filters in the two branches. If the 2025 branch forgets status = 'completed', a customer whose only 2025 order was cancelled stops counting as lapsed.
• Extra columns in the comparison. SELECT customer_id, channel would call a customer lapsed if they only switched channel.
• Calling 245 a churn rate. It is a count. The rate is 245 / 997 = 24.6% of 2024 buyers.

In SQL Server

EXCEPT works the same in SQL Server, but there is no EXCEPT ALL. Oracle calls it MINUS (EXCEPT from 21c), and MySQL supports EXCEPT from 8.0.31, so older code uses NOT EXISTS.