<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2008
»
T-SQL (SS2K8)
»
Order of JOIN in multiple table joins
Order of JOIN in multiple table joins
Rate Topic
Display Mode
Topic Options
Author
Message
purushottam2
purushottam2
Posted Monday, January 28, 2013 9:04 AM
Valued Member
Group: General Forum Members
Last Login: Monday, April 29, 2013 1:59 AM
Points: 53,
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.
Post #1412487
Sean Lange
Sean Lange
Posted Monday, January 28, 2013 9:23 AM
SSCrazy Eights
Group: General Forum Members
Last Login: Today @ 3:12 PM
Points: 8,957,
Visits: 8,523
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 Moden's
splitter
.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Post #1412498
purushottam2
purushottam2
Posted Monday, January 28, 2013 9:27 AM
Valued Member
Group: General Forum Members
Last Login: Monday, April 29, 2013 1:59 AM
Points: 53,
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.
Post #1412503
Eugene Elutin
Eugene Elutin
Posted Monday, January 28, 2013 9:31 AM
SSCrazy
Group: General Forum Members
Last Login: Today @ 2:08 AM
Points: 2,596,
Visits: 4,506
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!"
(So many miracle inventions provided by MS to us...)
How to post your question to get the best and quick help
Post #1412505
CapnHector
CapnHector
Posted Monday, January 28, 2013 11:53 AM
SSC Eights!
Group: General Forum Members
Last Login: Thursday, June 06, 2013 3:00 PM
Points: 939,
Visits: 1,713
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
Jeremy Oursler
Post #1412588
wolfkillj
wolfkillj
Posted Tuesday, January 29, 2013 9:30 AM
Right there with Babe
Group: General Forum Members
Last Login: Today @ 12:30 PM
Points: 768,
Visits: 1,496
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).
Post #1413141
« Prev Topic
|
Next Topic »
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
