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);

-- Retrieve employees whose salary exceeds the average salary of every department (> ALL).
SELECT employee_name, salary, department
FROM employees
WHERE salary > ALL (
    SELECT AVG(salary)
    FROM employees
    GROUP BY department
);

-- Retrieve employees whose salary exceeds the highest salary in at least one department (> ANY).
SELECT employee_name, salary
FROM employees
WHERE salary > ANY (
    SELECT MAX(salary)
    FROM employees
    WHERE department <> 'Executive Planning'
    GROUP BY department
);

-- = ANY is equivalent to IN. Retrieve employees in the Sales or Engineering department.
SELECT employee_name, department
FROM employees
WHERE department = ANY (SELECT department FROM departments WHERE type = 'direct');

-- Employees whose salary exceeds all department averages (350000, 420000, 510000).
employee_name | salary  | department
--------------+---------+------------------
Hanako Suzuki | 620000  | Engineering
Taro Yamada   | 540000  | Executive Planning

-- = ANY: employees belonging to a directly managed department.
employee_name | department
--------------+-----------
Hanako Suzuki | Engineering
Jiro Sato     | Sales
Megumi Tanaka | Sales

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

SELECT employee_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.

-- Substitute for > ALL in SQLite: returns rows greater than the maximum of the subquery.
SELECT employee_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.