What does the position of the ON clause actually mean?

The normal JOIN ... ON ... syntax is well known. But it is also possible to position the ON clause separately from the JOIN that it corresponds to. This is something that is rarely seen in practice, not found in tutorials and I have not found any web resource that even mentions that this is possible.

Here is a script to play around with:

SELECT *
INTO #widgets1
FROM (VALUES (1), (2), (3)) x(WidgetID)

SELECT *
INTO #widgets2
FROM (VALUES (1, 'SomeValue1'), (2, 'SomeValue2'), (3, 'SomeValue3')) x(WidgetID, SomeValue)

SELECT *
INTO #widgetProperties
FROM (VALUES
    (1, 'a'), (1, 'b'),
    (2, 'a'), (2, 'b'))
x(WidgetID, PropertyName)

--q1
SELECT w1.WidgetID, w2.SomeValue, wp.PropertyName
FROM #widgets1 w1
LEFT JOIN #widgets2 w2 ON w2.WidgetID = w1.WidgetID
LEFT JOIN #widgetProperties wp ON w2.WidgetID = wp.WidgetID AND wp.PropertyName = 'b'
ORDER BY w1.WidgetID

--q2
SELECT w1.WidgetID, w2.SomeValue, wp.PropertyName
FROM #widgets1 w1
LEFT JOIN #widgets2 w2 --no ON clause here
JOIN #widgetProperties wp
 ON w2.WidgetID = wp.WidgetID AND wp.PropertyName = 'b'
 ON w2.WidgetID = w1.WidgetID
ORDER BY w1.WidgetID

--q3
SELECT w1.WidgetID, w2.SomeValue, wp.PropertyName
FROM #widgets1 w1
LEFT JOIN (
    #widgets2 w2 --no SELECT or FROM here
    JOIN #widgetProperties wp
    ON w2.WidgetID = wp.WidgetID AND wp.PropertyName = 'b')
ON w2.WidgetID = w1.WidgetID
ORDER BY w1.WidgetID

q1 looks normal. q2 and q3 have these unusual positionings of the ON clause.

This script does not necessarily make much sense. It was hard for me to contrive a meaningful scenario.

So what do these unusual syntax patterns mean? How is this defined? I noticed that not all positions and orderings for the two ON clauses are allowed. What are the rules governing this?

Also is it ever a good idea to write queries like this?

It determines the logical tables involved in the join.

With a simple example

SELECT w1.WidgetID,
       w2.SomeValue,
       wp.PropertyName
FROM   #widgets1 w1
       LEFT JOIN #widgets2 w2
         ON w2.WidgetID = w1.WidgetID
       JOIN #widgetProperties wp
         ON w2.WidgetID = wp.WidgetID
            AND wp.PropertyName = 'b'
ORDER  BY w1.WidgetID

#widgets1 is left outer joined to #widgets2 - the result of that forms a virtual table that is inner joined to #widgetProperties. The predicate w2.WidgetID = wp.WidgetID will mean that any null extended rows from the initial outer join are filtered out, effectively making all the joins inner joins.

This differs from q2...

SELECT w1.WidgetID,
       w2.SomeValue,
       wp.PropertyName
FROM   #widgets1 w1
       LEFT JOIN #widgets2 w2 --no ON clause here
                 JOIN #widgetProperties wp
                   ON w2.WidgetID = wp.WidgetID
                      AND wp.PropertyName = 'b'
         ON w2.WidgetID = w1.WidgetID
ORDER  BY w1.WidgetID

#widgets2 is inner joined onto #widgetProperties. The virtual table resulting from that join is then the right hand table in the Left Outer Join on #widgets1

The same result can be achieved by using a derived table or Common Table Expression...

WITH VT2
     AS (SELECT w2.WidgetID,
                w2.SomeValue,
                wp.PropertyName
         FROM   #widgets2 w2 
                JOIN #widgetProperties wp
                  ON w2.WidgetID = wp.WidgetID
                     AND wp.PropertyName = 'b')
SELECT w1.WidgetID,
       VT2.SomeValue,
       VT2.PropertyName
FROM   #widgets1 w1
       LEFT JOIN VT2
         ON VT2.WidgetID = w1.WidgetID
ORDER  BY w1.WidgetID

... Or alternatively you could re-order the virtual tables and use a RIGHT JOIN instead.

SELECT w1.WidgetID,
       w2.SomeValue,
       wp.PropertyName
FROM   #widgets2 w2
       INNER JOIN #widgetProperties wp
               ON w2.WidgetID = wp.WidgetID
                  AND wp.PropertyName = 'b'
       RIGHT JOIN #widgets1 w1
               ON w2.WidgetID = w1.WidgetID
ORDER  BY w1.WidgetID

This is covered by Itzik Ben Gan here


... the JOIN conditions must follow a chiastic relationship to the table
order. That is, if you specify tables T1, T2, T3, and T4 in that order
and the JOIN conditions match T1 with T2, T2 with T3, and T3 with T4,
you must specify the JOIN conditions in the order opposite to the
table order, like this:

FROM   T1
        T2 T2
                   T3 T3
                              T4
                               ON T4.key = T3.key
                    ON T3.key = T2.key
         ON T2.key = T1.key

To look at this join technique in a different way,
a given JOIN condition can refer only to the table names right above
it or table names that earlier JOIN conditions already referred to and
resolved.

but the article has a number of inaccuracies, see the follow up letter by Lubor Kollar as well.

StackExchange Database Administrators Q#125422, answer score: 36