Difference between ON and WHERE in subquery

Question

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

This query runs fine:

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
)

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
)

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?

EDIT: Removed unneeded background information involving pre-mature optimization.

Answer 1

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

Example:

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;

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 docs, 13.2.9.2 JOIN Syntax

Answer 2

The where clause applies to the whole resultset.

The on clause only applies to the join in query.

Please refer following Links

What's the difference between where clause and on clause when table left join?

In SQL / MySQL, what is the difference between "ON" and "WHERE" in a join statement?