Join condition parsing puzzle

From: Mark Jeffcoat <jeffcoat(at)alumni(dot)rice(dot)edu>
To: pgsql-general(at)postgresql(dot)org
Subject: Join condition parsing puzzle
Date: 2018-08-23 18:17:37
Message-ID: CAPN82eQkDCtwG7E404DD3SqZdZoKp_xRSv5=P5+rMkfTGxxFTg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Joshua D. Drake 2018-08-23 18:43:54 Re: Upgrade/Downgrade
Previous Message Igor Neyman 2018-08-23 17:46:25 RE: extracting the sql for a function