CODEWITHSUNDEEP
sqlsql-servert-sqljoin
Brien Foss
Brien Foss

Reputation: 3367

TSQL joins using more than one 'ON' keyword

I came across a stored procedure that contained some syntax I found to be confusing to read. I am hoping someone can help to explain the multiple usage of the 'ON' keyword in JOINS (see the generalized example below). I am having trouble finding a clear answer and example as to why this approach would be used.

SELECT *
FROM rc rc 
RIGHT OUTER JOIN etc 
INNER JOIN CR cr 
INNER JOIN cre cre ON cr.x = cre.x 
INNER JOIN ca ca ON cr.ID = ca.ID ON etc.ETCID = cre.ETCID 
LEFT OUTER JOIN cred cred ON cre.CRID = cred.CRID 
LEFT OUTER JOIN SC SC ON cred.ID = SC.ID 
RIGHT OUTER JOIN ec ec ON cred.ID = ec.ID 
LEFT OUTER JOIN ecc ecc ON cred.ID = ecc.ID 
LEFT OUTER JOIN hcc hcc ON cred.ID = hcc.ID ON rc.ID = cred.ID

Upvotes: 3

Views: 521

Answers (4)

Joe Farrell
Joe Farrell

Reputation: 3542

Check out the syntax of the FROM clause on this page. I've reproduced a couple of the relevant bits here:

[ FROM { <table_source> } [ ,...n ] ]

<table_source> ::=   
{  
    table_or_view_name [ [ AS ] table_alias ]   
        [ <tablesample_clause> ]   
        [ WITH ( < table_hint > [ [ , ]...n ] ) ]   
    | <some options omitted here>
    | <joined_table>   
    | <some more options omitted here>
}  

<joined_table> ::=   
{  
    <table_source> <join_type> <table_source> ON <search_condition>   
    | <some options omitted here>
}

<join_type> ::=   
[ { INNER | { { LEFT | RIGHT | FULL } [ OUTER ] } } [ <join_hint> ] ]  
JOIN

A few points relevant to the query in your original question:

  1. The first form shown for <joined_table>—actually the only one I've shown—ends with an ON clause, and permits any <table_source> between its <join_type> and its ON clause.

  2. <joined_table> by itself is a valid form of <table_source>.

  3. It is therefore legal to nest one <joined_table> inside of another, in which case the ON clause of the inner <joined_table> will be immediately followed by the ON clause of the outer <joined_table>. I think that's what you're seeing in your query.

To verify that I could reproduce your result, I created the appropriate in-memory tables and substituted them into your original query to get:

declare @rc table (id bigint);
declare @etc table (etcid bigint);
declare @cr table (x bigint, id bigint);
declare @cre table (x bigint, etcid bigint, crid bigint);
declare @ca table (id bigint);
declare @cred table (crid bigint, id bigint);
declare @sc table (id bigint);
declare @ec table (id bigint);
declare @ecc table (id bigint);
declare @hcc table (id bigint);

SELECT *
FROM
    @rc rc 
    RIGHT OUTER JOIN 
        @etc etc/*added*/
        INNER JOIN
            @CR cr 
            INNER JOIN @cre cre
            ON cr.x = cre.x 
            INNER JOIN @ca ca
            ON cr.ID = ca.ID 
        ON etc.ETCID = cre.ETCID 
        LEFT OUTER JOIN @cred cred 
        ON cre.CRID = cred.CRID 
        LEFT OUTER JOIN @SC SC 
        ON cred.ID = SC.ID 
        RIGHT OUTER JOIN @ec ec 
        ON cred.ID = ec.ID 
        LEFT OUTER JOIN @ecc ecc 
        ON cred.ID = ecc.ID 
        LEFT OUTER JOIN @hcc hcc
        ON cred.ID = hcc.ID 
    ON rc.ID = cred.ID

There are only three differences between your original query and mine, and none of them should affect how it works:

  1. I replaced your original table names with my equivalent in-memory table names.

  2. I changed etc in the original query to @etc etc, giving an alias to the @etc table.

  3. I changed the formatting so that each ON clause is positioned directly below the corresponding JOIN, to hopefully illustrate what goes with what.

Here's a screenshot from my editor with some additional visual aids: note that each of the large expressions outlined in blue satisfies the definition of <joined_table> given above. The green arrows match the oddly-placed ON clauses to their corresponding joins.

Query illustration

So yes, it is legal syntax, but no, I can't think of any reason why you'd actually want to do this. If you're going to join one group of tables to another, there are vastly-less-confusing alternatives available, such as a subquery or common table expression.

Upvotes: 3

devio
devio

Reputation: 37215

Actually, JOIN not only joins tables or views, but also the result of other join operations. This causes the ON keyword to not directly follow the joined table name.

Brackets indicate joined join results:

SELECT *
FROM rc rc 
RIGHT OUTER JOIN 
[   etc 
    INNER JOIN CR 
    [   cr 
        INNER JOIN cre cre ON cr.x = cre.x 
        INNER JOIN ca ca ON cr.ID = ca.ID 
    ] ON etc.ETCID = cre.ETCID 
    LEFT OUTER JOIN cred cred ON cre.CRID = cred.CRID 
    LEFT OUTER JOIN SC SC ON cred.ID = SC.ID 
    RIGHT OUTER JOIN ec ec ON cred.ID = ec.ID 
    LEFT OUTER JOIN ecc ecc ON cred.ID = ecc.ID 
    LEFT OUTER JOIN hcc hcc ON cred.ID = hcc.ID 
] ON rc.ID = cred.ID

Upvotes: 2

Icarus
Icarus

Reputation: 63966

You either transcribed the SQL statement incorrectly or this is not T-SQL. You can't write: ON t2.ID = t3.ID ON t2.ID = t1.ID as is not even valid. And I'd be surprised if is even valid on any other database management system.

What you can do, though, is something like this:

SELECT *
FROM table1 t1
INNER JOIN table2 t2 ON t1.ID = t2.ID
LEFT OUTER JOIN table3 t3 ON t2.ID = t3.ID 
AND /*AND, NOT ON*/ 
t2.ID = t1.ID

In other words, you can do a JOIN forcing the match on multiple columns but you cannot do a JOIN the way you wrote it above.

Upvotes: -1

Isaiah3015
Isaiah3015

Reputation: 491

Using On 1x and use AND after is technically the same. There isn't a difference in the execution. Personally, I've never seen anyone write multiple ON

SELECT *
FROM table1 t1
INNER JOIN table2 t2 ON t1.ID = t2.ID
LEFT OUTER JOIN table3 t3 ON t2.ID = t3.ID AND  t2.ID = t1.ID

Upvotes: 0

Related Questions