SQL Tutorial · Joins and Subqueries · Lesson 25 of 36

SQL EXISTS

Test whether related rows exist with EXISTS and NOT EXISTS.

EXISTS

EXISTS (subquery) is true if the subquery returns at least one row. It stops at the first match, which makes it efficient:

Example 1Try it yourself

SELECT c.name
FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.customer_id AND o.status = 'delivered'
)
ORDER BY c.name;

Output

name
Ada Lovelace
Alan Turing
Grace Hopper
Linus Torvalds
Yukihiro Matsumoto

NOT EXISTS vs NOT IN

To find rows without related rows, prefer NOT EXISTS. NOT IN returns no rows at all if the subquery contains a NULL — a famous trap:

Example 2Try it yourself

-- Categories with no product over 100 (correct with NOT EXISTS)
SELECT name FROM categories cat
WHERE NOT EXISTS (SELECT 1 FROM products p WHERE p.category_id = cat.category_id AND p.price > 100);
-- NOT IN with a NULL in the list matches nothing:
SELECT COUNT(*) AS not_in_result FROM categories WHERE category_id NOT IN (SELECT category_id FROM products WHERE price > 100);

Output

name
Books
Home
Toys

not_in_result
0

Test yourself

Why can NOT IN (subquery) return no rows unexpectedly?

The subquery is too large
The subquery contains a NULL
NOT IN is deprecated
It needs an index

Show answer

Answer: The subquery contains a NULL