Documentation Home

13.2.11.7 相关子查询

相关子查询是包含对也出现在外部查询中的表的引用的子查询 。例如:

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

请注意,子查询包含对列的引用 t1,即使子查询的 FROM子句未提及表 t1。因此,MySQL 在子查询之外查找,并t1在外部查询中查找。

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

作用域规则: 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));

在此语句中,x.column2必须是表中的列,t2因为SELECT column1 FROM t2 AS x ...重命名t2。它不是表中的列,t1因为 它是一个更远SELECT column1 FROM t1 ...的外部查询。

从 MySQL 8.0.24 开始,当 启用变量subquery_to_derived标志时,优化器可以将相关标量子查询转换为派生表。optimizer_switch考虑此处显示的查询:

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

为了避免为给定的派生表多次具体化,我们可以改为具体化一次派生表,该派生表在内部查询中引用的表的连接列上添加分组 ( t2.a),然后在提升的谓词上添加外部连接 ( t1.a = derived.a)以便选择正确的组以与外行匹配。(如果子查询已经有一个明确的分组,额外的分组将被添加到分组列表的末尾。)前面显示的查询可以这样重写:

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;

在重写的查询中,REJECT_IF() 表示一个内部函数,它测试给定条件(此处为比较)并在条件为真时ct > 1引发给定错误(在本例中 为 )。ER_SUBQUERY_NO_1_ROW这反映了优化器在评估任何提升的谓词之前作为评估 JOINorWHERE子句的一部分执行的基数检查,仅当子查询不返回多于一行时才执行此检查。

只要满足以下条件,就可以执行此类转换:

  • 子查询可以是 SELECT列表、 WHERE条件或 HAVING条件的一部分,但不能是 JOIN条件的一部分,也不能包含LIMITor OFFSET子句。此外,子查询不能包含任何集合操作,例如 UNION.

  • WHERE子句可以包含一个或多个谓词,并与AND. 如果 WHERE子句中包含 OR子句,则无法对其进行转换。至少有一个WHERE子句谓词必须符合转换条件,并且它们都不能拒绝转换。

  • 要符合转换条件, WHERE子句谓词必须是相等谓词,其中每个操作数都应该是一个简单的列引用。没有其他谓词(包括其他比较谓词)有资格进行转换。谓词必须使用相等运算符 =进行比较;≪=> 在此上下文中不支持 null 安全 运算符。

  • 仅包含内部引用的WHERE子句谓词不符合转换条件,因为它可以在分组之前进行评估。仅包含外部引用的 WHERE子句谓词有资格进行转换,即使它可以提升到外部查询块。这可以通过在派生表中不分组的情况下添加基数检查来实现。

  • 要符合条件,WHERE子句谓词必须有一个仅包含内部引用的操作数和一个仅包含外部引用的操作数。如果谓词由于此规则不合格,则查询的转换将被拒绝。

  • 相关列只能出现在子查询的WHERE子句中(而不能出现在SELECT列表、a JOINorORDER BY 子句、GROUP BY列表或 HAVING子句中)。子查询列表中的派生表内也不能有任何相关列FROM

  • 相关列不能包含在聚合函数的参数列表中。

  • 必须在直接包含正在考虑进行转换的子查询的查询块中解析相关列。

  • 相关列不能出现在WHERE子句的嵌套标量子查询中。

  • 子查询不能包含任何窗口函数,也不能包含任何在子查询外部的查询块中聚合的聚合函数。COUNT()聚合函数如果包含在子查询的列表 SELECT元素中,则必须位于最顶层,并且不能是表达式的一部分。

另见第 13.2.11.8 节,“派生表”