SQL Anti Join: Find Rows With No Match

Your marketing team wants to run a campaign for customers who have never placed an order. You open your analytics tool, pull the customer list, then realise you need to exclude everyone who already appears in orders. That exclusion, finding rows in one table with no match in another, is exactly what an anti join does.

What an Anti Join Does

An anti join is not a separate JOIN type in SQL. It is a pattern built from existing syntax. The goal is always the same: return rows from the left table where no matching row exists in the right table.

The most common way to write it uses a [LEFT JOIN](learn/sql-left-join) with a NULL check in WHERE:

SELECT c.customer_id, c.first_name, c.last_name
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.customer_id IS NULL

Read this in two steps. The LEFT JOIN brings back every customer, with order data attached where it exists and NULL where it does not. The WHERE clause then keeps only the rows where the join found nothing, which are the customers with no orders.

You are not filtering on the customer's data at all. You are filtering on the absence of a join match.

The Three Patterns

All three patterns produce the same result. Knowing all three helps you read other people's code, and choose the right one for your situation.

Pattern 1: LEFT JOIN with WHERE NULL

This is the most common form. It is readable, works in every SQL dialect, and performs well with indexes on the join columns.

SELECT c.customer_id, c.first_name, c.email
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.customer_id IS NULL

Pattern 2: NOT EXISTS

NOT EXISTS uses a [subquery](learn/sql-subqueries). For each row in customers, SQL checks whether a matching row exists in orders. If none is found, the row is included.

SELECT c.customer_id, c.first_name, c.email
FROM customers c
WHERE NOT EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.customer_id
)

The SELECT 1 inside the subquery is not returning data. It is just checking for the existence of a row. You could write SELECT * and get the same result, but SELECT 1 makes the intent clearer.

Pattern 3: NOT IN

NOT IN compares a column against a list of values returned by a subquery.

SELECT c.customer_id, c.first_name, c.email
FROM customers c
WHERE c.customer_id NOT IN (
  SELECT o.customer_id
  FROM orders o
)

This looks simple, but it has a serious flaw. If the subquery returns even one NULL value, the entire NOT IN check fails silently and returns no rows. In a real database where orders.customer_id could theoretically be NULL, this query would return nothing at all. Use NOT EXISTS or LEFT JOIN with WHERE NULL instead.

More Examples Against ecommerce.db

Anti joins work across any pair of related tables. Here are two more cases.

Products that have never been ordered:

SELECT p.product_id, p.product_name, p.category_id
FROM products p
LEFT JOIN order_items oi ON p.product_id = oi.product_id
WHERE oi.product_id IS NULL

Orders that have no payment recorded:

SELECT o.order_id, o.customer_id, o.order_date, o.total_amount
FROM orders o
LEFT JOIN payments pay ON o.order_id = pay.order_id
WHERE pay.order_id IS NULL

Both follow exactly the same structure. Table on the left, joined table on the right, NULL check in WHERE.

How Anti Joins Connect to Other Concepts

An anti join is the logical opposite of an [INNER JOIN](learn/sql-inner-join). An INNER JOIN keeps only matched rows. An anti join keeps only unmatched rows.

It is also related to [set operations](learn/sql-except). In databases that support it, EXCEPT returns rows from the first query that do not appear in the second. An anti join does the same thing but stays inside the JOIN syntax, which is more flexible when you need to SELECT specific columns.

Common Mistakes

1. Checking the wrong table's column in WHERE

The NULL check must be on a column from the joined (right) table, not from the left table.

-- wrong
SELECT c.customer_id, c.first_name
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE c.customer_id IS NULL

c.customer_id is never NULL because it comes from the base table. This returns no rows.

-- correct
SELECT c.customer_id, c.first_name
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.customer_id IS NULL

Check the column from the right table. That is the one that becomes NULL when no match exists.

2. Using NOT IN with a nullable column

If the subquery can return a NULL, NOT IN breaks entirely.

-- wrong
SELECT p.product_id, p.product_name
FROM products p
WHERE p.product_id NOT IN (
  SELECT oi.product_id FROM order_items oi
)

If any row in order_items has a NULL product_id, this returns zero rows regardless of what is in products.

-- correct
SELECT p.product_id, p.product_name
FROM products p
LEFT JOIN order_items oi ON p.product_id = oi.product_id
WHERE oi.product_id IS NULL

LEFT JOIN with WHERE NULL handles NULLs correctly.

3. Adding a WHERE filter before the anti join check

Filtering on the right table inside the WHERE clause instead of the ON clause turns a LEFT JOIN into an INNER JOIN.

-- wrong
SELECT c.customer_id, c.first_name
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.status = 'pending'
  AND o.customer_id IS NULL

If o.status = 'pending' is in WHERE, SQL discards rows where o is NULL before the NULL check runs. No rows pass.

-- correct
SELECT c.customer_id, c.first_name
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
  AND o.status = 'pending'
WHERE o.customer_id IS NULL

Move the filter into the ON clause. The LEFT JOIN still produces NULLs for non-matching rows, and the WHERE check works as expected.

Quick Recap

• An anti join returns rows from the left table that have no matching row in the right table.
• SQL has no ANTI JOIN keyword. You write it with LEFT JOIN plus WHERE NULL, NOT EXISTS, or NOT IN.
• The NULL check in WHERE must target a column from the joined table, not the base table.
• NOT IN breaks silently when the subquery returns any NULL values. Use LEFT JOIN with WHERE NULL or NOT EXISTS instead.
• When filtering on the joined table, put conditions in the ON clause, not in WHERE, to preserve the anti join behaviour.
• Anti joins are the logical opposite of INNER JOINs: one keeps matches, the other keeps non-matches.

SELECT e.employee_id, e.first_name, e.last_name
FROM employees e
LEFT JOIN orders o ON e.employee_id = o.employee_id
WHERE o.employee_id IS NULL

SELECT p.product_id, p.product_name
FROM products p
WHERE NOT EXISTS (
  SELECT 1
  FROM order_items oi
  WHERE oi.product_id = p.product_id
)

SELECT o.order_id, o.customer_id, o.total_amount
FROM orders o
LEFT JOIN payments pay ON o.order_id = pay.order_id
WHERE pay.order_id IS NULL
ORDER BY o.total_amount DESC

SELECT c.customer_id, c.first_name, c.last_name, c.city
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.customer_id IS NULL
  AND c.country = 'Germany'

SELECT p.product_id, p.product_name, p.price
FROM products p
LEFT JOIN order_items oi ON p.product_id = oi.product_id
WHERE oi.product_id IS NULL
  AND p.price > 100
ORDER BY p.price DESC