Anti-Join

An anti-join returns the rows from one table that have no matching row in another: customers who have never ordered, products never sold, accounts with no login this month. It is the opposite of a semi-join, which keeps rows that do have at least one match. Most SQL dialects have no dedicated keyword for it — DuckDB, for one, does offer ANTI JOIN — so the same question is usually written in one of several portable ways, and they do not all mean the same thing.

Four ways to write it

The examples use an invented customers table with C1, C2, and C3, and an orders table in which C1 and C2 have orders and one guest order has a NULL customer_id. The correct answer is C3.

-- 1. NOT EXISTS: returns C3
SELECT c.customer_id
FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);

-- 2. LEFT JOIN and a test for no match: returns C3
SELECT c.customer_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;

-- 3. NOT IN: returns no rows at all
SELECT customer_id
FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM orders);

-- 4. EXCEPT: returns C3, as a de-duplicated set of IDs
SELECT customer_id FROM customers
EXCEPT
SELECT customer_id FROM orders;

Why NOT IN fails

NOT IN is a chain of “not equal” comparisons. For C3 that means C3 is not C1, C3 is not C2, and C3 is not NULL — and the last comparison is unknown, not true. PostgreSQL’s documentation states the consequence: if no right-hand value is equal and at least one right-hand row is null, the result of NOT IN is “null, not true.” A row is kept only when its condition is true, so C3 is dropped. Because every customer is compared against the same NULL, one NULL in the subquery removes every row from the result.

The failure is silent and plausible. An empty “never ordered” list looks like good news. Adding WHERE customer_id IS NOT NULL inside the subquery fixes this query, but it depends on everyone remembering to add it, and on the column staying the way it is.

Filtering the right-hand NULLs still does not make NOT IN equivalent to NOT EXISTS. A customer whose own customer_id is NULL is dropped by NOT IN whenever the subquery returns at least one row: its comparisons are unknown. If the subquery returns no rows, however, NOT IN is true regardless of the left value and the customer is kept, as in a system with no orders yet.

An equality-correlated NOT EXISTS keeps that customer because no order row equals NULL, so nothing matches. Whether an unknown key belongs in “never ordered” is a policy to decide, not a detail to inherit from the syntax.

What distinguishes the other forms

The four forms agree only while the key is non-NULL on both sides and the left table lists each key once (EXCEPT de-duplicates, so repeated left rows collapse to one); once it can be NULL, decide first whether a row with an unknown key counts as unmatched, then pick the form that implements that. NOT EXISTS is the most robust general default: it expresses the question directly and keeps the left table’s full rows. The LEFT JOIN form is equally correct when written carefully, and PostgreSQL’s documentation on outer joins is the reference for the distinction that keeps it so: a condition in ON defines what counts as a match, a condition in WHERE filters the joined result, and a WHERE condition that is false or unknown for the NULLs an unmatched row carries removes exactly the rows an anti-join exists to keep. WHERE o.order_id IS NULL admits them, which is why it works; WHERE o.status = 'paid' would not. Move a condition into ON when it is part of the match, and leave it in WHERE when it is meant to select from the result — moving it changes the question. Execution speed of the forms depends on the engine and its planner, and is a separate question from correctness.

How anti-joins fit among the other places where valid SQL returns wrong answers is worked through in When Correct SQL Returns the Wrong Answer.

Further reading

• PostgreSQL Documentation, Subquery Expressions
• PostgreSQL Documentation, Table Expressions
• PostgreSQL Documentation, Combining Queries