EXISTS, ANY and ALL

Does a related row exist?

Often you do not need the related rows themselves. You only need to know whether any exist. "Which customers have placed an order?" does not require listing the orders. EXISTS answers that, and it is usually written with a correlated subquery, which links the inner query to the current outer row.

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

+----+------------+
| id | first_name |
+----+------------+
|  1 | Asha       |
|  2 | Ben        |
|  3 | Chloe      |
|  4 | Daniel     |
|  5 | Elena      |
|  6 | Farid      |
|  7 | Grace      |
|  8 | Hiro       |
| 10 | Jack       |
| 11 | Kavya      |
+----+------------+

For each customer the inner query looks for at least one matching order. As soon as it finds one, EXISTS is true and MySQL stops looking. That early exit can make it faster than a join that has to produce every pair. The SELECT 1 is a convention: the inner select list is ignored, so many people write a constant.

Unlike a join, EXISTS never duplicates the outer rows. Asha has four orders but appears once.

NOT EXISTS: rows with no relatives

NOT EXISTS is true when the subquery returns nothing. This finds customers who never ordered:

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

+----+------------+
| id | first_name |
+----+------------+
|  9 | Isabel     |
| 12 | Liam       |
+----+------------+

Products that have never been ordered:

SELECT p.id, p.name
FROM products p
WHERE NOT EXISTS (SELECT 1 FROM order_items oi WHERE oi.product_id = p.id);

+----+--------------------+
| id | name               |
+----+--------------------+
| 15 | Puzzle 1000 Pieces |
+----+--------------------+

Both could also be written with LEFT JOIN ... IS NULL. The NOT EXISTS form reads more like the English question and avoids joining at all.

Why not NOT IN?

NOT IN looks similar but has a trap with NULL. The employees table has a NULL manager_id (the chief executive). Ask: which employees are not anyone's manager?

SELECT name FROM employees WHERE id NOT IN (SELECT manager_id FROM employees);

It returns nothing, which is wrong: several people manage nobody. The list contains a NULL, so id NOT IN (..., NULL) is never true. Fix it either by removing NULLs from the list, or by using NOT EXISTS:

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

+-------------+
| name        |
+-------------+
| Paula Gomez |
| Quinn Baker |
| Rina Das    |
| Sam Wright  |
| Tara Singh  |
| Uma Reddy   |
| Victor Lang |
+-------------+

Now it correctly lists everyone who has no direct reports. This is the main reason experienced developers prefer NOT EXISTS.

EXISTS with extra conditions

The subquery can have any conditions. Customers who have a delivered order in 2024 of more than one product line:

SELECT c.id, c.first_name
FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.id AND o.status = 'delivered'
    AND (SELECT COUNT(*) FROM order_items oi WHERE oi.order_id = o.id) > 1
)
ORDER BY c.id;

+----+------------+
| id | first_name |
+----+------------+
|  1 | Asha       |
|  2 | Ben        |
|  4 | Daniel     |
|  6 | Farid      |
|  8 | Hiro       |
| 10 | Jack       |
| 11 | Kavya      |
+----+------------+

ANY, SOME and ALL

These operators compare a value with the set of values a subquery returns. They sit after a comparison operator.

• x > ANY (subquery): true if x is greater than at least one value (that is, greater than the smallest).
• x > ALL (subquery): true if x is greater than every value (greater than the largest).
• = ANY is the same as IN. <> ALL is the same as NOT IN.
• SOME is a synonym for ANY.

Products more expensive than every book:

SELECT name, price FROM products WHERE price > ALL (SELECT price FROM products WHERE category_id = 2) ORDER BY price;

+-----------------------------+--------+
| name                        | price  |
+-----------------------------+--------+
| Building Blocks Set         |  49.99 |
| Standing Desk Mat           |  55.00 |
| Mechanical Keyboard         |  89.50 |
| Noise-Cancelling Headphones | 199.99 |
+-----------------------------+--------+

Products cheaper than at least one product in Electronics (so cheaper than the dearest one):

SELECT name, price FROM products WHERE price < ANY (SELECT price FROM products WHERE category_id = 1) AND category_id <> 1 ORDER BY price DESC LIMIT 3;

+-----------------------------+-------+
| name                        | price |
+-----------------------------+-------+
| Standing Desk Mat           | 55.00 |
| Building Blocks Set         | 49.99 |
| Data Structures Illustrated | 45.00 |
+-----------------------------+-------+

These are not used often, because MAX and MIN express the same idea more directly (price > (SELECT MAX(price) ...)), but you will see them in older code and interview questions. Be careful when the subquery returns no rows: > ALL over an empty set is true for every row, while > ANY over an empty set is false.

Common mistakes

• Forgetting to correlate the subquery. EXISTS (SELECT 1 FROM orders) without a link to the outer row is true for everyone, as long as the orders table has any row.
• Using NOT IN against a column that can contain NULL.
• Selecting many columns in the EXISTS subquery. Harmless but noisy; use SELECT 1.
• Confusing ALL and ANY in the direction of the comparison.

Exercise

Write queries to find: (1) customers who have at least one order with status shipped, (2) categories that contain no product ordered in June 2024, and (3) employees who earn more than all employees in the Sales department (department 2).

SELECT c.id, c.first_name FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id AND o.status = 'shipped');

SELECT cat.id, cat.name FROM categories cat
WHERE NOT EXISTS (
  SELECT 1 FROM products p
  JOIN order_items oi ON oi.product_id = p.id
  JOIN orders o ON o.id = oi.order_id
  WHERE p.category_id = cat.id AND o.order_date >= '2024-06-01' AND o.order_date < '2024-07-01'
);

SELECT name, salary FROM employees
WHERE salary > ALL (SELECT salary FROM employees WHERE department_id = 2);

+----+------------+
| id | first_name |
+----+------------+
|  1 | Asha       |
|  3 | Chloe      |
|  6 | Farid      |
| 10 | Jack       |
+----+------------+
+----+------+
| id | name |
+----+------+
|  3 | Home |
+----+------+
+-------------+-----------+
| name        | salary    |
+-------------+-----------+
| Meera Iyer  | 150000.00 |
| Nina Petrov | 125000.00 |
+-------------+-----------+