子查询中ON和WHERE之间的区别 [英] Difference between ON and WHERE in subquery

问题描述

我发现在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':

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子句可以引用名为的表中的列 这是正确的.现在，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语法

这篇关于子查询中ON和WHERE之间的区别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！