MySQL 8.0 Reference Manual Including MySQL NDB Cluster 8.0

Syntax:

`operand`

ALL (`comparison_operator`

)`subquery`

The word `ALL`

, which must follow a comparison
operator, means “return `TRUE`

if the
comparison is `TRUE`

for `ALL`

of the values in the column that the subquery returns.”
For example:

SELECT s1 FROM t1 WHERE s1 > ALL (SELECT s1 FROM t2);

Suppose that there is a row in table `t1`

containing `(10)`

. The expression is
`TRUE`

if table `t2`

contains
`(-5,0,+5)`

because `10`

is
greater than all three values in `t2`

. The
expression is `FALSE`

if table
`t2`

contains
`(12,6,NULL,-100)`

because there is a single
value `12`

in table `t2`

that
is greater than `10`

. The expression is
*unknown* (that is, `NULL`

)
if table `t2`

contains
`(0,NULL,1)`

.

Finally, the expression is `TRUE`

if table
`t2`

is empty. So, the following expression is
`TRUE`

when table `t2`

is
empty:

SELECT * FROM t1 WHERE 1 > ALL (SELECT s1 FROM t2);

But this expression is `NULL`

when table
`t2`

is empty:

SELECT * FROM t1 WHERE 1 > (SELECT s1 FROM t2);

In addition, the following expression is `NULL`

when table `t2`

is empty:

SELECT * FROM t1 WHERE 1 > ALL (SELECT MAX(s1) FROM t2);

In general, *tables containing NULL
values* and

`NOT IN`

is an alias for ```
<>
ALL
```

. Thus, these two statements are the same:

SELECT s1 FROM t1 WHERE s1 <> ALL (SELECT s1 FROM t2); SELECT s1 FROM t1 WHERE s1 NOT IN (SELECT s1 FROM t2);

MySQL 8.0.19 supports the `TABLE`

statement. As with `IN`

,
`ANY`

, and `SOME`

, you can use
`TABLE`

with `ALL`

and
`NOT IN`

provided that the following two
conditions are met:

The table in the subquery contains only one column

The subquery does not depend on a column expression

For example, assuming that table `t2`

consists
of a single column, the last two statements shown previously can
be written using `TABLE t2`

like this:

SELECT s1 FROM t1 WHERE s1 <> ALL (TABLE t2); SELECT s1 FROM t1 WHERE s1 NOT IN (TABLE t2);

A query such as ```
SELECT * FROM t1 WHERE 1 > ALL
(SELECT MAX(s1) FROM t2);
```

cannot be written using
`TABLE t2`

because the subquery depends on a
column expression.