EXCEPT
- SQL course
- Intermediate
- Lesson 62/115
- 4 min
Take rows in one query but not the other.
EXCEPT
EXCEPT returns rows in the first query but not in the second. Some databases use MINUS.
SELECT name FROM customers
EXCEPT
SELECT name FROM blacklist;This query returns customers not on the blacklist.
name is selected from customers.
EXCEPT — details
EXCEPT is a set difference. It subtracts the second set from the first.
For example, customers EXCEPT blacklist gives customers not on the blacklist; order matters, and reversing it gives a different result.
Practice EXCEPT
Find customers not on the blacklist using EXCEPT.
Starter code
-- difference
SELECT name FROM customersSample solution
SELECT name FROM customers
EXCEPT
SELECT name FROM blacklist;Expected output
name Mehmet Zeynep
Questions and answers
Review the lesson questions together with their answers. To test yourself, solve the lesson interactively.
1 What does EXCEPT return?
- The difference Correct answer
- The intersection
- The duplicates
- The union
Explanation: EXCEPT returns rows in the first set but not the second.
2 Add EXCEPT
Question
SELECT name FROM customers
____
SELECT name FROM blacklist;
Answer
SELECT name FROM customers
EXCEPT
SELECT name FROM blacklist;Explanation: EXCEPT returns rows that are in the first query but not in the second.
3 EXCEPT: match the terms
| Term | Meaning |
|---|---|
EXCEPT |
difference |
MINUS |
difference (Oracle) |
UNION |
union |
INTERSECT |
intersection |