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 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. 

## 13.2.11.5 Row Subqueries 

Scalar or column subqueries return a single value or a column of values. A row subquery  is a subquery variant that returns a single row and can thus return more than one column value. Legal operators for row subquery comparisons are: 

= > < >= <= <> != <=> 

Here are two examples: 

SELECT * FROM t1 WHERE (col1,col2) = (SELECT col3, col4 FROM t2 WHERE id = 10); SELECT * FROM t1 WHERE ROW(col1,col2) = (SELECT col3, col4 FROM t2 WHERE id = 10); 

For both queries, if the table t2  contains a single row with id = 10 , the subquery returns a single row. If this row has col3  and col4  values equal to the col1  and col2  values of any rows in t1 , the WHERE expression is TRUE  and each query returns those t1  rows. If the t2  row col3  and col4  values are not equal the col1  and col2  values of any t1  row, the expression is FALSE  and the query returns an empty result set. The expression is unknown  (that is, NULL ) if the subquery produces no rows. An error occurs if the subquery produces multiple rows because a row subquery can return at most one row. 

For information about how each operator works for row comparisons, see Section 12.4.2, “Comparison Functions and Operators” . 

The expressions (1,2)  and ROW(1,2)  are sometimes called row constructors . The two are equivalent. The row constructor and the row returned by the subquery must contain the same number of values. 

A row constructor is used for comparisons with subqueries that return two or more columns. When a subquery returns a single column, this is regarded as a scalar value and not as a row, so a row constructor 

