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

- URL: https://betteranalyst.com/sql/all

SQL guide · Filtering

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

- [SQL WHERE](https://betteranalyst.com/sql/where)
- [SQL ANY](https://betteranalyst.com/sql/any)
- [SQL IN](https://betteranalyst.com/sql/in)
- [SQL EXISTS](https://betteranalyst.com/sql/exists)
- [SQL NOT](https://betteranalyst.com/sql/not)
- [SQL HAVING](https://betteranalyst.com/sql/having)

[Browse all SQL guides](https://betteranalyst.com/sql)

## 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](https://betteranalyst.com/ai-sql-query-generator)