EXCEPT keeps rows found only in the first result
EXCEPT compares two SELECT results and keeps rows from the first result that do not appear in the second. The order matters: swapping the two SELECT statements changes the answer. Both sides must return the same number of compatible columns. Like UNION, it removes repeated rows from what it keeps. Dialect note: Oracle spells this MINUS, and MySQL has no equivalent.
A tiny example
In plain English: Show me the items on the restock list that have not been delivered.
SELECT name
FROM restock_list
EXCEPT
SELECT name
FROM deliveries;Query result
| name |
|---|
| cocoa |
| vanilla syrup |
Cocoa and vanilla syrup are on the restock list but not in deliveries.
Related SQL lessons
Ready for a break?
A fresh, bite-sized SQL puzzle waits every morning. Tap tiles, see real results. No terminal, no setup, no timers.
Try today's free SQL puzzle