SQL guide · Filtering

SQL ALL: True Only If Every Value in a Set Matches

The ALL operator compares a value against every row returned by a subquery and is true only if the comparison holds for all of them. Like ANY, it is used together with a comparison operator, for example > ALL, < ALL or <> ALL.

The meaning is easy to read in words. x > ALL (subquery) is true when x is greater than every value, so it equals x > MAX(...). x < ALL (subquery) equals x < MIN(...). x <> ALL (subquery) means the value matches none of them, which is the same as NOT IN.

If the subquery returns no rows, ALL is true, because there is nothing to violate the condition. That edge case surprises people, as does NULL handling: one NULL in the returned set makes the result unknown unless another value already makes it false. Aggregates with MAX or MIN are often clearer, but ALL expresses a rule over a whole set directly.

Basic syntax

SELECT column1
FROM table_name
WHERE column operator ALL (subquery);

-- operator is one of =, <>, <, <=, >, >=

The subquery must return one column. The result is true for an empty subquery. = ALL is rarely useful because it requires all values to be equal to the tested one. When you are unsure about the edge cases, run the subquery on its own first and check whether it can return zero rows or NULLs. Those two situations explain nearly every surprising result from this operator.

SQL ALL examples

Greater than every value

Employees who earn more than everyone in Support. This is the same as comparing with the highest support salary.

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

Less than every value

Products cheaper than all items in the premium tier, so cheaper than the least expensive premium item.

SELECT product_id, name, price
FROM products
WHERE price < ALL (
  SELECT price
  FROM products
  WHERE tier = 'premium'
);

Not equal to all (like NOT IN)

Customers whose id appears in no order. This is equivalent to NOT IN, and shares its problem: a NULL customer_id in orders makes the result empty.

SELECT customer_id, name
FROM customers
WHERE customer_id <> ALL (
  SELECT customer_id
  FROM orders
  WHERE customer_id IS NOT NULL
);

Equivalent rewrite with MAX

A scalar aggregate gives the same answer in most cases and works in every database. It also returns the intended result if the subquery has no rows differently, so check that edge case.

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

Where SQL dialects differ

PostgreSQL: Supports ALL with subqueries and arrays, such as price > ALL(ARRAY[10, 20]).

MySQL: Supports ALL with a subquery that returns a single column. It is not available with literal lists or arrays.

SQL Server: Supports ALL with a subquery. When the subquery returns no rows the condition is true, so include a guard if that is not wanted.

SQLite: Not supported. Use NOT EXISTS, NOT IN or MAX and MIN comparisons instead.

Oracle: Supports ALL with subqueries and lists of literals, for instance salary > ALL (3000, 4000).

Common mistakes

Forgetting that an empty subquery makes ALL true, so a filter can unexpectedly return every row.
Letting a NULL slip into the subquery, which turns <> ALL and > ALL results into unknown and drops rows.
Confusing <> ALL with <> ANY. The first means none match; the second is almost always true.
Using = ALL and expecting membership. It is true only when every returned value equals the tested one.

Related SQL guides

Browse all SQL guides

Need help writing the SQL?

Describe your tables and the result you want, then review the generated query against your database before running it.

Try the AI SQL generator