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?
TopAnswers
Databases
Top Answer
Martin Smith (imported from SE)
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][1]
> ... 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][2] as well.
[1]: https://www.itprotoday.com/software-development/take-control-joins
[2]: https://www.itprotoday.com/software-development/letters-february-2004If you look at the [`FROM` clause syntax diagram][1] 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.
[1]: https://technet.microsoft.com/en-us/library/ms177634%28v=sql.105%29.aspx