Click here to Skip to main content
14,328,801 members
Rate this:
Please Sign up or sign in to vote.
Hi All,

I have a question:

In a SQL Server query when we have join and where clause, which will be verified first for the output joins condition or where clause condition?

Example would be help for understanding.

Thanks
Govardhan
Posted
Rate this:
Please Sign up or sign in to vote.

Solution 4

Definitely Where clause would be verified before the Join clause for output execution.
   
v2
Rate this:
Please Sign up or sign in to vote.

Solution 3

Suppose we have a table Category and another table Item.
In Item table we have CategoryId column which is a foreign key derived from Category table.

The following is the query:
SELECT * FROM Item A
JOIN Category B ON A.CategoryId = B.CategoryId
WHERE ItemName LIKE '%Tea%'

The above query is executed in the following sequence:
1. A dataset is created from Item Table and incase there are Columns in where clause, these are also resolved while creating the dataset. Hence,
A dataset will be created from Item table in which all the items contain word "Tea".
2. The same is applied for the table Category.
3. Then both the datasets are joined to form single dataset consisting of data and at this point the joining condition is resolved.
4. Finally the dataset is returned.

The above sequence can be verified for a query by viewing its execution plan. I used SQL 2008 for this.

Hope this answer your question.
   
Comments
Espen Harlinn 13-Jan-11 5:13am
   
There's no way to turn off the optimizer, but you may supply it with hints. Sql Server looks at the whole query statement, dissects the logic and tries it's best to find the optimal execution plan. The optimal execution plan might be what you describe, but it's not guaranteed.
Rate this:
Please Sign up or sign in to vote.

Solution 1

It depends - internally SQL server will rewrite your query to optimize performance. It looks at meta information and collected statisticts - and then creates execution paths based on the available information.

Take a look at:
http://www.sql-server-performance.com/tips/hints_general_p1.aspx[^]

Regards
Espen Harlinn
   
v2
Rate this:
Please Sign up or sign in to vote.

Solution 2

Have a look and read here: SELECT (Transact-SQL)[^]

All your questions about the order should be answered via it!
P.S.: There is a link[^] for examples too, at the bottom of article.
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100