Query logical processing phases while mixing old and new join syntax

Question

Could anyone clarify why order of tables in FROM matters if I mix old and new syntax for joining tables? For example,

SELECT * 
FROM table1 a, table2 b
INNER JOIN table3 c ON (c.a_id = a.id) -- error - a.id - invalid identifier
WHERE (a.id = b.a_id)

SELECT * 
FROM table2 b, table1 a 
INNER JOIN table3 c ON (c.a_id = a.id) -- ok, table1 a is the last in FROM
WHERE (a.id = b.a_id)

I'm not going to use such ugly queries, but I believe all tables specified in FROM should be visible to other parts of the query.
I don't think it's up to RDMS because SQLServer, Oracle, and Mysql behave the same way - error for first query, no error for second. I'm definitely missing something obvious, but I didn't find anything that describes why it works this way.

Thank you.

Answer 1

It is to do with the precedence of the comma operator. As stated in the MySQL docs

the precedence of the comma operator is less than of INNER JOIN, CROSS JOIN, LEFT JOIN, and so on. If you mix comma joins with the other join types when there is a join condition, an error of the form Unknown column 'col_name' in 'on clause' may occur

You get the same results with the rewrite below (where the position of the ON clause makes clear the logical order of join operations)

Fails

SELECT * 
FROM   table1 a 
       INNER JOIN (table2 b 
                   INNER JOIN table3 c 
                     ON c.a_id = a.id ) -- error - a.id - invalid identifier 
         ON a.id = b.a_id 

Succeeds

SELECT * 
FROM   table2 b 
       INNER JOIN (table1 a 
                   INNER JOIN table3 c 
                     ON c.a_id = a.id ) 
         ON a.id = b.a_id 

In the one that succeeds a is first joined to c on c.a_id = a.id then the virtual table resulting from that join is joined to b on a.id = b.a_id which all works fine.

For the one that fails b is first attempted to be joined to c on c.a_id = a.id which is invalid as that references table a. The ON clause can only refer to the table immediately preceding it or one already brought in by a previous ON clause.

From the conditions c.a_id = a.id and a.id = b.a_id then c.a_id=b.a_id so you could change the join condition to that and it would work or you could of course also use a derived table or view to alter the precedence and ensure a,b were treated as a logically joined unit before joining onto c

SELECT * 
FROM   (SELECT * 
        FROM   table2 b, 
               table1 a) ba 
       INNER JOIN table3 c 
         ON ( c.a_id = ba.id ) 
WHERE  ( ba.id = ba.a_id )