Why does join syntax with stacked on clauses work?

Question

I have three versions of the same select query (SQL Fiddle here):

CREATE TABLE MainTable(ID INT)
CREATE TABLE SubTable(ID INT, Type CHAR(2))
CREATE TABLE SubTableType(Type CHAR(2))

INSERT MainTable VALUES (1), (2)
INSERT SubTable VALUES (1, 'AB'), (2, 'AB'), (3, 'CD')
INSERT SubTableType VALUES ('AB')

SELECT  *
FROM    MainTable P
JOIN    SubTable L
ON  P.ID = L.ID
JOIN    SubTableType LT
ON  L.Type = LT.Type

SELECT  *
FROM    MainTable P
JOIN    SubTable L
JOIN    SubTableType LT
ON  L.Type = LT.Type
ON  P.ID = L.ID

SELECT  *
FROM    MainTable P
JOIN    SubTable L
JOIN    SubTableType LT
ON  P.ID = L.ID
ON  L.Type = LT.Type

The first two select statements work and produce the same result. The third reports the error,

The multi-part identifier "P.ID" could not be bound.

This question appears to claim that the first two queries are semantically equivalent, though they may perform differently. My question is rather about the syntax. I've always written joins according to the first pattern, and I didn't even realize that the second pattern was permissible. Clearly it is, but then why isn't the third pattern also acceptable? The main documentation doesn't seem to touch on this, so I'm trying to find documentation that spells out what is and isn't permissible.

You might find this answer useful. – mustaccio Aug 08 '18 at 23:06 — mustaccio, Aug 08 '18 at 23:06

score 2 · Answer 1 · edited Aug 08 '18 at 22:44

The ON clause location determines join order. You can do this with parentheses, which makes it much clearer:

SELECT  *
FROM    MainTable P
        INNER JOIN    
          ( SubTable L
            INNER JOIN    
            SubTableType LT
            ON  L.Type = LT.Type
          )
        ON  P.ID = L.ID

You can now just omit the parentheses, and you come up with your second query which forces L JOIN LT to be performed first, followed by the join to P.

The error you are getting is because your 3rd query doesn't preserve the Chiastic Order. Look at it with the parentheses, and it will become clear:

SELECT  *
FROM    MainTable P
        INNER JOIN    
        ( SubTable L
          INNER JOIN    
           SubTableType LT
           ON  P.ID = L.ID --<<< P is not a valid identifier here
         )
        ON  L.Type = LT.Type

My recommendation - if you need to force join order, always use the parentheses, and proper indentation of the code, which will make this immediately visible and easy to understand.

Since you are only using INNER JOINs, order doesn't matter, and the engine is free to choose to perform the join in any order it sees fit. When OUTER JOINs are involved, the order does matter, so you need to be able to force which tables you want joined first.

The ON clause location does not determine the join order; rather, it should match the semantic order of joined tables. — mustaccio, Aug 08 '18 at 23:11
@sp_BlitzErik according to Paul White's comment in the linked article, only using OPTION (FORCE ORDER) guarantees join order. Am I missing something, or is something incorrect? — mathewb, Aug 13 '18 at 18:07

Why does join syntax with stacked on clauses work?

1 Answers1