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);