✏️ Explanatory Question
Level: Coding Round — A LeetCode classic; tests the "find rows in A with no match in B" pattern and its three canonical solutions.
The Puzzle: Given a customers table and an orders table, find all customers who have never placed an order. This "find the non-matching rows" pattern is called an anti-join.
| id | name |
|---|---|
| 1 | Rumman |
| 2 | Krushna |
| 3 | Swetha |
| 4 | Ritesh |
| id | customer_id |
|---|---|
| 101 | 1 |
| 102 | 3 |
| name |
|---|
| Krushna |
| Ritesh |
Customers 1 (Rumman) and 3 (Swetha) have orders. Customers 2 (Krushna) and 4 (Ritesh) never ordered → they are the answer.
The three canonical anti-join techniques: (1) NOT IN a subquery, (2) NOT EXISTS a correlated subquery, and (3) LEFT JOIN ... WHERE right IS NULL. All three express "no matching order," but they differ crucially in NULL safety and performance.
SELECT c.name
FROM customers c
WHERE NOT EXISTS (
SELECT 1 FROM orders o WHERE o.customer_id = c.id
);
-- NULL-safe and typically efficient; stops at the first matchSELECT c.name
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.customer_id IS NULL; -- no matching order row -> never orderedSELECT c.name
FROM customers c
WHERE c.id NOT IN (
SELECT customer_id FROM orders
WHERE customer_id IS NOT NULL -- MUST guard against NULLs here!
); The NOT IN NULL trap (revisited): If the orders.customer_id subquery returns even one NULL, a bare NOT IN returns zero rows — silently wrong. This is why NOT EXISTS or LEFT JOIN ... IS NULL are preferred: they are inherently NULL-safe. Always add WHERE customer_id IS NOT NULL if you must use NOT IN.
| Approach | NULL-safe? | Notes |
|---|---|---|
| NOT EXISTS | Yes | Usually best; short-circuits |
| LEFT JOIN ... IS NULL | Yes | Clear intent; good with indexes |
| NOT IN | No | Breaks silently on NULLs |
Interviewer follow-up: "Return customers whose LAST order was more than a year ago, OR who never ordered." → Combine an aggregate with the anti-join: LEFT JOIN orders, GROUP BY customer, then HAVING MAX(order_date) < CURDATE() - INTERVAL 1 YEAR OR MAX(order_date) IS NULL. The IS NULL branch captures never-ordered customers, showing you can blend anti-join logic with aggregation.