Importance of order in left Join

[zephan]

Hi All,

I run

SELECT db1.table1.*, table2.f1, table2.f2, table3.f3
FROM table1, table2
LEFT JOIN table3
ON t1_fk = t3_pk

the query returns an error that t1_fk does'nt exist.

If i revert table1 and table2 order like below :

SELECT db1.table1.*, table2.f1, table2.f2, table3.f3
FROM table2, table1
LEFT JOIN table3
ON t1_fk = t3_pk

The query works fine.

t1_fk is a field of table1 and t3_pk a filed of table3.

Is the order of transcription generally important in FROM clause ? Is it dependant of a version, or a parameter in DB ?

Thanks

 

[TonyGroves]

The clause[tt]
FROM table1, table2 LEFT JOIN table3 ON t1_fk=t3_pk[/tt]
is the same as saying[tt]
FROM
table1
INNER JOIN table2
LEFT JOIN table3 ON t1_fk=t3_pk[/tt]
You have two joins here. The first joins every record of table1 to every record of table2, since you haven't specified a join condition; if you have 100 records in one table and 200 in the other, that gives a result set of 20000 records. Then the second join is applied; the result set from the first join (the "left side") is joined to table3 (the "right side") using the condition "t1_fk=t3_pk". Since that is a left join, all records from the left side of the join will be returned, with null fields where there is no matching record in the right-side table.

Therefore, the order of tables is important for a LEFT JOIN or RIGHT JOIN, but not for an inner join.

In your example, I can't understand why you got the error you reported.

 

[TonyGroves]

It appears I could be mistaken here. Maybe the comma join operator is evaluated /after/ any explicit JOIN operators. That would explain your error message, since the first join would have been "table2 LEFT JOIN table3 ON t1_fk=t3_pk", which of course would be wrong since table1 is not yet being joined.

In any event, you would propably be better off avoiding the comma join operator altogether, and using explicit JOIN statements instead, which are much clearer.

 

[r937]

just another example which emphasizes the importance of the following...

tip: never mix table list syntax with JOIN syntax



r937.com | rudy.ca

 

[zephan]

This code is generated by a PHP/MySql soft. Actually, it's not a cartesian product but a mix, as u said of table list and join syntax :

SELECT db1.table1.*, table2.f1, table2.f2, table3.f3
FROM table1, table2
LEFT JOIN table3
ON t1_fk = t3_pk
WHERE table1.f1 = table2.f1

I dont know why the developper used this mixed structure.
Thank you for your answers Tony and Rudy.

 

[zephan]

Hi,
Finally, it seems that in the earlier version of MySql (till v4) the query would work. Since v5 it would'nt for normalization reasons.
Regards

 

[r937]

see [link]http://bugs.mysql.com/bug.php?id=13551[/url]

scroll down to the entry [8 Oct 12:12] Sergei Golubchik



r937.com | rudy.ca