Technical
SQL anti-join patterns: finding missing rows without surprises
Published Feb 12, 2026
"Show me what exists in A but not in B" sounds simple, but it can quietly go wrong when nulls, duplicates, or unexpected join keys are involved. The good news: there are a few patterns that are reliable in production.
In this post, we assume a common scenario: you have customers and orders, and you need
customers with no orders.
Option 1 (recommended): LEFT JOIN + IS NULL
Works across most databases and avoids the classic NOT IN null trap.
SELECT c.*
FROM customers c
LEFT JOIN orders o
ON o.customer_id = c.id
WHERE o.customer_id IS NULL;
Tip: ensure orders.customer_id is indexed for speed.
Option 2: NOT EXISTS
Often the clearest expression of intent, and typically optimized well.
SELECT c.*
FROM customers c
WHERE NOT EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.id
);Avoid this unless you understand null behavior: NOT IN
NOT IN can return zero rows if the subquery produces a null. If you must use it, filter nulls explicitly.
SELECT c.*
FROM customers c
WHERE c.id NOT IN (
SELECT o.customer_id
FROM orders o
WHERE o.customer_id IS NOT NULL
);