Use EXISTS and NOT EXISTS for Relationship Checks in SQL

Many SQL queries do not actually need data from a related table. They only need to answer a yes-or-no question:

• Does this customer have at least one paid order?
• Is this project missing an active owner?
• Does any inventory row satisfy this product requirement?

A common first attempt is to join the tables and then remove duplicates. That can work, but it makes the query produce more rows than the problem requires and then asks a later operation to repair the result.

EXISTS expresses the relationship check directly. NOT EXISTS expresses its opposite.

The useful mental model is:

JOIN        -> combine matching rows
EXISTS      -> keep an outer row if at least one match exists
NOT EXISTS  -> keep an outer row if no match exists

Database literature often calls the EXISTS shape a semi-join and the NOT EXISTS shape an anti-join. Those names describe the relationship being computed; you normally write the query with EXISTS or NOT EXISTS.

This article uses SQL that works in PostgreSQL and SQLite. Query planners can transform these expressions internally in different ways, so the article focuses on query semantics rather than assuming a particular execution strategy.

Start with a relationship check that a JOIN can distort

Suppose you have customers and orders:

CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    status TEXT NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

The requirement is simple: return each customer who has at least one paid order.

A join looks natural:

SELECT
    c.customer_id,
    c.name
FROM customers AS c
JOIN orders AS o
  ON o.customer_id = c.customer_id
WHERE o.status = 'paid';

The problem is cardinality. If one customer has three paid orders, the join produces three matching result rows for that customer.

You can add DISTINCT:

SELECT DISTINCT
    c.customer_id,
    c.name
FROM customers AS c
JOIN orders AS o
  ON o.customer_id = c.customer_id
WHERE o.status = 'paid';

That may return the desired rows, but it describes the task indirectly:

combine every matching customer/order pair
        |
        v
remove duplicate customer rows

The requirement was never “combine these rows.” It was “keep the customer if a match exists.”

Write the smallest useful EXISTS query

EXISTS takes a subquery and evaluates to true when that subquery returns at least one row.

The direct query is:

SELECT
    c.customer_id,
    c.name
FROM customers AS c
WHERE EXISTS (
    SELECT 1
    FROM orders AS o
    WHERE o.customer_id = c.customer_id
      AND o.status = 'paid'
);

The inner query references c.customer_id from the outer query. That makes it a correlated subquery: its condition depends on the current outer row.

Read it from the outside in:

for this customer c
    is there an order o
    where o belongs to c
    and o is paid?

If yes, the customer survives the WHERE clause. If no, it does not.

Each customer can appear at most once because the outer query reads from customers only once. Ten matching orders still answer the same Boolean question: yes, at least one match exists.

Understand why SELECT 1 is enough

Inside an EXISTS test, the selected values normally do not matter. What matters is whether the subquery returns a row.

That is why this convention is common:

WHERE EXISTS (
    SELECT 1
    FROM orders AS o
    WHERE o.customer_id = c.customer_id
);

This communicates intent: the subquery is checking existence, not retrieving a value.

Writing SELECT o.order_id would usually have the same existence semantics:

WHERE EXISTS (
    SELECT o.order_id
    FROM orders AS o
    WHERE o.customer_id = c.customer_id
);

The first form is clearer because readers do not have to wonder whether order_id is used elsewhere.

Do not infer from SELECT 1 that the database must materialize a column full of ones. EXISTS is a Boolean expression, and query execution is an optimizer concern.

Put every condition that defines the match inside EXISTS

A relationship often has more than one condition.

Suppose “active customer” means a customer with at least one paid order:

WHERE EXISTS (
    SELECT 1
    FROM orders AS o
    WHERE o.customer_id = c.customer_id
      AND o.status = 'paid'
);

Both predicates are part of the matching relationship.

This is different from asking whether the customer has any order and separately applying a condition somewhere else. Keep the predicates that define the qualifying related row together inside the subquery.

That makes the query easier to read and prevents accidental changes in meaning during maintenance.

Use NOT EXISTS when the requirement is absence

Now reverse the question: return customers who have no paid orders.

The direct query is:

SELECT
    c.customer_id,
    c.name
FROM customers AS c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders AS o
    WHERE o.customer_id = c.customer_id
      AND o.status = 'paid'
);

Read it literally:

keep this customer
if no paid order belonging to this customer exists

This anti-join shape is useful for missing relationships:

• accounts with no active subscription;
• products with no inventory in a warehouse;
• jobs with no successful run;
• parent rows with no qualifying children.

