MySQL 8.0-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. So, MySQL looks outside the subquery, and finds t1 in the outer query.

注意，子查询包含对t1列的引用，即使子查询的FROM子句没有提到表t1。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. 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.

假设表t1包含一行，其中column1 = 5, column2 = 6;同时，表t2包含一行，其中column1 = 5和column2 = 7。这个简单的表达…WHERE column1 = ANY (SELECT column1 FROM t2)将为TRUE，但在本例中，子查询中的WHERE子句为FALSE(因为(5,6)不等于(5,7))，因此表达式作为一个整体是FALSE。

Scoping rule: MySQL evaluates from inside to outside. For example:

作用域规则:MySQL从内部到外部计算。例如:

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