EXCEPT

Find rows in the first result that don't appear in the second

Advanced 8 minEXCEPTset operationsMINUS

Set Difference

EXCEPT returns rows from the first query that do not appear in the second query — the set-theory difference: A minus B. It answers "what's in the first set but not the second?" Here's what to know:

Direction matters — A EXCEPT B is not the same as B EXCEPT A. The first query is always the source set, and the second query defines what gets excluded from it. Swapping them produces completely different results.

Alternative to anti-joins — EXCEPT replaces the LEFT JOIN + WHERE right.id IS NULL pattern for finding "what exists in A but not in B." It's cleaner, more readable, and conveys the intent more directly in your query.

Auto-deduplicates — Like UNION and INTERSECT, EXCEPT automatically removes duplicate rows from the final result. Some databases use the keyword MINUS instead of EXCEPT — Oracle is the most notable example.

Knowledge Canvas

How EXCEPT Works

Find rows in A but not in B

A EXCEPT B = rows in first query NOT in second
Direction matters: A EXCEPT B ≠ B EXCEPT A
Auto-deduplicates like UNION and INTERSECT
Oracle uses MINUS instead of EXCEPT

EXCEPT vs Anti-Join

Two ways to find what's missing

EXCEPT: set difference, concise

LEFT JOIN + IS NULL: more flexible

Compares full rows

Match on specific columns

Auto-deduplicates

Preserves duplicates from left

EXCEPT Gotchas

Direction and dedup

Swapping query order gives completely different results
EXCEPT auto-deduplicates — duplicates in A are collapsed
Column count mismatch → syntax error
Not available in older MySQL versions (pre-8.0)

When to Use EXCEPT

Finding gaps and differences

Customers who ordered but never reviewed
Products not in any order
Data reconciliation between systems
Regression testing: old results EXCEPT new results

Syntax

Syntax Template

1SELECT

2 cols

3FROM table_a

4EXCEPT

5SELECT

6 cols

7FROM table_b;

EXCEPTRows in first query but NOT in second

Direction mattersA EXCEPT B ≠ B EXCEPT A

Sample Data

orders

12 rows

id	customer_id
5001	C1001
5002	C1002
5003	C1001
5004	C1003
5005	C1006
5006	C1005
5007	C1004
5008	C1009
5009	C1012
5010	C1007
5011	C1008
5012	C1010

reviews

12 rows

id	customer_id	product_id
7001	C1001	P101
7002	C1003	P101
7003	C1005	P101
7004	C1002	P102
7005	C1006	P102
7006	C1004	P103
7007	C1007	P104
7008	C1009	P105
7009	C1012	P105
7010	C1008	P107
7011	C1011	P108
7012	C1001	P109

order_items

16 rows

id	order_id	product_id	quantity	unit_price
6001	5001	P101	2	24.99
6002	5001	P104	1	12.99
6003	5002	P109	2	159
6004	5002	P102	1	189
6005	5003	P108	1	8.5
6006	5004	P107	1	89.99
6007	5005	P106	1	95
6008	5005	P102	1	189
6009	5006	P109	1	159
6010	5007	P103	1	45.5
6011	5008	P105	1	79
6012	5009	P110	1	425
6013	5010	P101	1	24.99
6014	5011	P105	1	79
6015	5011	P103	1	45.5
6016	5012	P107	1	89.99

Worked Example

Find customer IDs that placed orders but never left a review.

SQL

1SELECT DISTINCT

2 customer_id

3FROM orders

4EXCEPT

5SELECT DISTINCT

6 customer_id

7FROM reviews;

All unique customer_ids from orders are computed. Then any that also appear in reviews are removed. What remains are customers who ordered but never reviewed.

Output

1 row

customer_id
C1010

Common Mistakes

❌

Column count mismatch

SQL

1SELECT

2 name,

3 email

4FROM customers

5EXCEPT

6SELECT

7 name

8FROM products;

First query has 2 columns, second has 1. Set operations require matching column counts.

Ensure both SELECTs have the same number of columns with compatible types.

Key Concepts

1A EXCEPT B ≠ B EXCEPT A — direction matters

2Cleaner than LEFT JOIN + WHERE IS NULL for finding gaps

3Auto-deduplicates like UNION and INTERSECT

4Some databases call it MINUS instead of EXCEPT (Oracle)

Pro Tip

Finding what's missing or different between two datasets is a fundamental analysis task. EXCEPT expresses it naturally.

When to Use

Customers who ordered but never reviewed, products not in any order, data reconciliation between systems.

Challenge

Solve the problem below

Find product IDs that have been ordered but never reviewed. Use EXCEPT on the product_id columns from order_items and reviews.

Your Query

INTERSECT