Eric Campbell 6 min read Engineering

SQL - Anti Join (NOT EXISTS / NOT IN)

Anti joins solve a specific problem: finding rows in one table that have no corresponding match in another table. Unlike regular joins that combine matching data, anti joins return only the 'lonely'...

Key Insights

  • NOT EXISTS handles NULL values safely while NOT IN can silently return zero rows when NULLs exist in the subquery—this single difference makes NOT EXISTS the safer default choice.
  • Modern query optimizers often generate identical execution plans for NOT EXISTS, NOT IN, and LEFT JOIN anti-patterns, but understanding the semantic differences matters for correctness.
  • Always filter out NULLs explicitly when using NOT IN, or better yet, default to NOT EXISTS and avoid the mental overhead entirely.

Introduction to Anti Joins

Anti joins solve a specific problem: finding rows in one table that have no corresponding match in another table. Unlike regular joins that combine matching data, anti joins return only the “lonely” rows—the ones left behind.

You’ll reach for anti joins constantly in real applications:

  • Finding customers who haven’t placed orders
  • Identifying products never purchased
  • Detecting orphaned records after failed deletions
  • Building exclusion lists for marketing campaigns
  • Validating referential integrity before migrations

SQL doesn’t have explicit ANTI JOIN syntax like some languages offer. Instead, you implement the pattern using NOT EXISTS, NOT IN, or a LEFT JOIN with a NULL check. Each approach has trade-offs that matter in production code.

NOT EXISTS Syntax and Mechanics

NOT EXISTS uses a correlated subquery—a subquery that references columns from the outer query. The database evaluates the subquery for each row in the outer table, returning that row only when the subquery produces zero results.

-- Find customers who have never placed an order
SELECT c.customer_id, c.email, c.created_at
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

The correlation happens in WHERE o.customer_id = c.customer_id. For each customer, the database checks whether any orders exist with that customer ID. If none exist, the customer appears in the results.

The SELECT 1 inside the subquery is a convention. The actual selected value doesn’t matter—the database only cares whether any rows exist. Some developers write SELECT * or SELECT o.id, but SELECT 1 signals intent clearly: we’re checking for existence, not retrieving data.

The execution model matters for understanding behavior. Conceptually, the database processes each outer row and runs the inner query. Modern optimizers transform this into more efficient operations, but the row-by-row mental model helps when reasoning about correctness.

NOT IN Syntax and Mechanics

NOT IN takes a different approach. Instead of a correlated subquery, it builds a list of values and checks whether the outer column appears in that list.

-- Same query using NOT IN
SELECT c.customer_id, c.email, c.created_at
FROM customers c
WHERE c.customer_id NOT IN (
    SELECT o.customer_id
    FROM orders o
);

The syntax reads naturally: “select customers whose ID is not in the list of customer IDs from orders.” For developers coming from programming languages, this feels intuitive—it’s similar to checking if value not in collection.

The subquery runs once, producing a set of values. The outer query then filters rows whose customer_id doesn’t appear in that set. This non-correlated approach seems simpler, and for many cases, it works identically to NOT EXISTS.

But there’s a trap waiting.

The NULL Trap with NOT IN

This section could save you hours of debugging. When the subquery in NOT IN contains any NULL values, the entire NOT IN clause returns no rows. Not wrong rows—zero rows.

Here’s why. SQL uses three-valued logic: TRUE, FALSE, and UNKNOWN. When you compare anything to NULL, the result is UNKNOWN, not FALSE. The expression 5 NOT IN (1, 2, NULL) evaluates like this:

5 <> 1  → TRUE
5 <> 2  → TRUE  
5 <> NULL → UNKNOWN

TRUE AND TRUE AND UNKNOWN → UNKNOWN

Since UNKNOWN isn’t TRUE, the row gets excluded. This happens for every row in your outer table.

-- Setup: orders table with a NULL customer_id (perhaps from a data import bug)
INSERT INTO orders (order_id, customer_id, total) VALUES 
(1, 100, 50.00),
(2, 101, 75.00),
(3, NULL, 25.00);  -- Orphaned order with NULL customer_id

-- This returns ZERO rows, even for customers 102, 103, etc.
SELECT c.customer_id, c.email
FROM customers c
WHERE c.customer_id NOT IN (
    SELECT o.customer_id FROM orders o
);

-- NOT EXISTS handles this correctly
SELECT c.customer_id, c.email
FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.customer_id = c.customer_id
);

