Finding What Is NOT There: Three Ways, One Correct

“Which customers have never ordered?” — this question fails more candidates than any other join question.

The problem

Find customers who have never placed an order. The catch: those customers have no rows in the orders table at all, so an ordinary JOIN cannot see them.

Three ways to write it

1. LEFT JOIN … IS NULL

SELECT c.*
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

Keep every customer, then keep only the rows where the right side came back empty. The most intuitive version when you are learning.

2. NOT EXISTS — usually fastest

SELECT c.*
FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

The engine stops at the first match instead of joining everything. Safe with NULL.

3. NOT IN — the dangerous one

SELECT * FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);

Why: id NOT IN (1, 2, NULL) expands to id <> 1 AND id <> 2 AND id <> NULL. That last comparison is always UNKNOWN, and AND with UNKNOWN can never be TRUE.

If you must use it, guard it:

WHERE id NOT IN (
  SELECT customer_id FROM orders
  WHERE customer_id IS NOT NULL
);

The common mistake

Putting the filter in WHERE instead of ON:

-- WRONG: turns the LEFT JOIN into an INNER JOIN
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'completed' AND o.id IS NULL

-- RIGHT
LEFT JOIN orders o
  ON o.customer_id = c.id
 AND o.status = 'completed'
WHERE o.id IS NULL

Practice

• LeetCode 183 — Customers Who Never Order
• LeetCode 1607 — Sellers With No Sales
• LeetCode 1795 — Rearrange Products Table
• LeetCode 619 — Biggest Single Number
• HackerRank — Challenges