MySQL 9.0 Release Notes
Syntax:
operand comparison_operator ALL (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 empty tables are
“edge cases.” When writing subqueries, always
consider whether you have taken those two possibilities into
account.
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);
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.