Sql server - Inner Join syntax with two joins

Question

I've noticed one of our programmers wrote this:

SELECT * FROM Table_A A
  INNER JOIN Table_B B
    INNER JOIN Table_C C
      ON C.Id = B.Id
        ON B.Id = A.Id

I didn't expect this to work but it does return results from our database.

Does this work the same as the normal join syntax? I can't find documentation on it.

Yes this is similar to the normal joins. These are explained in the book ''SQL Server Bible" — Dheerendra
– Dheerendra, Commented Jan 18, 2019 at 15:40

Damien_The_Unbeliever · Accepted Answer · 2019-01-18 15:52:26Z

4

Yes. The way I usually recommend people think about it is as if JOIN acts the same as an opening bracket ( and ON acts the same as a closing bracket ). Then the normal rules you're used to for bracket matching tell you which ON clause relates to which JOIN, and gives you some context for determining which table names/aliases are in scope.

So this query effectively joins table B to table C, then joins A to that combined result.

I'd normally recommend against it when purely using INNER JOINs - you can rearrange the tables easily so that you follow the more "normal" JOIN/ON/JOIN/ON pattern. You get the same logical result from this query¹:

SELECT *
FROM Table_B B
INNER JOIN Table_C C
  ON C.Id = B.Id
INNER JOIN  Table_A A
  ON B.Id = A.Id

Note that this is covered by the documentation, if you've read it carefully:

<table_source> ::=   
{  
    table_or_view_name [ [ AS ] table_alias ]   
        [ <tablesample_clause> ]   
        [ WITH ( < table_hint > [ [ , ]...n ] ) ]   
    ...
    | <joined_table>
    ...

And

<joined_table> ::=   
{  
    <table_source> <join_type> <table_source> ON <search_condition>
    ...

So, in a <joined_table>, what goes on the left of the <join_type>? Any <table_source>. And what goes on the right of the <join_type>? Any <table_source>. And either or both of those can, themselves, in fact be a <joined_table>.

The "normal" form you refer to in your question is what falls out if we only allow ourselves to use a <joined_table> in the left-hand position of another <joined_table>.

¹Although if you're relying on the column order rather than column names, you'll get columns in a different order due to using SELECT *. But you shouldn't rely on column orders.

edited Jan 18, 2019 at 15:52

answered Jan 18, 2019 at 15:41

Damien_The_Unbeliever

241k28 gold badges358 silver badges470 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Elaskanator Over a year ago

+1 for recommending against the syntax in OP's question. Readability is paramount to any collaborative software project. Also, I've had to write quite a few dozen+ table join statements before, so that syntax will not scale (unless you like horizontal scrollbars).

Collectives™ on Stack Overflow

Sql server - Inner Join syntax with two joins

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related