Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Join precendence
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

3 Posts

Posted - 10/16/2006 :  11:06:12  Show Profile  Reply with Quote

I've been googling for quite a while trying to find information on this but i'm having no luck.

What I'm trying to find out is when joining 3+ tables together how can you explicitly specify the order in which the joins are applied? I understand that the basic order is "FROM" then "WHERE" then "HAVING". I've also read that the query optimizer does a bit of work. but if I have a more complex join e.g.:

table1 inner join
table2 on t1.a = t2.a inner join
table3 on t2.b = t3.b inner join
table4 on t4.a = t1.a

(so here I'm joining table1 to table2, then table2 to table3, and then table4 to table1.)

what exactly specifys the order in which these tables are joined? Does it just join the tables in order I've referenced the table? Or perhaps the order of the "ON" clauses? What about if I did this:

table1 inner join
(table2 inner join table3 on t2.b = t3.b) on t1.a = t2.a
inner join table4 on t4.a = t1.a

would the parenthes make any difference whatsoever?

If someone could explain or point me in the direction of some documentation on this then I'd be very grateful.



Patron Saint of Lost Yaks

30421 Posts

Posted - 10/16/2006 :  11:23:35  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Try the different solutions and check out the query plan.
That might give you a hint of what happens behind the scene.
Also use SET STATISTICS flag to get an idea of readings from the database.

Peter Larsson
Helsingborg, Sweden
Go to Top of Page


United Kingdom
12543 Posts

Posted - 10/16/2006 :  11:35:52  Show Profile  Visit nr's Homepage  Reply with Quote
It will join in the way it thinks best.
You can force your order via a force plan hint but I wouldn't advise it.

If it's causing problems then try derived tables (but it'll have a good go at spotting this).
Or better use a temp table which you can index.

Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

Not Just a Number

15586 Posts

Posted - 10/16/2006 :  11:58:00  Show Profile  Reply with Quote
Are there any other predicates?

If not I think it would always do a merge scan join



Hint: Want your questions answered fast? Follow the direction in this link

Add yourself!

Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000