SQL Server – What Does the Position of the ON Clause Mean?

sql servert-sql

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?

Best Answer

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.

Related Question