Anti-JOIN Pattern
AdvancedAn anti-join returns rows from one table that have no match in another; the three implementations (NOT EXISTS, NOT IN, LEFT JOIN IS NULL) have different semantics and performance profiles.
Overview
An anti-join is conceptually "A minus B" — return rows from A that do not appear in B. SQL provides three ways to express this: (1) NOT EXISTS with a correlated subquery, (2) NOT IN with a subquery, and (3) LEFT JOIN + WHERE right_pk IS NULL. NOT EXISTS is typically the most efficient because the planner can implement it as a hash anti-join and short-circuits after the first match. NOT IN is dangerous when the subquery can return NULLs — a single NULL in the list makes the entire NOT IN return no rows due to three-valued logic. LEFT JOIN IS NULL is readable and portable but can be slower than NOT EXISTS for large tables. The planner often rewrites NOT EXISTS to the same plan as LEFT JOIN IS NULL, but NOT IN is never rewritten when NULLs are possible.
Three Anti-Join Implementations
Use NOT EXISTS as the default choice. Avoid NOT IN when the subquery column can be NULL. LEFT JOIN IS NULL is acceptable when the join column is a non-nullable PK.
-- Find users who have never placed an order
-- 1. NOT EXISTS (recommended — safe with NULLs, often fastest)
SELECT u.id, u.email
FROM users u
WHERE NOT EXISTS (
SELECT 1 FROM orders o WHERE o.user_id = u.id
);
-- 2. NOT IN (dangerous if orders.user_id can be NULL)
SELECT u.id, u.email
FROM users u
WHERE u.id NOT IN (SELECT user_id FROM orders);
-- If any row in orders has user_id = NULL → returns 0 rows!
-- Safe only when the subquery column is declared NOT NULL
-- 3. LEFT JOIN IS NULL (readable, portable)
SELECT u.id, u.email
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;
-- Products that have never been ordered
SELECT p.id, p.name
FROM products p
WHERE NOT EXISTS (
SELECT 1 FROM order_items oi WHERE oi.product_id = p.id
);Performance Deep Dive
The EXPLAIN plan reveals which physical anti-join strategy the planner selects. NOT EXISTS typically produces a Hash Anti Join. NOT IN with non-nullable columns may be rewritten to the same plan; with nullable columns it cannot.
-- PostgreSQL EXPLAIN: NOT EXISTS → Hash Anti Join
EXPLAIN (ANALYZE)
SELECT u.id FROM users u
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);
/*
Hash Anti Join (cost=1200..2400 rows=5000)
Hash Cond: (u.id = o.user_id)
-> Seq Scan on users
-> Hash
-> Seq Scan on orders
Short-circuit: stops probing as soon as a match IS found (opposite of hash join)
*/
-- NOT IN with NULL-safe subquery (explicit filter makes it safe and fast)
SELECT u.id, u.email
FROM users u
WHERE u.id NOT IN (
SELECT user_id FROM orders WHERE user_id IS NOT NULL
);
-- Anti-join in JPA: NOT EXISTS via Specifications or @Query
@Query("""
SELECT u FROM User u
WHERE NOT EXISTS (
SELECT o FROM Order o WHERE o.user = u
)
""")
List<User> findUsersWithNoOrders();Key Points to Remember
- 1Anti-join returns rows from A with no match in B — "A minus B" semantics.
- 2NOT EXISTS is the safest and typically fastest — use as the default.
- 3NOT IN returns zero rows if the subquery contains any NULL — a common silent bug.
- 4LEFT JOIN IS NULL is equivalent to NOT EXISTS when the join column is non-nullable.
- 5PostgreSQL implements NOT EXISTS as Hash Anti Join — short-circuits on first match.
- 6Always add WHERE subquery_col IS NOT NULL when using NOT IN to be safe.
Interview Questions
Sign in to ask AriaWhat is the difference between NOT IN and NOT EXISTS when the subquery has NULLs?
Write the same anti-join three ways and explain when you would choose each.
How does PostgreSQL implement NOT EXISTS internally?
Why does NOT IN return no rows when the subquery includes a NULL value?
Ask Aria about Anti-JOIN Pattern
Your personal AI tutor — ask anything about this concept
Revision Status
Personal Notes
Sign in to save personal notes for this topic.
Discussion
Sign in to join the discussion.