ANY & ALL

ANY and ALL let you compare a value against every value returned by a multi-row subquery, using ordinary comparison operators like =, >, <, >=, and <=. They are placed right before the subquery, and they change how the comparison behaves across the whole set of returned values. They are a frequent source of confusion, mostly because the plain-English meaning of "any" and "all" does not immediately map onto how they behave with > and <.

The Core Idea

Expression	Equivalent To	Meaning in Plain English
`= ANY (subquery)`	`IN (subquery)`	Equal to at least one value returned
`<> ALL (subquery)`	`NOT IN (subquery)`	Not equal to any value returned (NULL caveats apply, same as NOT IN)
`> ALL (subquery)`	Greater than the maximum	Greater than every value returned
`< ALL (subquery)`	Less than the minimum	Less than every value returned
`> ANY (subquery)`	Greater than the minimum	Greater than at least one value returned
`< ANY (subquery)`	Less than the maximum	Less than at least one value returned

Note

The mental trick that helps most people: ALL with a > means you have to beat the toughest competitor — the maximum — so it is equivalent to "greater than the maximum." ANY with a > only means you have to beat the easiest competitor — the minimum — so it is equivalent to "greater than the minimum." The comparisons flip for <.

= ANY: Equivalent to IN

SQL

-- These two queries return exactly the same result
SELECT product_name
FROM products
WHERE category_id = ANY (
  SELECT category_id FROM categories WHERE is_featured = true
);

SELECT product_name
FROM products
WHERE category_id IN (
  SELECT category_id FROM categories WHERE is_featured = true
);

> ALL: Greater Than Every Value (the Maximum)

SQL

-- Employees who earn more than every employee in the Sales department
-- i.e. more than the HIGHEST salary in Sales
SELECT employee_name, salary
FROM employees
WHERE salary > ALL (
  SELECT salary FROM employees WHERE department = 'Sales'
);

-- Equivalent, written with a scalar subquery and MAX
SELECT employee_name, salary
FROM employees
WHERE salary > (
  SELECT MAX(salary) FROM employees WHERE department = 'Sales'
);

> ANY: Greater Than at Least One Value (the Minimum)

SQL

-- Employees who earn more than AT LEAST ONE employee in Sales
-- i.e. more than the LOWEST salary in Sales
SELECT employee_name, salary
FROM employees
WHERE salary > ANY (
  SELECT salary FROM employees WHERE department = 'Sales'
);

-- Equivalent, written with a scalar subquery and MIN
SELECT employee_name, salary
FROM employees
WHERE salary > (
  SELECT MIN(salary) FROM employees WHERE department = 'Sales'
);

Putting Them Side by Side

Suppose the Sales department has salaries of 40000, 55000, and 70000. Comparing an outside employee's salary against that set with ANY and ALL produces very different cutoffs:

SQL

-- Sales salaries: 40000, 55000, 70000

-- salary > ALL (...) --> must exceed 70000 (the maximum)
-- salary > ANY (...) --> must exceed just 40000 (the minimum)
-- salary < ALL (...) --> must be below 40000 (the minimum)
-- salary < ANY (...) --> must be below just 70000 (the maximum)

NOT IN vs <> ALL

<> ALL (subquery) behaves the same as NOT IN (subquery), which means it carries the exact same NULL pitfall discussed on the EXISTS page — if the subquery returns even one NULL value, the comparison against every row becomes unknown, and the whole condition effectively excludes everything. NOT EXISTS remains the safer alternative when NULLs are possible.

= ANY behaves exactly like IN — true if the value matches at least one row.
<> ALL behaves exactly like NOT IN — including its NULL pitfall.
> ALL / < ALL compare against the toughest value (max / min respectively).
> ANY / < ANY compare against the easiest value (min / max respectively).
When in doubt, rewrite ANY/ALL using MIN/MAX in a scalar subquery to double-check your logic.