NOT EXISTS doesn’t have this problem because the correlation o.customer_id = c.customer_id evaluates to UNKNOWN when o.customer_id is NULL, which doesn’t satisfy the WHERE clause. The NULL row simply doesn’t match, which is the behavior you want.

If you must use NOT IN, explicitly filter NULLs:

SELECT c.customer_id, c.email
FROM customers c
WHERE c.customer_id NOT IN (
    SELECT o.customer_id 
    FROM orders o 
    WHERE o.customer_id IS NOT NULL
);

But this adds cognitive overhead. You must remember to add this filter every time, and you must trust that future developers maintaining the code understand why it’s there.

Performance Comparison

Developers often ask which approach is faster. The honest answer: it depends on your database, version, indexes, and data distribution. Modern optimizers are remarkably good at recognizing these patterns and generating equivalent plans.

-- PostgreSQL: Compare execution plans
EXPLAIN ANALYZE
SELECT c.customer_id
FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);

EXPLAIN ANALYZE  
SELECT c.customer_id
FROM customers c
WHERE c.customer_id NOT IN (
    SELECT o.customer_id FROM orders o WHERE o.customer_id IS NOT NULL
);

In PostgreSQL, both queries typically produce an “Anti Join” node in the execution plan. The optimizer recognizes the anti-join pattern regardless of syntax.

->  Hash Anti Join  (cost=... rows=...)
      Hash Cond: (c.customer_id = o.customer_id)

SQL Server shows similar behavior, often converting both patterns to the same physical operator. MySQL has historically handled these differently, with NOT EXISTS sometimes performing better on older versions, but recent releases have improved NOT IN optimization.

Index usage matters more than syntax choice. Ensure you have indexes on the join columns:

CREATE INDEX idx_orders_customer_id ON orders(customer_id);

With proper indexes, both approaches use index seeks or scans efficiently. Without indexes, both degrade to full table scans.

For large datasets, test both approaches with your actual data. Execution plans can vary based on statistics, and the optimizer might choose differently based on estimated row counts.

LEFT JOIN Alternative

A third pattern achieves the same result using LEFT JOIN with a NULL check:

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

This works because LEFT JOIN preserves all rows from the left table, filling in NULL for unmatched right-table columns. Filtering for NULL in the join column gives us only the unmatched rows.

Some developers find this more readable—it explicitly shows the join relationship. Others find it confusing because you’re joining tables and then filtering for non-matches.

Performance is typically equivalent to the other approaches. The optimizer recognizes this pattern too:

EXPLAIN ANALYZE
SELECT c.customer_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;

One advantage: LEFT JOIN naturally handles the NULL issue since you’re checking for NULL in the result, not in a subquery list.

A disadvantage: you must be careful about which column you check for NULL. Use a column that’s guaranteed non-nullable in the joined table (like the primary key), or you might get false positives.

Best Practices and Recommendations

After years of writing and reviewing SQL, here’s my practical advice:

Default to NOT EXISTS. It handles NULL values correctly without extra thought. It clearly expresses intent—“where no matching row exists.” It performs well across all major databases.

Use NOT IN only for static lists. When the list is hardcoded values you control, NOT IN reads cleanly:

SELECT * FROM products 
WHERE category NOT IN ('discontinued', 'archived', 'draft');

No NULL risk here because you wrote the list.

Avoid NOT IN with subqueries unless you’re certain about NULLs. If you use it, add the IS NOT NULL filter and leave a comment explaining why.

Consider LEFT JOIN for complex conditions. When you need to check multiple columns or add conditions to the anti-join logic, LEFT JOIN sometimes reads more clearly:

SELECT c.customer_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id 
    AND o.status = 'completed'
    AND o.created_at > '2024-01-01'
WHERE o.order_id IS NULL;

Database-specific notes:

  • PostgreSQL: All three approaches optimize well. Use whichever reads best.
  • MySQL: Recent versions (8.0+) handle all patterns efficiently. Older versions sometimes favored NOT EXISTS.
  • SQL Server: Excellent optimization for all patterns. NOT EXISTS is idiomatic in SQL Server codebases.
  • Oracle: All approaches work well. Oracle developers often prefer NOT EXISTS.

The performance differences between these patterns have largely disappeared in modern databases. Choose based on correctness guarantees and code clarity. NOT EXISTS wins on both counts for most situations.

Liked this? There's more.

Every week: one practical technique, explained simply, with code you can use immediately.