Anti-joins

Keep the rows on the left that never found a partner on the right: the customers who never ordered.

The idea

An inner join answers who matched. An anti-join answers the opposite question: who didn't. You walk each row of table A, look for any partner in table B, and keep only the rows that came back empty-handed.

Two ways of writing it say this faithfully: NOT EXISTS, and a LEFT JOIN whose padded NULLs you filter for. A third way, NOT IN, looks equivalent and usually behaves, until a single NULL appears in the list and the whole result quietly collapses to nothing.

Watch the rows pair off

customers (A) orders (B) customer_id

Ready. We are looking for customers with no matching row in orders.

How it works

All three queries below ask the same English question: which customers have no order? The first two ask it in a way three-valued logic cannot distort. The third asks it as “is this id absent from that list of values”, and a NULL in the list makes “absent” unprovable.

-- 1. NOT EXISTS — says exactly what we mean, and is NULL-safe
SELECT c.id, c.name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.id      -- the correlation is the whole point
);

-- 2. LEFT JOIN ... IS NULL — same answer, different shape
SELECT c.id, c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.customer_id IS NULL;        -- test the join key (or B's primary key):
                                    -- a column that is never NULL in a matched row

-- 3. NOT IN — reads the same, behaves differently
SELECT c.id, c.name
FROM customers c
WHERE c.id NOT IN (SELECT o.customer_id FROM orders o);
-- if orders.customer_id is nullable, this returns ZERO rows the moment
-- one NULL is present, because  id <> NULL  is UNKNOWN, never TRUE.
-- Repair (if you must keep NOT IN):
--     ... NOT IN (SELECT o.customer_id FROM orders o WHERE o.customer_id IS NOT NULL)

Why: x NOT IN (a, b, NULL) expands to x <> a AND x <> b AND x <> NULL. The last conjunct is UNKNOWN. TRUE AND TRUE AND UNKNOWN = UNKNOWN, and WHERE keeps only rows that evaluate to TRUE. Rows that do match still evaluate to FALSE and are dropped, so every row is dropped.

Cost

plan the engine may choosetimeextra spacewhen
hash anti-joinO(|A| + |B|)O(|B|)large unsorted inputs; build side must fit in memory
merge anti-joinO(|A| + |B|)O(1)both inputs already ordered by the key (index scan); otherwise add O(n log n) to sort
indexed nested loopO(|A| · log|B|)O(1)A is small and B has an index on the join key
plain nested loopO(|A| · |B|)O(1)no index: the plan you don't want

The trade-off: NOT EXISTS and LEFT JOIN … IS NULL normally compile to the same anti-join operator, so pick whichever reads better. NOT IN over a nullable column forces the engine to preserve three-valued semantics, which often blocks the anti-join rewrite: slower as well as wrong.

Watch out for

Worked example

You need a win-back list: every customer with no order in the last 90 days. Write it as SELECT c.id, c.email FROM customers c WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id AND o.placed_at >= now() - interval '90 days'). Note the date test sits inside the subquery: it narrows what counts as a partner, so a customer whose only order is two years old still has no partner and stays in the list.

The tempting alternative, c.id NOT IN (SELECT customer_id FROM orders WHERE placed_at >= now() - interval '90 days'), reads almost identically. But if orders.customer_id is nullable, say guest checkouts write NULL there, a single recent guest order puts a NULL in that list, every comparison becomes UNKNOWN, and your win-back campaign mails nobody. Same intent, same data, empty result. The animation above is exactly that moment.

Check yourself

1. orders.customer_id is nullable and exactly one row holds NULL. What does WHERE c.id NOT IN (SELECT customer_id FROM orders) return?

2. Which one is a faithful anti-join between customers c and orders o?