ALL / ANY

ALL: satisfied when the comparison is true for all values in the subquery.

SELECT column FROM table
WHERE column operator ALL (SELECT column FROM subquery);

ANY: satisfied when the comparison is true for at least one value in the subquery.

SELECT column FROM table
WHERE column operator ANY (SELECT column FROM subquery);

SOME is an alias for ANY and behaves identically.

SELECT column FROM table
WHERE column operator SOME (SELECT column FROM subquery);

The following employees table is used in the examples below.

The examples below combine multiple features. Make sure you understand conditional filtering with WHERE, average calculation with AVG, and grouping with GROUP BY before proceeding.

Retrieve employees whose salary exceeds the average salary of every department (> ALL).

sample_all_any.sql

SELECT name, salary, department
FROM employees
WHERE salary > ALL (
    SELECT AVG(salary)
    FROM employees
    GROUP BY department
);

+--------+--------+------------+
| name   | salary | department |
+--------+--------+------------+
| user_a | 620000 | org_a      |
| user_c | 540000 | org_a      |
+--------+--------+------------+
2 rows in set

Retrieve employees whose salary exceeds the highest salary in at least one department (> ANY).

sample_all_any.sql

SELECT name, salary
FROM employees
WHERE salary > ANY (
    SELECT MAX(salary)
    FROM employees
    WHERE department <> 'org_a'
    GROUP BY department
);

+--------+--------+
| name   | salary |
+--------+--------+
| user_a | 620000 |
| user_c | 540000 |
+--------+--------+
2 rows in set

= ANY is equivalent to IN. Retrieve employees in the 'org_a' or 'org_b' department.

sample_all_any.sql

SELECT name, department
FROM employees
WHERE department = ANY (
    SELECT DISTINCT department FROM employees
    WHERE department IN ('org_a', 'org_b')
);

+--------+------------+
| name   | department |
+--------+------------+
| user_a | org_a      |
| user_c | org_a      |
| user_e | org_b      |
| user_b | org_b      |
| user_d | org_a      |
+--------+------------+
5 rows in set

ALL and ANY (SOME) are standard SQL and are supported in MySQL, PostgreSQL, Oracle, and SQL Server.

SELECT name, salary, department
FROM employees
WHERE salary > ALL (
    SELECT AVG(salary)
    FROM employees
    GROUP BY department
);

SQLite does not support ALL, ANY, or SOME. Use a subquery with MAX or MIN as a substitute.

SELECT name, salary, department
FROM employees
WHERE salary > (
    SELECT MAX(avg_salary) FROM (
        SELECT AVG(salary) AS avg_salary FROM employees GROUP BY department
    )
);

ALL and ANY are used together with comparison operators (=, <>, <, >, <=, >=). Learning common patterns makes them easier to apply. For example, > ALL (SELECT MAX(...) ...) means "greater than the highest value across all groups."

= ANY returns the same result as IN. Using IN is generally preferred for readability. Conversely, <> ALL is equivalent to NOT IN. When the subquery returns no rows, ALL evaluates to TRUE (a universal statement over an empty set is vacuously true), which can cause unexpected full-table results.

For advanced subqueries that reference the outer query, see Correlated Subqueries.