Understanding Variations on Multiple Joins

Question

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.

Answer 1

You can't randomize them, the order is important. Maybe you can see it clearer if we add a few parenthesis between the joins.

SELECT
    c.id
FROM
    customers c
        JOIN (sales s JOIN saleitems si ON s.id = si.saleid)
    ON c.id = s.customerid

What this query is doing is joining first sales with saleitems and then their result is being joined with customers. You can't reference a customers column inside the parenthesis join because it's not accessible:

SELECT
    c.id
FROM
    customers c
        JOIN (sales s JOIN saleitems si ON s.id = si.saleid 
            AND c.id = c.customerid) -- What is c.customerid here??

Msg 4104, Level 16, State 1, Line 6 The multi-part identifier "s.customerid" could not be bound.

If you ask me, I'd stick with the first way of writing the query, it's way clearer for most programmers that way.

Answer 2

The JOIN syntax in most cases requires both JOIN and ON to be present (unless some specific situations like CROSS JOIN for which ON would not make any difference). Consider this to be similar to BEGIN..END statements - they have to be the END statement relates to the nearest previous BEGIN like

BEGIN -- block 1
  BEGIN -- block 2
    -- 
  END -- end block 2
END -- end block 1

That is why it worked when you put ON clauses in the reverse-order. But it is not a good practice, because it will be time consuming to determine which condition relates to what tables. Especially if you loose indenting/formatting on the way.

Additionally, the extra indenting for the 2nd and all the next JOINS is unnecessary. The order of JOIN clauses does not matter usually (unless you use query hint that forces the optimizer to do so).

I, personally, would find it much cleaner as below (+ explicitly state INNER JOIN, + arrange joins starting with the 'fact' table that you are interested in, other tables are description of the facts you are retrieving, optionnally if important, I put each column in a separate row - if not important i usually put them in one row). But then again, this is individual coding style thing.

SELECT
  c.id, c.givenname, c.familyname,
  a.givenname, a.familyname
FROM
  saleitems si
  INNER JOIN sales s ON si.SaleId = s.Id
  INNER JOIN customers c ON s.CustomerId = c.Id
  INNER JOIN paintings p ON si.PaintingId = p.Id
  INNER JOIN artists a ON p.ArtistId = a.Id