The important word is qualifying. A customer may have several cancelled orders and still satisfy “has no paid orders.”

Be careful with the LEFT JOIN anti-join pattern

Another common absence query uses LEFT JOIN:

SELECT
    c.customer_id,
    c.name
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.customer_id
 AND o.status = 'paid'
WHERE o.order_id IS NULL;

This can be correct. The join tries to find qualifying paid orders, and unmatched customers receive null-extended columns from orders. Testing a non-nullable matched column such as the primary key then identifies the unmatched rows.

However, this form has more moving parts:

1. the qualification condition must stay in the ON clause;
2. the null test must use a column whose matched value cannot itself be NULL;
3. readers must recognize the outer-join anti-join idiom.

NOT EXISTS often states the business question more directly.

Do not rewrite every valid LEFT JOIN ... IS NULL query mechanically. A left join is still useful when you also need columns or aggregates from the joined rows.

Understand the NOT IN NULL trap

NOT IN can look like a compact substitute for NOT EXISTS:

SELECT
    c.customer_id,
    c.name
FROM customers AS c
WHERE c.customer_id NOT IN (
    SELECT o.customer_id
    FROM orders AS o
);

If the subquery can produce NULL, this can behave differently from many developers’ intuition.

Consider:

SELECT 5 NOT IN (1, 2, NULL);

The comparisons are conceptually similar to:

5 <> 1
AND 5 <> 2
AND 5 <> NULL

The last comparison is unknown, not true. With no false comparison to settle the expression, the overall result is unknown. A WHERE clause keeps only rows whose condition is true, so the row is filtered out.

That means one unexpected NULL on the right side can make a NOT IN anti-match return no rows that would otherwise qualify.

NOT EXISTS does not have the same problem because it asks whether a row satisfying an explicit predicate exists:

WHERE NOT EXISTS (
    SELECT 1
    FROM orders AS o
    WHERE o.customer_id = c.customer_id
);

A row whose o.customer_id is NULL does not equal a non-null c.customer_id, so it simply does not satisfy that correlation predicate.

If the schema guarantees the subquery column is NOT NULL, NOT IN may be perfectly valid. The key is to choose it knowingly rather than assuming it is interchangeable with NOT EXISTS under nullable data.

NULL in the selected columns does not change EXISTS

There is a different NULL rule worth separating from NOT IN.

This expression is true because the subquery returns one row:

SELECT EXISTS (
    SELECT NULL
);

For EXISTS, the contents of the returned row do not determine the result. The row’s existence does.

That is another reason to keep this distinction clear:

EXISTS  -> cares whether a row exists
NOT IN  -> compares values, so NULL participates in comparison logic

Use EXISTS when the relationship is many-to-many

EXISTS is especially useful when a relationship passes through a junction table.

Suppose projects and users are connected through memberships:

CREATE TABLE project_memberships (
    project_id INTEGER NOT NULL,
    user_id INTEGER NOT NULL,
    role TEXT NOT NULL,
    PRIMARY KEY (project_id, user_id)
);

To return projects that have at least one owner:

SELECT
    p.project_id,
    p.name
FROM projects AS p
WHERE EXISTS (
    SELECT 1
    FROM project_memberships AS pm
    WHERE pm.project_id = p.project_id
      AND pm.role = 'owner'
);

The query does not need membership columns in its output. The junction table exists only to answer the relationship question, which is exactly where EXISTS is expressive.

Combine multiple independent existence rules

Sometimes a row must satisfy several relationship conditions.

For example, return projects that have an owner but no unresolved incident:

SELECT
    p.project_id,
    p.name
FROM projects AS p
WHERE EXISTS (
    SELECT 1
    FROM project_memberships AS pm
    WHERE pm.project_id = p.project_id
      AND pm.role = 'owner'
)
AND NOT EXISTS (
    SELECT 1
    FROM incidents AS i
    WHERE i.project_id = p.project_id
      AND i.resolved_at IS NULL
);

The two subqueries model two separate facts:

owner exists
AND
unresolved incident does not exist

Keeping them separate is often clearer than constructing one large join graph whose duplicates and null behavior must then be reasoned about together.

Correlation is part of correctness

A dangerous mistake is forgetting the condition that connects the subquery to the outer row.

Incorrect:

SELECT
    c.customer_id,
    c.name
FROM customers AS c
WHERE EXISTS (
    SELECT 1
    FROM orders AS o
    WHERE o.status = 'paid'
);

This asks:

does any paid order exist anywhere?

