MySQL 8.0 Reference Manual Including MySQL NDB Cluster 8.0

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 14.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 cannot be used with a subquery that does not return at least two columns. Thus, the following query fails with a syntax error:

SELECT * FROM t1 WHERE ROW(1) = (SELECT column1 FROM t2)

Row constructors are legal in other contexts. For example, the following two statements are semantically equivalent (and are handled in the same way by the optimizer):

SELECT * FROM t1 WHERE (column1,column2) = (1,1); SELECT * FROM t1 WHERE column1 = 1 AND column2 = 1;

The following query answers the request, “find all rows in
table `t1`

that also exist in table
`t2`

”:

SELECT column1,column2,column3 FROM t1 WHERE (column1,column2,column3) IN (SELECT column1,column2,column3 FROM t2);

For more information about the optimizer and row constructors, see Section 10.2.1.22, “Row Constructor Expression Optimization”