13.2.11.7 Correlated Subqueries相关的子查询

A correlated subquery is a subquery that contains a reference to a table that also appears in the outer query. 相关子查询是一个子查询，它包含对也出现在外部查询中的表的引用。For example:例如：

SELECT * FROM t1
  WHERE column1 = ANY (SELECT column1 FROM t2
                       WHERE t2.column2 = t1.column2);

Notice that the subquery contains a reference to a column of t1, even though the subquery's FROM clause does not mention a table t1. 注意，子查询包含对t1列的引用，即使子查询的FROM子句没有提到表t1。So, MySQL looks outside the subquery, and finds t1 in the outer query.因此，MySQL在子查询外部查找，并在外部查询中找到t1。

Suppose that table t1 contains a row where column1 = 5 and column2 = 6; meanwhile, table t2 contains a row where column1 = 5 and column2 = 7. 假设表t1包含一行，其中column1=5，column2=6；同时，表t2包含一行，其中column1=5，column2=7。The simple expression ... WHERE column1 = ANY (SELECT column1 FROM t2) would be TRUE, but in this example, the WHERE clause within the subquery is FALSE (because (5,6) is not equal to (5,7)), so the expression as a whole is FALSE.简单表达式... WHERE column1 = ANY (SELECT column1 FROM t2)会成为TRUE，但是在此示例中，子查询中的WHERE子句是FALSE（因为(5,6)不等于(5,7)，所以作为整体的表达式是FLASE。）

Scoping rule:范围规则： MySQL evaluates from inside to outside. MySQL从内到外进行计算。For example:例如：

SELECT column1 FROM t1 AS x
  WHERE x.column1 = (SELECT column1 FROM t2 AS x
    WHERE x.column1 = (SELECT column1 FROM t3
      WHERE x.column2 = t3.column1));

In this statement, x.column2 must be a column in table t2 because SELECT column1 FROM t2 AS x ... renames t2. 在此语句中，x.column2必须是一个表t2中的列，因为SELECT column1 FROM t2 AS x ...重命名了t2。It is not a column in table t1 because SELECT column1 FROM t1 ... is an outer query that is farther out.它不是表t1中的列，因为SELECT column1 FROM t1 ...是较远的外部查询。

Beginning with MySQL 8.0.24, the optimizer can transform a correlated scalar subquery to a derived table when the subquery_to_derived flag of the optimizer_switch variable is enabled. 从MySQL 8.0.24开始，优化器可以在启用优化器开关变量的subquery_to_derived标志时，将相关标量子查询转换为派生表。Consider the query shown here:考虑下面显示的查询：

SELECT * FROM t1 
    WHERE ( SELECT a FROM t2 
              WHERE t2.a=t1.a ) > 0;

To avoid materializing several times for a given derived table, we can instead materialize—once—a derived table which adds a grouping on the join column from the table referenced in the inner query (t2.a) and then an outer join on the lifted predicate (t1.a = derived.a) in order to select the correct group to match up with the outer row. 为了避免对给定的派生表进行多次具体化，我们可以将派生表具体化一次，即在内部查询（t2.a）中引用的表的联接列上添加一个分组，然后在提升谓词（t1.a=derived.a）上添加一个外部联接，以便选择与外部行匹配的正确组。(If the subquery already has an explicit grouping, the extra grouping is added to the end of the grouping list.) （如果子查询已经有显式分组，则额外的分组将添加到分组列表的末尾。）The query previously shown can thus be rewritten like this:因此，前面显示的查询可以这样重写：

SELECT t1.* FROM t1 
    LEFT OUTER JOIN
        (SELECT a, COUNT(*) AS ct FROM t2 GROUP BY a) AS derived
    ON  t1.a = derived.a 
        AND 
        REJECT_IF(
            (ct > 1),
            "ERROR 1242 (21000): Subquery returns more than 1 row"
            )
    WHERE derived.a > 0;

In the rewritten query, REJECT_IF() represents an internal function which tests a given condition (here, the comparison ct > 1) and raises a given error (in this case, ER_SUBQUERY_NO_1_ROW) if the condition is true. 在重写的查询中，REJECT_IF()表示测试给定条件的内部函数（这里，比较ct>1) 如果条件为true，则引发给定的错误（在本例中为ER_SUBQUERY_NO_1_ROW）。This reflects the cardinality check that the optimizer performs as part of evaluating the JOIN or WHERE clause, prior to evaluating any lifted predicate, which is done only if the subquery does not return more than one row.这反映了在计算任何提升谓词之前，优化器作为计算JOIN或WHERE子句的一部分执行的基数检查，只有在子查询不返回多行时才执行该检查。

This type of transformation can be performed, provided the following conditions are met:只要满足以下条件，就可以执行这种类型的转换：

See also Section 13.2.11.8, “Derived Tables”.另请参阅第13.2.11.8节，“导出表”。