or
boot4life imported from SE
sql-server
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?
Top Answer
Martin Smith
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-2004
Answer #2
mustaccio imported from SE
If 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
What does the position of the ON clause actually mean?

This is a dedicated room for discussion about this question.

Once logged in you can direct comments to the question poster (or any answer poster) here.