Introduction

NOT EXISTS is the standard SQL way to return rows from one table only when no matching row exists in another table. It expresses an anti-join clearly, handles NULL safely, and is often easier for the optimizer to reason about than more error-prone alternatives.

Basic Pattern

The outer query selects candidate rows. The correlated subquery checks whether a related row exists for the current candidate. NOT EXISTS keeps the outer row only when the subquery returns zero rows.

1SELECT c.customer_id, c.name
2FROM customers AS c
3WHERE NOT EXISTS (
4    SELECT 1
5    FROM orders AS o
6    WHERE o.customer_id = c.customer_id
7);

This returns customers who have no orders.

The SELECT 1 inside the subquery is conventional. The subquery is not returning data to the outer query; it is only answering yes or no.

How the Correlation Works

The important part is the predicate that ties the inner query to the current row of the outer query:

WHERE o.customer_id = c.customer_id

Without that correlation, you are no longer asking "Does this customer have an order?" You are asking "Does the orders table contain any row at all?" That is a completely different query.

Common Anti-Join Use Cases

Finding records with no related rows is the most common use:

1SELECT p.project_id, p.name
2FROM projects AS p
3WHERE NOT EXISTS (
4    SELECT 1
5    FROM deployments AS d
6    WHERE d.project_id = p.project_id
7);

It is also useful in idempotent insert patterns:

1INSERT INTO feature_flags (flag_key, enabled)
2SELECT 'new_checkout', FALSE
3WHERE NOT EXISTS (
4    SELECT 1
5    FROM feature_flags AS f
6    WHERE f.flag_key = 'new_checkout'
7);

This ensures the insert happens only when the row is missing.

NOT EXISTS vs NOT IN

NOT IN can look similar, but it behaves badly when the subquery can return NULL.

1SELECT c.customer_id
2FROM customers AS c
3WHERE c.customer_id NOT IN (
4    SELECT o.customer_id
5    FROM orders AS o
6);

If orders.customer_id contains NULL, the three-valued logic of SQL can make the whole predicate evaluate in surprising ways. NOT EXISTS avoids that trap because it checks for matching rows directly instead of comparing against a list with possible null semantics.

NOT EXISTS vs LEFT JOIN ... IS NULL

This pattern is also common:

1SELECT c.customer_id, c.name
2FROM customers AS c
3LEFT JOIN orders AS o
4    ON o.customer_id = c.customer_id
5WHERE o.customer_id IS NULL;

It often produces the same result, and many optimizers transform both forms into similar plans. Still, NOT EXISTS usually reads closer to the intent: keep rows for which no related row exists.

Performance Considerations

NOT EXISTS is usually efficient when the join predicate is index-friendly. For the customer and orders example, an index on orders.customer_id is the main thing that matters.

CREATE INDEX idx_orders_customer_id
    ON orders (customer_id);

With the right index, the database can often stop searching as soon as it finds the first match for a given outer row, because existence is all it needs to know.

Common Pitfalls

• Forgetting the correlation predicate and accidentally checking whether the inner table has any rows at all.
• Rewriting the query as LEFT JOIN ... IS NULL and then testing the wrong inner column.
• Using NOT IN on a nullable subquery column and getting unexpected NULL-driven logic.
• Assuming the subquery output columns matter when only existence is being tested.
• Ignoring indexes on the correlation columns and then blaming NOT EXISTS for poor performance.

Summary

• 'NOT EXISTS is the canonical SQL anti-join pattern.'
• It returns outer rows only when the correlated subquery finds no match.
• It is safer than NOT IN when NULL values are possible.
• It often expresses intent more clearly than LEFT JOIN ... IS NULL.
• Good indexes on the correlation columns are the main performance requirement.