EXCEPT / MINUS – Show Me What’s in One, But Not the Other
(because sometimes, you want to know who didn’t show up to the party)
🔧 Quick Syntax
SELECT column1, column2
FROM table1
EXCEPT -- or MINUS
SELECT column1, column2
FROM table2;
This tells SQL:
“Give me rows from the first query that do NOT exist in the second one.”
Note:
– EXCEPT works in PostgreSQL, SQL Server, and SQLite
– MINUS is used in Oracle
– MySQL doesn’t support either, but there’s a workaround below
Context: Suppose You Have Two Lists of Customers
Let’s say your app tracks two types of users:
- People who signed up for your newsletter
- People who made a purchase
And now, you want to find out:
“Who signed up, but never bought anything?”
📨 Table: newsletter_signups
| alice@mail.com |
| bob@mail.com |
| carol@mail.com |
| dave@mail.com |
Table: customers
| bob@mail.com |
| carol@mail.com |
| emma@mail.com |
Suppose You Need To…
…find users who signed up but never purchased.
SQL Using EXCEPT
SELECT email
FROM newsletter_signups
EXCEPT
SELECT email
FROM customers;
Output:
| alice@mail.com |
| dave@mail.com |
These are users who joined your list but haven’t made a purchase — and maybe need a friendly nudge
In Oracle, Use MINUS Instead
SELECT email
FROM newsletter_signups
MINUS
SELECT email
FROM customers;
Same logic, just a different keyword.
Let’s Compare It With INTERSECT
- INTERSECT → what’s in both
- EXCEPT / MINUS → what’s in only the first, and not in the second
Think of it like subtraction:
“Give me A minus B.”
Recap
EXCEPTorMINUSreturns rows that are in Query 1 but not in Query 2- Used to filter out overlapping data
- Very handy for “who hasn’t…” type questions
- Not supported in MySQL — see the workaround below
Beginner Tips & Gotchas
| Confusion | Clarification |
|---|---|
| Is this like a JOIN? | No — this is set logic, not relational matching |
| Does it return duplicates? | No — results are always distinct |
| Do the columns need to match? | Yes — same number and compatible data types |
| Does MySQL support this? | No — but use LEFT JOIN with WHERE IS NULL instead |
MySQL Workaround
If you’re using MySQL and want to mimic EXCEPT, try this instead:
SELECT s.email
FROM newsletter_signups s
LEFT JOIN customers c ON s.email = c.email
WHERE c.email IS NULL;
This gives the same result — just using joins instead of set operations.