CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2005join
IronicMuffin
IronicMuffin

Reputation: 4192

Is there documentation on/can someone explain a nested join in TSQL?

I'm not quite sure how to describe this, and I'm not quite sure if it's just syntactical sugar. This is the first time I've seen it, and I'm having trouble finding a reference or explanation as to the why and what of it.

I have a query as follows:

select * from
     table1
          join table2 on field1 = field2
          join (
               table3 
                    join table4 on field3 = field4
                    join table5 on field5 = field6
               ) on field3 = field2 
               -- notice the fields in the parens and outside the parens 
               -- are part of the on clause

Are the parentheses necessary? Will removing them change the join order? I'm in a SQL Server 2005 environment in this case. Thanks!

Upvotes: 1

Views: 134

Answers (3)

Aghilas Yakoub
Aghilas Yakoub

Reputation: 28970

In this case they are not necessary:

select * from table1
join table2 on field1 = field2
join table3 on field3 = field2 
join table4 on field3 = field4
join table5 on field5 = field6

Produces the same result.

Upvotes: 2

Nicholas Carey
Nicholas Carey

Reputation: 74227

Join order should make no difference in the result set of a query using natural joins (outside of column order). The query

select *
from t1
join t2 on t2.t1_id = t1.id

produces the same result set as

select * from t2 join t1 on t1.id = t2.t1_id

If you're using outer joins and change the order of the tables in the from clause, naturally the direction of the outer join must change:

select *
from      t1
left join t2 on t2.t1_id = t1.id

is the same as

select *
from       t2
right join t1 on t1.id = t2.t1_id

However, if you see a subquery used as a table, with syntax like

select *
from t1
join ( select t2.*
       from t2
       join t3 on t3.t2_id = t2.id
       where t3.foobar = 37
     ) x on x.t1_id = t1.id

You'll note the table alias (x) assigned to the subquery above.

What you have is something called a derived table (though some people call it a virtual table). You can think of it as a temporary view that exists for the life of a query. It's particularly useful when you need to filter something based on something like the result of a aggregration (group by).

The T-SQL documentation on the select, under the from clause goes into the details:

Upvotes: 3

Jon Hanna
Jon Hanna

Reputation: 113242

It's not necessary in this case.

It's necessary (or at the very least, a lot simpler) in some others, especially where you name the nested call:

select table1.fieldX, table2.fieldY, sq.field6 from
table1 join table2 on field1 = field2
join ( select
   top 1 table3.field6
   from table3 join table4
   on field3 = field4
   where table3.field7 = table2.field8
   order by fieldGoshIveUsedALotOfFieldsAlready
           ) sq on sq.field6 = field12345

The code you had could have been:

  1. Like the above once, and then refactored.
  2. Machine produced.
  3. Reflecting the thought process of the developer as he or she arrived at the query, as they thought of that part of the larger query as a unit, then worked it into the larger query.

Upvotes: 2

Related Questions