If the answer is yes, every customer passes the condition.

The preferred query correlates the rows:

SELECT
    c.customer_id,
    c.name
FROM customers AS c
WHERE EXISTS (
    SELECT 1
    FROM orders AS o
    WHERE o.customer_id = c.customer_id
      AND o.status = 'paid'
);

When reviewing an EXISTS query, identify the predicate that ties the inner row to the current outer row. If you cannot find one, verify that an uncorrelated existence test is truly what the query intends.

EXISTS does not mean “count is greater than zero”

You could express an existence check with a correlated count:

WHERE (
    SELECT COUNT(*)
    FROM orders AS o
    WHERE o.customer_id = c.customer_id
      AND o.status = 'paid'
) > 0

This can produce the same logical answer, but it asks for a count when the application does not need one.

EXISTS states the weaker requirement: determine whether at least one qualifying row exists.

That distinction gives the database more direct semantic information about what the query needs. PostgreSQL, for example, documents that an EXISTS subquery will generally be executed only far enough to determine whether at least one row exists. Do not turn that into a universal promise about a specific physical plan: optimizers are free to transform queries while preserving results.

Use COUNT when the count itself is needed. Use EXISTS when the answer is Boolean.

Index the predicates that find the relationship, when the workload needs it

An EXISTS query is not automatically fast.

The database still needs a way to find qualifying inner rows. For this query:

WHERE EXISTS (
    SELECT 1
    FROM orders AS o
    WHERE o.customer_id = c.customer_id
      AND o.status = 'paid'
);

an index beginning with the relationship key may help:

CREATE INDEX idx_orders_customer_status
    ON orders (customer_id, status);

Whether that index improves the query depends on the database, data distribution, table size, selectivity, statistics, and competing workloads.

The order of index columns also matters to other queries. Do not add an index only because an example suggests one. Measure the actual workload and inspect the database’s query plan.

The defensible performance rule is:

EXISTS expresses the requirement clearly;
indexes and the optimizer determine how cheaply the database can prove it.

Know when a JOIN is the better tool

EXISTS is not a replacement for joins.

Use a regular join when you actually need data from both sides:

SELECT
    c.name,
    o.order_id,
    o.status
FROM customers AS c
JOIN orders AS o
  ON o.customer_id = c.customer_id
WHERE o.status = 'paid';

Multiple rows per customer are correct here because each result row represents a customer/order pair.

A join is also appropriate when you need to aggregate related rows:

SELECT
    c.customer_id,
    COUNT(*) AS paid_order_count
FROM customers AS c
JOIN orders AS o
  ON o.customer_id = c.customer_id
WHERE o.status = 'paid'
GROUP BY c.customer_id;

Choose the construct that matches the result’s meaning:

need related columns or one row per match -> JOIN
need only "at least one match?"           -> EXISTS
need only "no match?"                     -> NOT EXISTS
need the number of matches                -> aggregate

Common mistakes

Joining and adding DISTINCT by habit

DISTINCT can hide duplicate rows produced by a one-to-many join. If the real question is existence, start with EXISTS instead of generating duplicates first.

Moving an anti-join condition into WHERE

With a LEFT JOIN, moving a predicate from ON to WHERE can reject the null-extended unmatched rows and change the query into different semantics. Keep qualifying conditions in the join predicate when using the LEFT JOIN ... IS NULL anti-join pattern.

Assuming NOT IN and NOT EXISTS are always interchangeable

They differ when nullable values participate. Check schema nullability and the exact semantics before substituting one for the other.

Forgetting the correlation predicate

An uncorrelated EXISTS tests a global condition. A correlated EXISTS tests a condition for the current outer row.

Assuming syntax determines the physical plan

A database may rewrite an existence query into an internal semi-join, use an index lookup, choose a hash-based strategy, or use another plan. Treat EXISTS as a semantic choice first. Inspect the plan when performance matters.

When to use EXISTS and when not to

Reach for EXISTS when the outer row should survive based only on whether at least one qualifying related row exists.

Reach for NOT EXISTS when survival depends on the absence of such a row, especially when nullable data would make NOT IN harder to reason about.

Prefer a join when each matching relationship should produce output or when you need columns from the related row. Prefer an aggregate when you need the number, sum, minimum, maximum, or another aggregate property of the related rows.

The practical question is not “which SQL construct is shortest?” It is “what does one result row represent?”

When one result row should represent one outer entity and the related table only answers yes or no, EXISTS and NOT EXISTS make that intent explicit.