MySQL :: MySQL 8.0 Reference Manual :: 15.2.15.4 Subqueries with ALL (original) (raw)

15.2.15.4 Subqueries with ALL

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 isTRUE if table t2 contains(-5,0,+5) because 10 is greater than all three values in t2. The expression is FALSE if tablet2 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 tablet2 is empty. So, the following expression isTRUE when table t2 is empty:

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

But this expression is NULL when tablet2 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);

MySQL 8.0.19 supports the TABLE statement. As with IN,ANY, and SOME, you can useTABLE with ALL andNOT 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 usingTABLE t2 because the subquery depends on a column expression.