Thread: Join condition parsing puzzle

I'm looking at a query generated by SQLAlchemy. It works; Postgres is
perfectly happy to run it, and it gives answers that make sense to the
guy who wrote it. But I don't understand why it works.

Stripped way down ...

CREATE VIEW relation_a (id_c, id_v)
AS VALUES (1, 20),  (2, 21), (3, 22);

CREATE VIEW relation_b (id_c, id_v, id_p)
AS VALUES (1, 20, 300), (2, 21, 301);

CREATE VIEW relation_c (id_p)
AS VALUES (301);

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.

-- 
Mark Jeffcoat
Austin, TX

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

On Thu, Aug 23, 2018 at 4:51 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Mark Jeffcoat <jeffcoat@alumni.rice.edu> writes:

> > 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.
>
> 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.



Thank you very much for your help, Tom. In retrospect, I see I'd
over-generalized the rule that sub-selects in the from clause require
an alias.

Clear now.

-- 
Mark Jeffcoat
Austin, TX