I have two versions of a multiple join which produce identical results. The five tables are:
customers ← sales ← saleitems → paintings → artists
The arrows (hopefully) show the relationship between the tables.
Each table has a primary key called id
, and the inner tables have a foreign key to another table called [othertable]id
.
The first version is a classic join with the table listed in the above order
SELECT
c.id, c.givenname, c.familyname,
a.givenname, a.familyname
FROM
customers c
JOIN sales s ON c.id=s.customerid
JOIN saleitems si ON s.id=si.saleid
JOIN paintings p ON si.paintingid=p.id
JOIN artists a ON p.artistid=a.id
;
The second version jas all the JOIN
clauses first, and the the ON
clauses, in the reverse order.
SELECT
c.id, c.givenname, c.familyname,
a.givenname, a.familyname
FROM
customers c
JOIN sales s
JOIN saleitems si
JOIN paintings p
JOIN artists a
ON p.artistid=a.id
ON si.paintingid=p.id
ON s.id=si.saleid
ON c.id=s.customerid
;
OK, so it works, but can any one explain how the second version works? Why must the order of the ON
clauses be the reverse of the JOIN
clauses? Is it possible to randomly order the JOIN
or ON
clauses?
I have tested this in Microsoft SQL as well as with PostgreSQL.