SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

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

O11Y
Starting Member

3 Posts

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

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.

Cheers,

Olly


SwePeso
Patron Saint of Lost Yaks

Sweden
30281 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

nr
SQLTeam MVY

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

X002548
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


Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page
  Previous Topic Topic Next Topic  
 New 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.08 seconds. Powered By: Snitz Forums 2000