What does the position of the ON clause actually mean?
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
<join_type> T2 T2
<join_type> T3 T3
<join_type> 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.
If you look at the FROM
clause syntax diagram you will see that there is only one place for the ON
clause:
<joined_table> ::=
{
<table_source> <join_type> <table_source> ON <search_condition>
...
}
What you find confusing is simple recursion, because <table_source>
in <joined_table
> above can be another <joined_table
>:
[ FROM { <table_source> } [ ,...n ] ]
<table_source> ::=
{
table_or_view_name ...
...
| <joined_table>
...
}
To avoid confusion you should use parentheses in non-obvious cases (like your examples) to visually separate <table_sources>
; they are not necessary for the query parser but useful for humans.