So I saw some code that does this...
select *
from A
left join B
left Join C
on C.column1 = B.column3
on B.column2 = A.column4
and C.column5 > 0
and B.column6 < 0
is this equivalent to
select *
from A
left join B
on B.column2 = A.column4
and B.column6 < 0
left Join C
on C.column1 = B.column
and C.column5 > 0
Why would anyone ever do that first snippet? was it maybe a typo?
MySQL has a looser syntax for joins than the ANSI standard. In fact, there is a whole section of the documentation devoted just to this topic.
The first query is indeed acceptable syntax, as shown in this SQL Fiddle. This query (same query, different formatting):
select *
from A left join
B left Join
C
on C.column1 = B.column3
on B.column2 = A.column4 and C.column5 > 0 and B.column6 < 0
Is interpreted as if there were parentheses:
select *
from A left join
(B left Join
C
on C.column1 = B.column3
)
on B.column2 = A.column4 and C.column5 > 0 and B.column6 < 0;
You can perhaps convince yourself of this by swapping the on clauses. This query:
select *
from A left join
B left Join
C
on B.column2 = A.column4 and C.column5 > 0 and B.column6 < 0
on C.column1 = B.column3;
produces an error. The A table reference is not known for the first on clause, because that join is just between B and C.
I'm not sure why someone would do it. It could be that the parentheses were dropped in some "clean-up" phase. It could simply be that when writing the query, this syntax made sense and the person never noticed how awkward the query is (as a note: I've never done such a thing ;). It could be intentional obfuscation.
join_condition) allowed per JOIN.