1

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.

Omn
  • 2,982
  • 1
  • 26
  • 39
  • Please describe what you need the query to do. I am not convinced that a subquery is necessary. – Gordon Linoff Mar 04 '16 at 04:50
  • 1
    "(This matters because in my actual query I am joining more tables in the subquery and want to prevent the unneeded rows from getting joined and making even more unneeded rows)" - Premature optimisation. Let the optimiser do its job, then analyse the execution plan, and only then think about whether or not you need to speed it up. – Amadan Mar 04 '16 at 04:57
  • @Amadan You are right... I was unaware that the optimiser would cause these two queries to execute the same... So I don't need to to get a around this restriction, however I would still like more information on this syntax error. I'll edit my question to reflect this. – Omn Mar 04 '16 at 05:31
  • It depends on the type of join you are using. If you are using INNER JOIN then ON & WHERE would be same. The results differ if the join is left/right – Deepanshu Goyal Mar 04 '16 at 05:50

2 Answers2

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

Amadan
  • 191,408
  • 23
  • 240
  • 301
  • The key part here is you can only use columns that an operand of the ON clause. – Omn Mar 05 '16 at 00:55
  • And if anyone is curious 'The term “previously” means “prior to MySQL 5.0.12.”' – Omn Mar 05 '16 at 00:57
0

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?

Community
  • 1
  • 1
user2148124
  • 940
  • 1
  • 7
  • 20