Anti-join

Return the rows in one table that have no match in another.

6 min read

Overview

Some questions are about what is missing.

The products never ordered, the users who never logged in, the payments with no invoice. An anti-join answers them: it returns the rows in one table that have no match in another. You met the shape at the end of the outer joins guide. Here it becomes a tool of its own.

products

id	name
1	Desk
2	Lamp
3	Rug

orders

id	product_id
901	1
902	1
903	2

Trace each product id through orders. Which product survives when the rule is “keep only products with no matching order”?

How it works

An anti-join behaves as a filter over the kept table. For each product, the database probes orders for a match on the key, and a product is removed the moment any match is found. A product whose probe finds nothing is kept. Because no order row is ever attached, the result carries only product columns.

select p.name
from products p
where not exists (
  select 1 from orders o where o.product_id = p.id
)
order by p.id;

Run the demo and compare it with your prediction.

Keep only the unmatchedPress play, or step through it yourself.

products p

id	name
1	Desk
2	Lamp
3	Rug

orders o

id	product_id
901	1
902	1
903	2

result

name

not exists (o.product_id = p.id)

Rug survives because no order carries product_id 3. Desk and Lamp are removed as soon as their first matching order is found.

The same result has two other spellings. A left join whose match came back null, left join orders o on o.product_id = p.id where o.id is null, and not in with a subquery. All three are anti-joins. Trade-offs weighs them below, because one of the three misbehaves around null.

Patterns

The anti-join shows up wherever an absence is the answer.

1
The never-matched rows
Products never ordered, customers who never bought, articles nobody read. Probe the activity table and keep the rows that found nothing. This is the basic negative-existence pattern.
2
What one table has that another lacks
Signups without activations, exports missing from the warehouse. Probe table B from table A and keep the gaps. Run it in both directions and you have a full reconciliation.
```
select s.user_id
from signups s
where not exists (
  select 1 from activations a
  where a.user_id = s.user_id
);
```
3
Orphans, by reversing the direction
Point the probe the other way to audit integrity: orders whose product_id matches no product. On a well-constrained schema the query returns nothing, and an empty answer here means the integrity held.

Trade-offs

Of the three spellings, not exists is the default. It says what you mean, handles null keys correctly, and lets the database stop probing a row at the first match it finds. The left join with is null is just as correct and reads naturally if you think in joins. Databases usually plan both the same way. not in reads the friendliest and is the one to be careful with, for the reason Pitfalls makes plain. Reach for not exists first, and treat the other two as alternative spellings of the same question.

The anti-join has a positive counterpart. The reversed question, which rows do have a match, belongs to the semi-join and its own guide. except subtracts one result from another as well, and the difference is what each compares: except works on entire rows and folds duplicates, while the anti-join filters by a key you choose. The set operations guide covers that boundary.

Pitfalls

NOT IN meets a null and returns nothing

Write where p.id not in (select product_id from orders) and let one product_id be null, and the whole query returns zero rows rather than the unmatched ones. A comparison against null is unknown, so not in can never prove a row is absent from a list that contains one. No error is raised, and the empty result looks like there is nothing to find. This null behavior is why not exists is the safer default.

Correlate the probe. The subquery must reference the outer row, o.product_id = p.id. Forget the correlation and you are asking whether orders has any rows at all, which filters everything or nothing.
The result is left rows only. An anti-join never returns columns from the probed table. If you find yourself selecting from it, the question was not an anti-join.
Absence is direction-sensitive. Products with no orders and orders with no product are different questions. Be deliberate about which table you keep and which you probe.
A null key on the kept side stays. A product whose own id is null matches nothing, so not exists keeps it. If null keys are possible on your side, decide whether they belong in the answer and filter them on purpose.

Performance

Logically, a match can reject a row as soon as it is found while an unmatched row requires establishing absence. Engines may use indexes or hashing as part of an anti-join plan. Inspect the probe key and the chosen plan.

Practice

EasyReturn never-ordered products with a null-safe anti-join and alphabetical ordering. Every returned product must have zero order matches, and every ordered product must be absent.MediumBuild a 30-day inactive-customer query with not exists and keep the date condition inside the correlated probe. Old-only and never-ordered customers must remain, while recent buyers must not.HardCreate a minimal nullable case where not in returns no rows, then rewrite it with not exists. The second query must recover valid unmatched rows without being suppressed by null.

Recap

Use an anti-join for “A without B,” where the result needs columns from the kept side only.
Default to correlated not exists and check that the probe references the current outer row.
Guard not in against nullable probe values because one null can suppress every unmatched row.
Check direction explicitly, since products without orders and orders without products are different audits.
Use a semi-join when presence qualifies the row, or except when whole rows are being subtracted.