InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 07:15:15 GMT20http://www.sqlservercentral.com/Forums/Topic1412487-392-1.aspx[quote][b]CapnHector (1/28/2013)[/b][hr][quote][b]Eugene Elutin (1/28/2013)[/b][hr][quote][b]purushottam2 (1/28/2013)[/b][hr]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.[/quote]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...[/quote]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.)[/quote]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).Tue, 29 Jan 2013 09:30:17 GMTwolfkilljhttp://www.sqlservercentral.com/Forums/Topic1412487-392-1.aspx[quote][b]Eugene Elutin (1/28/2013)[/b][hr][quote][b]purushottam2 (1/28/2013)[/b][hr]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.[/quote]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...[/quote]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.)Mon, 28 Jan 2013 11:53:52 GMTCapnHectorhttp://www.sqlservercentral.com/Forums/Topic1412487-392-1.aspx[quote][b]purushottam2 (1/28/2013)[/b][hr]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.[/quote]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...Mon, 28 Jan 2013 09:31:04 GMTEugene Elutinhttp://www.sqlservercentral.com/Forums/Topic1412487-392-1.aspxYes, 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.Mon, 28 Jan 2013 09:27:55 GMTpurushottam2http://www.sqlservercentral.com/Forums/Topic1412487-392-1.aspxNot sure I understand the question here. Are you wanting to logically or how the actual internals work?Mon, 28 Jan 2013 09:23:43 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1412487-392-1.aspxI want to know how order of joins executed in sql server. Suppose i have used 3 tables in join.table1 JOIN table2 JOIN table3Does 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.Mon, 28 Jan 2013 09:04:25 GMTpurushottam2