SQL EXISTS: filter rows by related records

SQL EXISTS returns true when a correlated subquery finds at least one matching row. Use it to filter parent rows by the presence (or absence) of related child rows without duplicating results the way a JOIN can. Query planners commonly execute EXISTS as a semi-join, so they can stop the subquery work after the first match.

SQL EXISTS Example for Row Existence Filtering

Output:

+------+
| name |
+------+
| Alma |
| Cara |
+------+
2 row(s)

Why EXISTS Returns Alma and Cara

1. Two tables are created: customers holds three people, orders holds three orders linked by customer_id.
2. WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id) runs the subquery once per customer row. If at least one matching order exists, the customer passes the filter.
3. SELECT 1 inside the subquery is a convention — the database ignores the select list and only checks whether any row qualifies.
4. Ben has no orders, so his subquery returns zero rows and EXISTS evaluates to false. Alma and Cara each have at least one order and appear in the result.

Unlike an INNER JOIN, EXISTS never duplicates the outer row. Alma has two orders yet appears once because EXISTS only asks “does a match exist?” — not “how many?”

NOT EXISTS vs NOT IN: The NULL Pitfall

SQL NOT EXISTS (written as NOT EXISTS) is the safe choice for anti-joins on nullable columns. When a subquery returns NULL values, NOT IN produces unexpected results because any comparison with NULL yields unknown:

-- Suppose orders has a NULL customer_id row
-- NOT IN silently drops ALL rows
SELECT name FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);

-- NOT EXISTS still works correctly
SELECT name FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

NOT IN treats id NOT IN (1, 3, NULL) as id <> 1 AND id <> 3 AND id <> NULL. The last comparison is always unknown, so the entire predicate is never true and zero rows return. NOT EXISTS avoids this because it checks row existence, not value equality.

Common Mistakes With SQL EXISTS

Missing correlation predicate

If the subquery isn’t correlated to the outer row, EXISTS becomes a constant test: when any order exists, every customer matches.

-- Wrong: not correlated to the outer row
SELECT c.name FROM customers c
WHERE EXISTS (SELECT 1 FROM orders);

-- Right: correlated on customer_id
SELECT c.name FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

Using JOIN when only existence matters

An INNER JOIN returns one row per match, so customers with multiple orders appear multiple times.

SELECT c.name
FROM customers c
JOIN orders o ON o.customer_id = c.id;

Use EXISTS unless you need columns from orders.

EXISTS vs IN vs JOIN

Rule: use EXISTS for “does a related row exist?” questions. Switch to JOIN when you need data from both sides.

Performance Notes

EXISTS is often planned as a semi-join. An index on the correlation column (orders.customer_id here) lets the engine probe for the first matching row per customer instead of scanning orders. NOT EXISTS benefits from the same index. If EXISTS is slow, check the query plan and whether the correlation column is indexed.

FAQ

Does EXISTS stop after the first match?

Logically, the result becomes true as soon as one qualifying row exists. Many databases implement that as a stop-after-first-match semi-join, but the exact strategy is up to the query planner. Look at the execution plan if you need to confirm what your database does.

Can EXISTS be used in UPDATE and DELETE?

Yes. UPDATE ... WHERE EXISTS (subquery) and DELETE ... WHERE EXISTS (subquery) restrict which rows are modified or removed based on related-row existence. Keep the same correlation predicate you’d use in a SELECT, so each target row checks only its related rows.

What is the difference between EXISTS and IN in SQL?

EXISTS checks whether a (usually correlated) subquery returns any rows. IN compares a value against a result set. EXISTS works naturally with outer-query correlation, and many engines can stop after the first match; IN is a good fit for small literal lists or uncorrelated membership checks. For anti-joins on nullable columns, prefer NOT EXISTS over NOT IN.