What is an anti-join, and when does NOT IN behave differently from NOT EXISTS?

How to think about it

This lands as a Hard question because the interviewer is hunting for one specific thing: do you know that NOT IN silently returns zero rows the moment a NULL appears in the subquery? Plenty of people can define an anti-join — a query returning the left rows with no match on the right. The ones who pass can explain why NOT IN breaks and show they reach for NOT EXISTS by reflex.

There are three ways to write it, and they are not equivalent once NULLs are in play:

-- Pattern 1: NOT IN — dangerous with NULLs
SELECT id FROM orders
WHERE customer_id NOT IN (SELECT id FROM vip_customers);

-- Pattern 2: NOT EXISTS — NULL-safe, the default to reach for
SELECT o.id FROM orders o
WHERE NOT EXISTS (SELECT 1 FROM vip_customers v WHERE v.id = o.customer_id);

-- Pattern 3: LEFT JOIN ... IS NULL — NULL-safe, often the fastest
SELECT o.id FROM orders o
LEFT JOIN vip_customers v ON v.id = o.customer_id
WHERE v.id IS NULL;

A worked example — watch NOT IN vanish

vip_customers holds 10, 20, and one NULL. The same anti-join, two ways:

-- orders: customers 10, 20, 30, 40   |   vip_customers: 10, 20, NULL

-- NOT IN — expands to customer_id != 10 AND != 20 AND != NULL
SELECT id FROM orders
WHERE customer_id NOT IN (SELECT id FROM vip_customers);

(0 rows)

-- NOT EXISTS — the correct anti-join
SELECT o.id FROM orders o
WHERE NOT EXISTS (SELECT 1 FROM vip_customers v WHERE v.id = o.customer_id);

id
3
4

Customers 30 and 40 are the ones with no VIP match, and NOT EXISTS returns exactly those. NOT IN returns nothing at all — not an error, just an empty result — and that gap between “looks right” and “is right” is the whole bug.

Why NOT IN breaks

SQL expands x NOT IN (10, 20, NULL) into x != 10 AND x != 20 AND x != NULL. That last comparison is UNKNOWN — not FALSE — because any comparison to NULL is unknown under three-valued logic. And UNKNOWN in a WHERE clause suppresses the row. So a single NULL anywhere in the subquery quietly drops every result:

SELECT 1 WHERE 5 NOT IN (1, 2, NULL);   -- 0 rows, though 5 is plainly not in the list

When to use each

Pattern	Use when
NOT IN	the subquery column is declared NOT NULL and the list is small/static
NOT EXISTS	the subquery column is nullable; correlated/complex condition
LEFT JOIN ... IS NULL	you also need columns from the right table, or prefer set-based style