EXISTS & NOT EXISTS

EXISTS is a special kind of condition: instead of comparing values, EXISTS (subquery) simply asks whether the subquery would return at least one row for the current outer row. It evaluates to true or false — it does not matter at all what columns or values the subquery selects, only whether any row comes back. A very common, harmless convention is to write SELECT 1 inside an EXISTS subquery, since the actual selected value is never used.

Why EXISTS Can Be More Efficient Than IN

Because EXISTS only cares about row presence, a database can stop scanning as soon as it finds the first matching row for the current outer row — it never needs to keep searching for more matches, and it never needs to build up a full list of values the way IN conceptually does. On large tables this short-circuiting behavior often makes EXISTS the faster and more predictable choice.

SQL

-- Customers who have placed at least one order, using EXISTS
SELECT c.customer_id, c.name
FROM customers c
WHERE EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.customer_id
);

The Classic Use Case: Finding Customers With No Orders

SQL

-- Customers who have NEVER placed an order
SELECT c.customer_id, c.name
FROM customers c
WHERE NOT EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.customer_id
);

NOT EXISTS vs NOT IN — The NULL Trap

NOT IN looks like the natural way to write the same query, but it has a dangerous gotcha: if the subquery's result contains even a single NULL value, NOT IN silently returns an empty result — for every row — instead of the answer you expect. This happens because SQL compares against NULL with three-valued logic, and value <> NULL is neither true nor false, it is unknown, which breaks the whole NOT IN comparison list.

SQL

-- DANGEROUS: if any order has a NULL customer_id, this returns ZERO rows,
-- even though plenty of customers have genuinely never ordered anything
SELECT c.customer_id, c.name
FROM customers c
WHERE c.customer_id NOT IN (
  SELECT customer_id FROM orders   -- one NULL here breaks everything
);

-- SAFE: NOT EXISTS is unaffected by NULLs in the subquery
SELECT c.customer_id, c.name
FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);

Tip

NOT EXISTS works correctly regardless of NULLs in the subquery, because it never performs a value-by-value comparison against the subquery's results — it only checks whether the correlated condition inside the subquery ever matches. As a rule of thumb, prefer NOT EXISTS over NOT IN whenever the subquery's column could contain NULLs, which in practice means most of the time.

Condition	Cares About Values?	NULL-Safe?	Can Short-Circuit?
`EXISTS`	No — presence only	Yes	Yes, stops at first match
`NOT EXISTS`	No — absence only	Yes	Yes, stops at first match
`IN`	Yes — value match	Yes	Depends on optimizer
`NOT IN`	Yes — value match	No — breaks silently with NULLs	Depends on optimizer

EXISTS with Additional Conditions

Because the subquery inside EXISTS is a full query, you can add any extra WHERE conditions you like — EXISTS is really just checking "does at least one row matching all of this criteria exist for the current outer row."

SQL

-- Customers with at least one delivered order over $1000
SELECT c.customer_id, c.name
FROM customers c
WHERE EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.customer_id
    AND o.status = 'delivered'
    AND o.total > 1000
);

Note

EXISTS subqueries are almost always correlated subqueries — they reference a column from the outer query (like o.customer_id = c.customer_id above) so the database knows which outer row it is checking existence for. See the Correlated Subqueries page for more on how that referencing works.

EXISTS returns true or false based purely on whether the subquery returns any rows.
The columns selected inside an EXISTS subquery are irrelevant — SELECT 1 is a common convention.
Prefer NOT EXISTS over NOT IN whenever the subquery could contain NULL values.
EXISTS can short-circuit at the first match, which often makes it efficient for large tables.