> ## Documentation Index
> Fetch the complete documentation index at: https://villagesql.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# MySQL ANY and ALL

> How the MySQL ANY and ALL operators compare a value against every row a subquery returns, what each means, and what both do when the subquery is empty.

<Card title="VillageSQL is a drop-in replacement for MySQL with extensions." icon="database" href="/docs/mysql-8.4/stable/quickstart">
  All examples on this page work on VillageSQL. Install Now →
</Card>

`=` compares a value to 1 value. `ANY` and `ALL` put a comparison in front of a
whole subquery result: greater than any of them, greater than all of them. You will meet them more often in other people's queries than write them
yourself, so the thing to get from this lesson is being able to read one.

Open the client with `mysql -u root -p sakila` before you start.

## How ANY and ALL work

```sql theme={null}
SELECT COLUMN_LIST
FROM TABLE_NAME
WHERE COLUMN_NAME > ALL (SELECT COLUMN_NAME FROM OTHER_TABLE);
```

Replace `COLUMN_LIST` with the columns you want, `TABLE_NAME` with the table,
`COLUMN_NAME` with the column to test, and `OTHER_TABLE` with the table the
subquery reads. Replace `>` with any comparison operator.

`ANY` is true when the comparison holds against at least 1 returned row. `ALL`
is true when it holds against every one. `SOME` is another spelling of `ANY`
and means exactly the same thing.

Both give NULL rather than true or false when a NULL in the subquery leaves the
answer undecided, the same way any other comparison against NULL does, and
`WHERE` then drops the row.

Read them as the plain English: `> ANY` is "bigger than at least one of them",
which is the same as bigger than the smallest. `> ALL` is "bigger than every
one of them", the same as bigger than the largest.

## Examples

### 1) Bigger than at least one

```sql theme={null}
SELECT title, length
FROM film
WHERE length > ANY (SELECT length FROM film WHERE rating = 'G')
ORDER BY length, title
LIMIT 3;
```

```text theme={null}
+---------------------+--------+
| title               | length |
+---------------------+--------+
| ACE GOLDFINGER      |     48 |
| HEAVEN FREEDOM      |     48 |
| MIDSUMMER GROUNDHOG |     48 |
+---------------------+--------+
3 rows in set (0.00 sec)
```

The shortest `G` film runs 47 minutes, so every film of 48 or more qualifies.

### 2) Bigger than every one

```sql theme={null}
SELECT title, length
FROM film
WHERE length > ALL (SELECT length FROM film WHERE rating = 'G')
ORDER BY title;
```

```text theme={null}
Empty set (0.01 sec)
```

No rows, and that is the right answer. The longest `G` film is 185 minutes,
which is also the longest film in the catalog, so nothing is longer than all
of them.

### 3) = ANY is IN

```sql theme={null}
SELECT title FROM film WHERE rating = ANY (SELECT rating FROM film WHERE title = 'ACADEMY DINOSAUR') ORDER BY title LIMIT 3;
```

```text theme={null}
+------------------+
| title            |
+------------------+
| ACADEMY DINOSAUR |
| AGENT TRUMAN     |
| ALASKA PHANTOM   |
+------------------+
3 rows in set (0.00 sec)
```

`= ANY` and [IN](/docs/tutorial/in) are the same operator written 2 ways, and `IN`
is the one people read faster. `<> ALL` is likewise `NOT IN`, with the same
NULL trap that lesson describes.

## An empty subquery flips them

This is the part worth knowing, because it looks wrong.

```sql theme={null}
SELECT title, length
FROM film
WHERE length > ALL (SELECT length FROM film WHERE rating = 'X')
ORDER BY title
LIMIT 3;
```

```text theme={null}
+------------------+--------+
| title            | length |
+------------------+--------+
| ACADEMY DINOSAUR |     86 |
| ACE GOLDFINGER   |     48 |
| ADAPTATION HOLES |     50 |
+------------------+--------+
3 rows in set (0.00 sec)
```

No film is rated `X`, so the subquery returns nothing, and every film comes
back. `ALL` over an empty set is true: there is no row that fails the test.
`ANY` over an empty set is false for the mirror reason, so the same query with
`ANY` returns nothing.

A typo in the subquery therefore gives you the whole table with `ALL` and an
empty result with `ANY`, in both cases without an error.

## Summary

* Read `> ANY` as bigger than the smallest, and `> ALL` as bigger than the largest.
* Read `SOME` as another spelling of `ANY`.
* Prefer `IN` over `= ANY` and `NOT IN` over `<> ALL`, because they read faster.
* Expect `ALL` to be true and `ANY` to be false when the subquery returns nothing.
* Expect NULL rather than true or false when a NULL in the subquery leaves it undecided.

## See also

* [IN](/docs/tutorial/in) — the same test, written the way most people write it
* [Subqueries](/docs/tutorial/subqueries) — the queries these operators compare against
* [EXISTS](/docs/tutorial/exists) — testing for a row rather than comparing values