Which of NOT EXISTS, NOT IN and LEFT JOIN ... IS NULL correctly returns customers with no orders?

## The pattern "Rows on the left with no match on the right" is the **anti-join**. It is the complement of the semi-join, it appears constantly (customers who never ordered, products never sold, staging rows not yet loaded), and SQL gives you three idiomatic spellings. An interviewer asking for all three is checking whether you understand them as one concept with different failure modes, rather than as three memorised snippets. ## Form 1 — NOT EXISTS (the reference implementation) ```sql SELECT c.id, c.name FROM customers c WHERE NOT EXISTS ( SELECT 1 FROM orders o WHERE o.customer_id = c.id ); ``` `EXISTS` yields TRUE or FALSE and never UNKNOWN, so `NOT EXISTS` is simply its negation: keep the customer when the correlated subquery would produce no rows. Nothing about NULLs in `orders.customer_id` can change that — a NULL customer_id never satisfies `o.customer_id = c.id`, so such a row is not a match, which is exactly the answer you want. The pattern also generalises without thought: matching on two columns just means two ANDed predicates inside the subquery. ## Form 2 — LEFT JOIN with an IS NULL test ```sql SELECT c.id, c.name FROM customers c LEFT JOIN orders o ON o.customer_id = c.id WHERE o.customer_id IS NULL; ``` The left join keeps every customer; unmatched customers get a row whose `orders` columns are all NULL (NULL-extension). The `WHERE` then keeps only those NULL-extended rows. Two details make or break it. **Which column you test.** Pick a column that cannot be NULL in a real matched row. The join key is always safe: if `o.customer_id` is NULL in the output, the only possible cause is NULL-extension, because a row that matched must have had `o.customer_id = c.id`. Testing a nullable payload column such as `o.cancelled_at` is a genuine bug — customers with a real, uncancelled order would be reported as having no orders at all. **Where the extra predicates go.** If the requirement is "customers with no order in 2024", the year filter belongs in `ON`, not `WHERE`. Put it in `WHERE` and the NULL-extended rows fail it, turning the query into an inner join and returning nothing. (Predicate placement with outer joins is a topic in its own right; the anti-join is simply its most common victim.) A nice property: this form cannot produce duplicates. Multiplicity in an outer join comes from *matching* rows, and the surviving rows are by definition the non-matching ones — exactly one per unmatched left row. ## Form 3 — NOT IN (the fragile one) ```sql SELECT c.id, c.name FROM customers c WHERE c.id NOT IN (SELECT o.customer_id FROM orders o); ``` When `orders.customer_id` is declared `NOT NULL`, this is equivalent to the other two and reads very naturally. When it is nullable and even one row holds NULL, the predicate can never be TRUE for any customer, and the query returns the empty result set — silently, with no error. If you must use this form over a nullable column, add `WHERE o.customer_id IS NOT NULL` inside the subquery, and be aware that the three-valued-logic reasoning behind the trap is a topic worth studying on its own. One more asymmetry that catches people out in the opposite direction: if the subquery returns **no rows at all**, `NOT IN` is TRUE for every customer, so an empty `orders` table returns all customers. That is correct, but it means an empty subquery and a NULL-containing subquery produce opposite extremes. ## Choosing between them Default to `NOT EXISTS`. It states the intent ("no such row exists"), is immune to the NULL trap, needs no thought about which column to test, and extends to composite keys. Reach for `LEFT JOIN ... IS NULL` when the surrounding query already left-joins that table for other reasons, or when a team's house style prefers it — just discipline yourself to test the join key. Treat `NOT IN` over a subquery as a smell unless the column is provably `NOT NULL`; over a literal list (`status NOT IN ('a','b')`) it is perfectly ordinary. ## What to say in the interview Name the concept ("these are all anti-joins"), give `NOT EXISTS` as the safe default, then state the two equivalence conditions for the left-join form and the one failure mode of `NOT IN`. That sequence shows you know they are the same idea, and that you know exactly where the idea stops holding.