An Anti JOIN returns rows from one dataset for which no matching row exists in another dataset. It is commonly implemented using NOT EXISTS or LEFT JOIN with IS NULL.
Finding missing relationships is essential for data validation and business reporting.
Find customers who have never placed an order.
SELECT
c.CustomerID,
c.CustomerName
FROM Customers c
WHERE NOT EXISTS (
SELECT 1
FROM Orders o
WHERE o.CustomerID = c.CustomerID
);Used for inactive customers, missing transactions, unmatched records, and data-quality checks.
- Returns non-matching rows.
- NOT EXISTS is a common implementation.
- LEFT JOIN with IS NULL can also implement the pattern.
- Useful for missing-data analysis.
- NULL behavior makes NOT EXISTS safer than some NOT IN cases.
What is an Anti JOIN?
It returns rows from one table that do not have a matching row in another table.
What SQL patterns can implement an Anti JOIN?
NOT EXISTS and LEFT JOIN with IS NULL are common approaches.