Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
O11Y
Starting Member
3 Posts |
 Posted - 2006-10-16 : 11:06:12
|
| 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 jointable3 on t2.b = t3.b inner jointable4 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.ainner join table4 on t4.a = t1.awould 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
30421 Posts |
Posted - 2006-10-16 : 11:23:35
|
| 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 LarssonHelsingborg, Sweden |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-10-16 : 11:35:52
|
| 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. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
|
|
|
Join precendence