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
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
I have tested this in Microsoft SQL as well as with PostgreSQL.