EXCEPT

Take rows in one query but not the other.

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.

Find customers not on the blacklist using EXCEPT.

Show example solution

SELECT name FROM customers
EXCEPT
SELECT name FROM blacklist;

Add EXCEPT

SELECT name FROM customers
____
SELECT name FROM blacklist;

Show answer

SELECT name FROM customers
EXCEPT
SELECT name FROM blacklist;

Next lesson: Set rules