子查询中ON和WHERE的区别

2022-01-23 00:00:00 join syntax subquery mysql

我发现在 mysql 中使用 ON 和 WHERE 过滤带有连接的子查询之间存在奇怪的差异.

I found a weird disparity in mysql between using an ON and a WHERE to filter a subquery with a join.

这个查询运行良好:

SELECT * FROM cobrand co WHERE co.id IN (
    SELECT co2.id FROM cobrand co3 INNER JOIN cobrand co2 
    ON co2.id = co3.id + 1 
    WHERE co2.id = co.id
)

但是这个返回一个错误Unknown column 'co.id' in 'on Clause':

But this one returns an error Unknown column 'co.id' in 'on clause':

SELECT * FROM cobrand co WHERE co.id IN (
    SELECT co2.id FROM cobrand co3 INNER JOIN cobrand co2 
    ON co2.id = co3.id + 1 
    AND co2.id = co.id
)

显然子查询的 ON 子句不能访问外部查询的别名，而 WHERE 子句可以.为什么会这样，任何人都可以指出文档中的哪个位置?

Obviously the subquery's ON clause does not have access to to the outer query's alias, while the WHERE claus does. Why is this and can anyone point out where in the documentation this is covered?

删除了涉及过早优化的不需要的背景信息.

Removed unneeded background information involving pre-mature optimization.

推荐答案

以前，ON 子句可以引用名为 to 的表中的列这是正确的.现在一个 ON 子句只能引用它的操作数.

Previously, the ON clause could refer to columns in tables named to its right. Now an ON clause can refer only to its operands.

例子:

CREATE TABLE t1 (i1 INT);
CREATE TABLE t2 (i2 INT);
CREATE TABLE t3 (i3 INT);
SELECT * FROM t1 JOIN t2 ON (i1 = i3) JOIN t3;

以前，SELECT 语句是合法的.现在声明失败在on 子句"错误中出现未知列i3"，因为 i3 是t3 中的列，它不是 ON 子句的操作数.该声明应该改写如下:

Previously, the SELECT statement was legal. Now the statement fails with an Unknown column 'i3' in 'on clause' error because i3 is a column in t3, which is not an operand of the ON clause. The statement should be rewritten as follows:

SELECT * FROM t1 JOIN t2 JOIN t3 ON (i1 = i3);

-- MySQL 文档，13.2.9.2 JOIN 语法

相关文章