Pattern
Anti-join
The rows of one table that have no match in another: customers who never ordered.
SELECT c.*
FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);
Same result with LEFT JOIN … WHERE o.customer_id IS NULL. Avoid NOT IN (subquery): one NULL in the
subquery and it returns nothing.
SELECT c.customer_id, c.first_name, c.last_name FROM customers c WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id) ORDER BY c.customer_id LIMIT 5;
| customer_id | first_name | last_name |
|---|---|---|
| 4 | Elsa | Richard |
| 5 | Louis | Martinez |
| 7 | Tom | Dupont |
| 9 | Jade | Meier |
| 12 | Sacha | Dumont |