SQL EXISTS and NOT EXISTS: finding matching and non-matching rows

Sometimes you only need to know whether a match exists

In the previous article, we used joins to combine related information from different tables. Consider the same customers and orders tables:

Suppose the requirement is “return customers who have placed at least one order.” We do not need any columns from orders. We only need to answer one question for each customer: does at least one matching order exist? SQL provides EXISTS for exactly this kind of problem.

EXISTS: keep a row when a related row exists

SELECT
    c.customer_id,
    c.customer_name
FROM customers AS c
WHERE EXISTS (
    SELECT 1
    FROM orders AS o
    WHERE o.customer_id = c.customer_id
)
ORDER BY c.customer_id;

Esha does not appear because she has no matching order. For each customer, SQL conceptually asks “does an order exist where customer_id matches?” and keeps the customer when the answer is yes.

Why SELECT 1 is common inside EXISTS

The 1 is not being returned as part of the final query result. EXISTS only asks whether the subquery produces at least one row, so SELECT 1 and SELECT * express the same existence test. Using SELECT 1 makes the intention clear: I only care whether a row exists.

The correlation connects the two queries

Look closely at WHERE o.customer_id = c.customer_id. The subquery references o.customer_id from orders and c.customer_id from the outer customers query. This connects the existence test to the customer currently being considered. For Asha, c.customer_id = 1, so the subquery becomes conceptually SELECT 1 FROM orders WHERE customer_id = 1;: rows exist, so Asha qualifies. For Esha, c.customer_id = 5: no row exists, so she does not qualify. This relationship between the inner and outer query is commonly called a correlated subquery.

Adding conditions to the match

Suppose the requirement becomes “return customers who have placed at least one paid order.” The existence condition now has two parts: the order belongs to the customer, and the order is paid.

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

Dev has an order, but it is cancelled. Esha has no order. Neither has a matching paid order. Returning rows from the left side when at least one matching row exists on the right is often described as a semi-join, and EXISTS is one common way to express it.

Using JOIN for an existence question can multiply rows

Suppose the requirement is return customers who have at least one paid order. Someone writes the query below. The SQL looks reasonable. But what does it return?

SELECT
    c.customer_id,
    c.customer_name
FROM customers AS c
INNER JOIN orders AS o
    ON o.customer_id = c.customer_id
WHERE o.status = 'paid'
ORDER BY c.customer_id;

The fix: use EXISTS for an existence question

Write the requirement directly. The outer query contains one row for Asha, and EXISTS only determines whether that row should remain; it does not create another output row for every matching order.

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

Why not just add DISTINCT?

You could write SELECT DISTINCT ... INNER JOIN ... WHERE o.status = 'paid'. This may produce the required output. But if the actual question is “does a matching row exist?”, then EXISTS communicates that requirement more directly. Using JOIN and then DISTINCT means “create all matching combinations, then remove duplicate customer rows,” while using EXISTS means “keep the customer when a match exists.” A useful rule is: if you do not need columns from the matching table and only care whether a match exists, consider EXISTS.

NOT EXISTS: keep rows when no match exists

Now consider the opposite requirement: “return customers who have never placed an order.” For each customer, we want no matching order to exist.

SELECT
    c.customer_id,
    c.customer_name
FROM customers AS c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders AS o
    WHERE o.customer_id = c.customer_id
)
ORDER BY c.customer_id;

For Asha, an order exists, so NOT EXISTS is FALSE and she is removed. For Esha, no order exists, so NOT EXISTS is TRUE and she remains. Returning left-side rows only when no matching row exists is commonly called an anti-join.

Finding customers without a particular kind of match

NOT EXISTS becomes especially useful when the absence condition is more specific. “Return customers who have no paid orders” does not mean “customers with no orders”: Dev has an order (cancelled) and should qualify, and Esha qualifies because she has no orders at all.

SELECT
    c.customer_id,
    c.customer_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'
)
ORDER BY c.customer_id;

A subtle difference: “no paid orders” vs “has a non-paid order”

LEFT JOIN … IS NULL, the correlation trap, and NULL

Another common way to find customers without orders is an outer join that keeps rows whose match came back empty. Customers with orders receive matching orders rows; Esha receives order_id = NULL because the LEFT JOIN found no match, so WHERE o.order_id IS NULL keeps only unmatched customers.

SELECT
    c.customer_id,
    c.customer_name
FROM customers AS c
LEFT JOIN orders AS o
    ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;

Both NOT EXISTS and LEFT JOIN … IS NULL can correctly express anti-matching. For pure existence or non-existence questions, EXISTS and NOT EXISTS often make the intent especially clear. Performance can depend on the database, schema, indexes, and optimizer, so choose based primarily on correctness and clarity unless measurement shows a reason to do otherwise.

The featured correlation trap

Consider WHERE EXISTS (SELECT 1 FROM orders AS o WHERE o.status = 'paid'). Notice what is missing: there is no condition connecting orders to the current customer. The subquery simply asks “does any paid order exist anywhere in the orders table?” The answer is yes, so EXISTS is TRUE for every customer, and even Esha appears.

EXISTS and NULL

One advantage of EXISTS is that it is concerned only with whether the subquery returns rows. A matching order can contain a nullable column such as discount_amount = NULL, and that does not prevent the order row itself from existing. This is different from comparison-based patterns where NULL can introduce UNKNOWN. Another way someone might try to find customers without orders is c.customer_id NOT IN (SELECT o.customer_id FROM orders o). With clean, non-NULL values this may work, but as covered in the NULL article, NOT IN becomes dangerous when the subquery can return NULL: a single NULL can cause otherwise non-matching comparisons to evaluate to UNKNOWN. NOT EXISTS expresses the anti-match directly and is often easier to reason about safely.

When to consider EXISTS or NOT EXISTS

Consider EXISTS or NOT EXISTS when the business requirement contains phrases such as customers who have at least one order, products that have ever been purchased, accounts that have a matching transaction, customers who have no orders, products that have never been sold, or records that do not exist in another dataset. Watch for these common mistakes:

Start with the business question

One more prediction

Requirement: return customers who have no paid orders. Which query correctly expresses the requirement?

-- Option A
WHERE EXISTS (SELECT 1 FROM orders o
  WHERE o.customer_id = c.customer_id
    AND o.status <> 'paid')

-- Option B
WHERE NOT EXISTS (SELECT 1 FROM orders o
  WHERE o.customer_id = c.customer_id
    AND o.status = 'paid')

-- Option C
INNER JOIN orders o ON o.customer_id = c.customer_id
WHERE o.status <> 'paid'

-- Option D
WHERE NOT EXISTS (SELECT 1 FROM orders o
  WHERE o.status = 'paid')

Lock these in

Summary

Use EXISTS when the business question is “does at least one matching row exist?” This is a common semi-join pattern. Use NOT EXISTS when the question is “does no matching row exist?” This is a common anti-join pattern. The central difference from a normal join is result shape: a join can produce one output row for every matching combination, while EXISTS and NOT EXISTS keep or remove the outer row based on whether a match exists.

Next, we will learn how to combine complete query results using UNION, UNION ALL, INTERSECT, and EXCEPT.