Thinking habit: Know what you don't know
SQL: find customers who never placed an order (and the NOT IN trap)
The question: List the customers who have never placed an order.
customers
| customer_id | name |
|---|---|
| 1 | Asha |
| 2 | Ravi |
| 3 | Neha |
| 4 | Kabir |
orders
| order_id | customer_id |
|---|---|
| 101 | 1 |
| 102 | 1 |
| 103 | 3 |
| 104 | NULL |
This query looks right. Run it, decide if it is, and fix it.
SELECT name FROM customers WHERE customer_id NOT IN (SELECT customer_id FROM orders);
Show the answer and why the first query is wrong
Why the usual answer is wrong
SELECT name FROM customers WHERE customer_id NOT IN (SELECT customer_id FROM orders);
Order 104 has no customer (NULL). NOT IN compares each customer with every value in the list, and a comparison with NULL is unknown, never true, so no customer is returned at all.
The answer
SELECT c.name FROM customers c LEFT JOIN orders o ON o.customer_id = c.customer_id WHERE o.order_id IS NULL;
Keep every customer with a LEFT JOIN, then keep only the ones where no order matched: the order columns are NULL there.
NOT EXISTS is just as safe. If you use NOT IN, filter the NULLs out of the list first, and say why in the interview: it shows you know how NULL behaves.
With NOT EXISTS
SELECT c.name FROM customers c WHERE NOT EXISTS ( SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id );
NOT IN, with the NULLs filtered out
SELECT name FROM customers WHERE customer_id NOT IN ( SELECT customer_id FROM orders WHERE customer_id IS NOT NULL );
Practise more like this, on real business data
324 interview-style questions on 35 tables with the duplicates, NULLs and missing weeks interviewers test. Free.