Re: Join condition parsing puzzle

Mark Jeffcoat <jeffcoat@alumni.rice.edu> writes:
> SELECT *
> FROM relation_a
> LEFT JOIN relation_b
> JOIN relation_c
> ON (relation_c.id_p = relation_b.id_p)
> ON (relation_a.id_c = relation_b.id_c AND relation_a.id_v = relation_b.id_v);

> I would have claimed before seeing this example that it wasn't even
> grammatical; I thought the only legal place to write the ON clause was
> immediately after the JOIN. Apparently not.

> How should I read this query?  I'd appreciate any help understanding this.

You read it as

SELECT *
FROM
  relation_a
  LEFT JOIN (relation_b
             JOIN relation_c
             ON (relation_c.id_p = relation_b.id_p))
  ON (relation_a.id_c = relation_b.id_c AND relation_a.id_v = relation_b.id_v);

There's no other valid way to parenthesize it, so that's what
the parser does.

            regards, tom lane