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.