Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer 1

up vote 2 down vote accepted

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 ) 
share|improve this answer
    
thanks for your answer. But it seems that your first example (marked as "fails") is not working due to syntax error (second ON), not because of invalid identifier. At least I'm able to execute that query SELECT * FROM table1 a INNER JOIN table2 b INNER JOIN table3 c ON ( c.a_id = a.id ) with Mysql 5.5. –  a1ex07 Mar 10 '12 at 18:42
    
@a1ex07 - MySQL requires some parentheses that are optional in SQL Server. Now added. In SQL Server the position of the ON clause is sufficient. –  Martin Smith Mar 10 '12 at 19:00
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.