SQL ANY and ALL Operators: Compare With Every Value in a Subquery

ANY and ALL compare one value with every value returned by a subquery:

• > ANY (...): greater than at least one of them.
• > ALL (...): greater than every one of them.

They work with any comparison operator: =, <>, <, <=, >, >=. SOME is an exact synonym for ANY.

The example table#

products

The two Furniture prices are 7,000 and 4,500.

> ALL: more expensive than every furniture item#

SELECT name, price
FROM products
WHERE price > ALL (
  SELECT price FROM products WHERE category = 'Furniture'
);

name    | price
--------+------
Laptop  | 55000
Monitor | 12000

To be greater than all of 7,000 and 4,500, a price must beat the largest one, 7,000.

> ANY: more expensive than at least one furniture item#

SELECT name, price
FROM products
WHERE price > ANY (
  SELECT price FROM products WHERE category = 'Furniture'
)
ORDER BY price;

name       | price
-----------+------
Desk Chair | 7000
Monitor    | 12000
Laptop     | 55000

To beat any one of them, a price only needs to beat the smallest, 4,500. (The Desk Chair counts because 7,000 > 4,500.)

Equivalents with MIN and MAX#

ANY and ALL can always be rewritten with an aggregate, which many people find easier to read:

For example, the first query above is the same as:

SELECT name, price
FROM products
WHERE price > (SELECT MAX(price) FROM products WHERE category = 'Furniture');

Another example: comparing with a department#

Employees who earn less than everyone in Sales (Sales salaries are 52,000 and 61,000):

SELECT name, salary
FROM employees
WHERE salary < ALL (
  SELECT salary FROM employees WHERE department = 'Sales'
);

name | salary
-----+-------
John | 46000

Edge cases: empty lists and NULLs#

These are where ANY / ALL and the MIN / MAX rewrites differ:

> ALL of an empty list is true because there is no value it fails to beat. That surprises people: if a category has no products, "more expensive than all of them" returns everything. Be deliberate about which behaviour you want.

Database support#

ANY, SOME and ALL with subqueries work in PostgreSQL, MySQL, SQL Server and Oracle. SQLite does not support them; use the MIN / MAX or IN / NOT IN forms there. PostgreSQL also accepts arrays: WHERE id = ANY(ARRAY[1, 2, 3]).

Common mistakes#

Practice#

SELECT name, salary
FROM employees
WHERE salary > ALL (
  SELECT salary FROM employees WHERE department = 'Sales'
);

SELECT name, salary
FROM employees
WHERE salary > (
  SELECT MAX(salary) FROM employees WHERE department = 'Sales'
);

name  | salary
------+-------
Ravi  | 78000
Meera | 81000

Key takeaway#