I am using PostgreSQL, but I think the question applies to other SQLs as well.
I was editing a JOIN clause, and I ended up with the following syntax.
tableA JOIN tableB JOIN tableC ON tableB.fk = tableC.pk ON tableA.pk = tableB.fk;
That is, I have 2 JOIN clauses followed by 2 ON clauses. The fields fk and pk are my primary keys, abbreviated for this example.
I tested before I noticed the syntax. What surprised me is that it worked.
The question is: is that a proper variation of the syntax, and how is it interpreted?
What you have written is correct. You can also write it as :
tableA JOIN tableB ON tableA.pk = tableB.fk JOIN tableC ON tableB.fk = tableC.pk
Both the queries gives the same result though :)
It is legal, though not common.
To interpret it, work from the inside out. First evaluate tableB JOIN tableC, and use the first ON expression. It helps, because this part makes sense all by itself:
tableB JOIN tableC ON tableB.fk = tableC.pk
The entire result set from that expression then becomes the 2nd-term/right-hand-side for the outer join expression, giving you this:
tableA JOIN (previous expression) ON tableA.pk = tableB.fk
One thing to remember is that if any records were filtered by the first/inner expression, they are not considered at all for the outer expression. It makes no difference in this example, but it can change things when OUTER joins are involved.