Checking for related rows with EXISTS and NOT EXISTS

Every customer in our seeded database that has orders would answer EXISTS the same way, so add one temporary customer with none:

We won't create any orders for this customer.

Now we have two useful cases:

Suppose we want customers who have at least one order.

Run:

You should see only customer 1:

The important part is:

For each customer being considered, the subquery asks whether a matching order exists.

If the subquery produces at least one row:

If it produces no rows:

Conceptually:

while:

Look at:

o.customer_id belongs to the inner query.

But:

belongs to the outer query.

The inner query therefore depends on the current customer from the outer query.

This is a correlated subquery.

Conceptually:

Then for another outer row:

Correlation describes this logical dependency.

It doesn't guarantee that PostgreSQL physically executes one complete subquery for every outer row. The planner can transform the query and choose an efficient execution strategy.

You can see that for yourself later in this chapter, where the execution plan for a correlated EXISTS turns out to contain no per-row loop at all.

Inside EXISTS, we wrote:

instead of:

or:

For EXISTS, the selected value normally doesn't matter.

PostgreSQL cares about:

not:

So this:

is a common way of expressing the intent clearly.

You can mentally read it as:

Now suppose we want the opposite:

Customers who have no orders.

Use:

Run:

You should see:

For customer 1, the subquery finds orders:

For customer 10001, it finds none:

Conceptually:

Finding rows with no related records is one of the most useful NOT EXISTS patterns.

Every seeded product already appears in an order item, so add one that does not:

Now find products that have never appeared in an order item:

You should see:

The question is:

If not, keep the product.

The same pattern works for:

Some queries can be expressed using either IN or EXISTS.

For example:

asks:

The EXISTS version is:

which asks:

Both can express the same requirement here.

The difference worth remembering is semantic:

For a question such as:

EXISTS often expresses the intent particularly clearly.

Do not assume one is automatically faster.

PostgreSQL's query planner may produce similar or different execution strategies depending on the query and data.

If performance matters, inspect the execution plan.

Logically, EXISTS doesn't need all matching rows.

As soon as the condition:

has been established, additional matching rows cannot change the result from TRUE to anything else.

This is why EXISTS is a natural expression for existence checks.

However, you should still let PostgreSQL's planner decide how to execute the complete query rather than assuming a particular physical loop or lookup strategy.

There is an important difference between:

and some uses of:

when NULL values are involved.

Consider this small set:

Now run:

You might expect:

because 2 is not equal to 1.

But the set also contains NULL.

SQL cannot determine whether the unknown value represented by NULL might equal 2.

The condition therefore does not evaluate to TRUE.

As a result, the query returns no row.

Conceptually:

This is one of the classic SQL NULL traps, and it follows directly from the three-valued logic you saw in the NULL values chapters.

Now express the requirement directly:

There is no row where:

so NOT EXISTS is TRUE.

The query returns:

For "find rows for which no matching related row exists" problems, NOT EXISTS often avoids the surprising NULL semantics associated with NOT IN.

This doesn't mean:

If you know the values returned by the subquery cannot contain NULL, NOT IN can be perfectly valid.

For example, a subquery returning a column defined as:

does not introduce that particular NULL problem.

The important point is to understand the semantics rather than memorize a blanket rule.

We could solve some existence problems using a join.

For example:

can also identify customers with orders.

But notice why DISTINCT may be necessary.

One customer can have many orders:

A join naturally produces multiple rows.

EXISTS asks a different question:

If that is the actual requirement, EXISTS can communicate the intent directly without producing the matching order rows.

This is a semantic reason to choose EXISTS, not a blanket performance rule.

Avoid rules such as:

PostgreSQL has a cost-based query planner and can transform SQL into different execution plans.

If a query matters for application performance, inspect it:

You should see a plan like this:

Your timing and buffer values will probably differ from the ones shown here.

This plan is worth reading closely. The SQL describes a correlated subquery running once per customer, but the plan contains a Hash Join over a HashAggregate of distinct customer_id values. PostgreSQL scanned orders once, not ten thousand times.

That is the concrete evidence for the earlier claim: correlation describes the logical dependency, not the execution strategy.

Then reason about the plan PostgreSQL actually selected.

Remove the temporary rows created in this chapter:

Use:

Use:

And remember:

The important point is:

`EXISTS` and `NOT EXISTS` express whether a relationship exists without requiring the matching rows themselves to appear in the result.