SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Order of JOIN in multiple table joins


Order of JOIN in multiple table joins

Author
Message
purushottam2
purushottam2
SSC-Enthusiastic
SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)

Group: General Forum Members
Points: 187 Visits: 101
I want to know how order of joins executed in sql server. Suppose i have used 3 tables in join.

table1 JOIN table2 JOIN table3

Does sql server execute in same sequence as i have written, first get common data from table1 and table2 then rest result and table3?

As i know WHERE clause dont executed by sql in the sequence that we write in query, so it raise question for me does for join.
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25897 Visits: 17517
Not sure I understand the question here. Are you wanting to logically or how the actual internals work?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
purushottam2
purushottam2
SSC-Enthusiastic
SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)

Group: General Forum Members
Points: 187 Visits: 101
Yes, Because in one of my query, i am joining with 3 tables. And table 1 have the 1 million record, 2 Lakh and table3 have 10 thousands.

I am using inner join, so question came on my mind how order of join works. For the above case it is important to know.
Eugene Elutin
Eugene Elutin
SSCarpal Tunnel
SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)

Group: General Forum Members
Points: 4964 Visits: 5478
purushottam2 (1/28/2013)
Yes, Because in one of my query, i am joining with 3 tables. And table 1 have the 1 million record, 2 Lakh and table3 have 10 thousands.

I am using inner join, so question came on my mind how order of join works. For the above case it is important to know.


SQL query optimizer should (and most of the times it does) figure out the best sequence by itself.
Saying that, I used to follow my old custom to drive query from the smallest table/data set...

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
CapnHector
CapnHector
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1331 Visits: 1789
Eugene Elutin (1/28/2013)
purushottam2 (1/28/2013)
Yes, Because in one of my query, i am joining with 3 tables. And table 1 have the 1 million record, 2 Lakh and table3 have 10 thousands.

I am using inner join, so question came on my mind how order of join works. For the above case it is important to know.


SQL query optimizer should (and most of the times it does) figure out the best sequence by itself.
Saying that, I used to follow my old custom to drive query from the smallest table/data set...




I believe the SQL Optimizer can change the join order for queries that use only inner joins. Once you start introducing Outer Joins the order of the joins matters and a different order of Outer Joins could produce a different result set.

(Now, im currently looking for the source i read this from but having difficulty finding it again. If i find it ill edit this post with a link to the source.)


For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw

Need to Split some strings? Jeff Moden's DelimitedSplit8K
Jeff Moden's Cross tab and Pivots Part 1
Jeff Moden's Cross tab and Pivots Part 2
wolfkillj
wolfkillj
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1454 Visits: 2582
CapnHector (1/28/2013)
Eugene Elutin (1/28/2013)
purushottam2 (1/28/2013)
Yes, Because in one of my query, i am joining with 3 tables. And table 1 have the 1 million record, 2 Lakh and table3 have 10 thousands.

I am using inner join, so question came on my mind how order of join works. For the above case it is important to know.


SQL query optimizer should (and most of the times it does) figure out the best sequence by itself.
Saying that, I used to follow my old custom to drive query from the smallest table/data set...




I believe the SQL Optimizer can change the join order for queries that use only inner joins. Once you start introducing Outer Joins the order of the joins matters and a different order of Outer Joins could produce a different result set.

(Now, im currently looking for the source i read this from but having difficulty finding it again. If i find it ill edit this post with a link to the source.)


The optimizer can construct and use any execution plan that is logically equivalent to the T-SQL query, i.e., it can process joins in any order that yields the required result. The optimizer's entire purpose is to find the most efficient execution plan. It relies on statistics and metadata to make cost-based decisions about how to execute each query, and it almost always does an excellent job. We can get better execution plans by writing queries in a way that allows the optimizer to make full use of the resources available to it, though (read up on SARGable predicates for one example of this).

Jason Wolfkill
Blog: SQLSouth
Twitter: @SQLSouth
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search