Anti-Join Pattern (LEFT JOIN WHERE NULL)
intermediatejoins
2 min read
What is an Anti-Join?
An anti-join returns rows from the left table that have no matching rows in the right table. PostgreSQL does not have an explicit ANTI JOIN keyword, but you can achieve it with LEFT JOIN ... WHERE right.key IS NULL.
Syntax
SELECT a.*
FROM table_a a
LEFT JOIN table_b b ON a.id = b.a_id
WHERE b.a_id IS NULL;When to Use
- Finding customers who have never placed an order
- Identifying students not enrolled in any course
- Detecting orphaned records or missing relationships
Key Points
- Pattern — LEFT JOIN + WHERE IS NULL on the join key of the right table.
- Alternative: NOT EXISTS —
WHERE NOT EXISTS (SELECT 1 FROM b WHERE b.a_id = a.id)is equivalent. - Alternative: NOT IN — Works but beware of NULL values in the subquery.
- Performance — NOT EXISTS and LEFT JOIN WHERE NULL typically perform similarly and both use Anti Join nodes in PostgreSQL.
- Prefer NOT EXISTS — It is often clearer and avoids NULL pitfalls of NOT IN.
Guided Practice
Solve the challenge below. Use hints when stuck and check your answer for instant feedback.
Practice challengeGuided learning mode
Anti-Join Pattern (LEFT JOIN WHERE NULL) Challenge
Write a query that find students with no enrollments.
Expected result
Students who do not appear in the enrollments table.
Hidden checks
- Returned rows and values
- Output columns and result shape
- Final database state after the query runs
Lesson guidance
What is an Anti-Join?
Initializing database...Each run starts from fresh sample data.
More Examples
Products with no orders
Find products that have never been ordered.
Initializing database...Each run starts from fresh sample data.
Anti-join with NOT EXISTS
Equivalent anti-join using NOT EXISTS.
Initializing database...Each run starts from fresh sample data.
Frequently Asked Questions
Which is faster: LEFT JOIN WHERE NULL or NOT EXISTS?
In PostgreSQL they typically produce the same execution plan (Anti Join). Choose whichever is more readable for your use case.
Why not use NOT IN for anti-joins?
NOT IN fails when the subquery returns NULL values — no rows are returned. NOT EXISTS handles NULLs correctly.