SQL EXISTS and NOT EXISTS: Check Whether Related Rows Exist

Many questions are about whether something exists: customers who have placed an order, products that have never been sold, employees with no direct reports. EXISTS answers exactly that. It returns true if a subquery finds at least one row.

The example tables#

customers

orders

Syntax#

SELECT columns
FROM table_a a
WHERE EXISTS (
  SELECT 1
  FROM table_b b
  WHERE b.some_column = a.some_column
);

The subquery is correlated: it refers to the outer row (a.some_column), so it is checked once for each row of table_a.

Customers who have ordered#

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

name
----
Asha
Ravi

For each customer, the database asks: is there at least one order with this customer's id? For Asha the answer is yes (twice, but once is enough), so she is kept. Note that each customer appears once, even though Asha has two orders. A JOIN would have returned her twice.

NOT EXISTS: rows with no match#

Customers who have never ordered:

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

name
-----
Meera
John

This is the same "anti-join" as LEFT JOIN ... WHERE o.order_id IS NULL, written in a way that reads almost like English.

Why NOT EXISTS is safer than NOT IN#

NOT IN breaks when the list it checks against contains a NULL:

SELECT name
FROM customers
WHERE customer_id NOT IN (1, 2, NULL);

name
----
(0 rows)

No rows at all, because 3 NOT IN (1, 2, NULL) cannot be proven true when one value is unknown. If orders.customer_id ever contained a NULL, WHERE customer_id NOT IN (SELECT customer_id FROM orders) would silently return nothing. NOT EXISTS is not affected by NULLs, so it is the safer default. (More in IN.)

EXISTS vs IN vs JOIN#

These three find customers who have ordered:

-- EXISTS
SELECT name FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);

-- IN
SELECT name FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders);

-- JOIN (needs DISTINCT, because Asha has two orders)
SELECT DISTINCT c.name
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id;

Modern optimisers often run EXISTS and IN the same way. Choose EXISTS for "has related rows" checks, especially negated ones.

Adding conditions inside EXISTS#

The subquery can contain any conditions. Customers with at least one order over 200:

SELECT name
FROM customers c
WHERE EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.customer_id
    AND o.amount > 200
);

This returns Asha (order 101 is 250) and Ravi (order 103 is 560).

Common mistakes#

Practice#

SELECT e.name
FROM employees e
WHERE EXISTS (
  SELECT 1
  FROM employees r
  WHERE r.manager_id = e.id
);

name
-----
Meera
Priya

Key takeaway#