MySQL ANY ALL: Compare a Value with a Subquery Result

ANY, SOME and ALL compare a value with every value a subquery returns.

query.sql

SQL

CREATE TABLE departments (id INT PRIMARY KEY, name VARCHAR(20));
CREATE TABLE employees (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(20), department_id INT, salary INT);
INSERT INTO departments VALUES (1, 'engineering'), (2, 'sales'), (3, 'support');
INSERT INTO employees (name, department_id, salary) VALUES
    ('Asha', 1, 120000), ('Ben', 1, 95000), ('Chitra', 2, 70000), ('Dev', 2, 64000), ('Esha', 3, 52000);

ANY (and SOME)

value op ANY (subquery) is true if the comparison is true for at least one value in the subquery. SOME is a synonym.

query.sql

SQL

1
2
3

SELECT name, salary FROM employees
WHERE salary > ANY (SELECT salary FROM employees WHERE department_id = 2)
ORDER BY salary;

Output

OUTPUT

+--------+--------+
| name   | salary |
+--------+--------+
| Chitra |  70000 |
| Ben    |  95000 |
| Asha   | 120000 |
+--------+--------+
3 rows in set

“Earns more than at least one sales employee”: more than the lowest sales salary (64,000). So > ANY means “greater than the minimum”.

ALL

value op ALL (subquery) is true if the comparison is true for every value.

query.sql

SQL

1
2
3

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

Output

OUTPUT

+------+--------+
| name | salary |
+------+--------+
| Ben  |  95000 |
| Asha | 120000 |
+------+--------+
2 rows in set

“Earns more than every sales employee”: more than the highest sales salary (70,000). > ALL means “greater than the maximum”.

The easy translation

Expression	Same as
`x > ANY (subquery)`	`x > (SELECT MIN(...))`
`x > ALL (subquery)`	`x > (SELECT MAX(...))`
`x < ANY (subquery)`	`x < (SELECT MAX(...))`
`x < ALL (subquery)`	`x < (SELECT MIN(...))`
`x = ANY (subquery)`	`x IN (subquery)`
`x <> ALL (subquery)`	`x NOT IN (subquery)`

Often the MAX and MIN forms are clearer and faster:

query.sql

SQL

SELECT name, salary FROM employees WHERE salary > (SELECT MAX(salary) FROM employees WHERE department_id = 2) ORDER BY salary;

Output

OUTPUT

+------+--------+
| name | salary |
+------+--------+
| Ben  |  95000 |
| Asha | 120000 |
+------+--------+
2 rows in set

= ANY and IN

query.sql

SQL

1
2

SELECT name FROM employees WHERE department_id = ANY (SELECT id FROM departments WHERE name IN ('engineering', 'support')) ORDER BY id;
SELECT name FROM employees WHERE department_id IN (SELECT id FROM departments WHERE name IN ('engineering', 'support')) ORDER BY id;

Output

OUTPUT

+------+
| name |
+------+
| Asha |
| Ben  |
| Esha |
+------+
3 rows in set

+------+
| name |
+------+
| Asha |
| Ben  |
| Esha |
+------+
3 rows in set

The same result. Almost everyone writes IN.

The empty set trap

With an empty subquery result:

x > ANY (empty) is false (there is no value to be greater than).
x > ALL (empty) is true (nothing contradicts it).

query.sql

SQL

1
2

SELECT COUNT(*) AS earns_more_than_all_of_nobody FROM employees WHERE salary > ALL (SELECT salary FROM employees WHERE department_id = 99);
SELECT COUNT(*) AS earns_more_than_any_of_nobody FROM employees WHERE salary > ANY (SELECT salary FROM employees WHERE department_id = 99);

Output

OUTPUT

+-------------------------------+
| earns_more_than_all_of_nobody |
+-------------------------------+
|                             5 |
+-------------------------------+
1 row in set

+-------------------------------+
| earns_more_than_any_of_nobody |
+-------------------------------+
|                             0 |
+-------------------------------+
1 row in set

Everyone “earns more than all of nobody”. This logic is correct and often surprising. With > (SELECT MAX(...)), an empty subquery gives NULL, and nobody matches.

The NULL trap

If the subquery returns a NULL, ALL comparisons can become unknown, and no rows are returned:

query.sql

SQL

SELECT COUNT(*) AS rows_returned FROM employees WHERE salary > ALL (SELECT NULL UNION ALL SELECT 50000);

Output

OUTPUT

+---------------+
| rows_returned |
+---------------+
|             0 |
+---------------+
1 row in set

The NULL makes “greater than all” unknown. Filter NULLs inside the subquery (WHERE salary IS NOT NULL), as for NOT IN.

The row with the maximum, per group

Another classic: “an employee who earns the most in their department” using >= ALL:

query.sql

SQL

SELECT e.name, e.department_id, e.salary
FROM employees e
WHERE e.salary >= ALL (SELECT x.salary FROM employees x WHERE x.department_id = e.department_id)
ORDER BY e.department_id;

Output

OUTPUT

+--------+---------------+--------+
| name   | department_id | salary |
+--------+---------------+--------+
| Asha   |             1 | 120000 |
| Chitra |             2 |  70000 |
| Esha   |             3 |  52000 |
+--------+---------------+--------+
3 rows in set

It works, but a window function (ROW_NUMBER() OVER (PARTITION BY ...)) is easier to read and faster (ROW_NUMBER).

When to use them

ANY and ALL are part of standard SQL and read like English. In practice, MAX/MIN subqueries, IN and EXISTS cover almost every need. Use ANY/ALL when the comparison with “any” or “all” is the idea you want to express, and the data may contain ties and gaps.

lightbulb

Translate when in doubt

If an ALL or ANY query surprises you, rewrite it with MAX or MIN and check the result. The two should agree, except for empty and NULL results.