MySQL 8.0-13.2.11.5 Row Subqueries_row subquery-CSDN博客

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.

对于这两个查询，如果表t2包含id = 10的单行，则子查询返回单行。如果这一行的col3和col4值等于t1中的任何行的col1和col2值，WHERE表达式为TRUE，每个查询返回这些t1行。如果t2行的col3和col4值不等于任何t1行的col1和col2值，则表达式为FALSE，查询返回一个空的结果集。如果子查询不产生任何行，则表达式是未知的(即NULL)。如果子查询产生多行，则会发生错误，因为一个行子查询最多只能返回一行。

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

有关每个操作符如何用于行比较的信息，请参见12.4.2节“比较函数和操作符”。

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.

表达式(1,2)和ROW(1,2)有时被称为行构造函数。两者是等价的。行构造函数和子查询返回的行必须包含相同数量的值。

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”:

下面的查询回答了“查找表t1中同时存在于表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 8.2.1.22, “Row Constructor Expression Optimization”

有关优化器和行构造函数的更多信息，请参见8.2.1.22节“行构造函数表达式优化”