相关子查询是包含对也出现在外部查询中的表的引用的子查询 。例如:
SELECT * FROM t1
WHERE column1 = ANY (SELECT column1 FROM t2
WHERE t2.column2 = t1.column2);
请注意,子查询包含对列的引用
t1
,即使子查询的
FROM
子句未提及表
t1
。因此,MySQL 在子查询之外查找,并t1
在外部查询中查找。
假设该表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
。
作用域规则: 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
这反映了优化器在评估任何提升的谓词之前作为评估
JOIN
orWHERE
子句的一部分执行的基数检查,仅当子查询不返回多于一行时才执行此检查。
只要满足以下条件,就可以执行此类转换:
子查询可以是
SELECT
列表、WHERE
条件或HAVING
条件的一部分,但不能是JOIN
条件的一部分,也不能包含LIMIT
orOFFSET
子句。此外,子查询不能包含任何集合操作,例如UNION
.该
WHERE
子句可以包含一个或多个谓词,并与AND
. 如果WHERE
子句中包含OR
子句,则无法对其进行转换。至少有一个WHERE
子句谓词必须符合转换条件,并且它们都不能拒绝转换。要符合转换条件,
WHERE
子句谓词必须是相等谓词,其中每个操作数都应该是一个简单的列引用。没有其他谓词(包括其他比较谓词)有资格进行转换。谓词必须使用相等运算符=
进行比较;≪=>
在此上下文中不支持 null 安全 运算符。仅包含内部引用的
WHERE
子句谓词不符合转换条件,因为它可以在分组之前进行评估。仅包含外部引用的WHERE
子句谓词有资格进行转换,即使它可以提升到外部查询块。这可以通过在派生表中不分组的情况下添加基数检查来实现。要符合条件,
WHERE
子句谓词必须有一个仅包含内部引用的操作数和一个仅包含外部引用的操作数。如果谓词由于此规则不合格,则查询的转换将被拒绝。相关列只能出现在子查询的
WHERE
子句中(而不能出现在SELECT
列表、aJOIN
orORDER BY
子句、GROUP BY
列表或HAVING
子句中)。子查询列表中的派生表内也不能有任何相关列FROM
。相关列不能包含在聚合函数的参数列表中。
必须在直接包含正在考虑进行转换的子查询的查询块中解析相关列。
相关列不能出现在
WHERE
子句的嵌套标量子查询中。子查询不能包含任何窗口函数,也不能包含任何在子查询外部的查询块中聚合的聚合函数。
COUNT()
聚合函数如果包含在子查询的列表SELECT
元素中,则必须位于最顶层,并且不能是表达式的一部分。