Of late, I penned down an article – SQL SERVER – Interesting Observation of ON Clause on LEFT JOIN – How ON Clause Effects Resultset in LEFT JOIN – which received a very intriguing comment from one of my regular blog readers Craig. According to him this phenomenon happens due to Logical Query Processing. His comment instigated a question in my mind. I have put forth this question to all my readers at the end of the article. Let me first give you an introduction to Logical Query Processing Phase.
What actually sets SQL Server apart from other programming languages is the way SQL Server processes its code. Generally, most programming languages process statement from top to bottom. By contrast, SQL Server processes them in a unique order which is known as Logical Query Processing Phase. These phases generate a series of virtual tables with each virtual table feeding into the next phase (virtual tables not viewable). These phases and their orders are given as follows:
1. FROM
2. ON
3. OUTER
4. WHERE
5. GROUP BY
6. CUBE | ROLLUP
7. HAVING
8. SELECT
9. DISTINCT
10 ORDER BY
11. TOP
As OUTER join is applied subsequent to ON clause, all rows eliminated by the ON clause will still be included by the OUTER join as described in the article SQL SERVER – Interesting Observation of ON Clause on LEFT JOIN – How ON Clause Effects Resultset in LEFT JOIN.
However, I am perplexed about the last two, ORDER BY and TOP. According to some people TOP comes first in logical query processing phase while others suggest that ORDER BY comes first. Now, here I’ve laid down my questions for you all to think about:
1) What is the correct answer for order query processing phase – ORDER BY or TOP?
2) How can we create an example to verify query processing phase for ORDER BY and TOP?
I will soon publish the answers I receive to the above questions on this blog, with due credit given to my readers.
Reference : Pinal Dave (https://blog.sqlauthority.com)
38 Comments. Leave new
Hi Pinal,
I would like to know what the sub query order. Please help me out about this.
Example Query:
Select * from Dept where DeptNo in (Select DeptNo from Emp)
I would like know how the above query will execute.
As of my knowledge, The above query will execute in the following Order:
1. Main Query From Clause
2. main Query Where Clause
3. Sub Query From Clause
4. Sub Query Where Clause
5. Sub Query Select
6. Main Query Select
Please Let me know my thought process is correct or not.
Thanks in advance.
Regards,
Sekhar Reddy
CREATE TABLE #Temp (
ID INT IDENTITY(1, 1)
,Ename VARCHAR(1000)
,Sal VARCHAR(100)
,DepAvgSal VARCHAR(100)
)
Insert into #Temp(Ename,Sal,DepAvgSal)
select ‘Nikhil’,10,20 union
select ‘Siva’,20,20 union
select ‘Sandeep’,’NA’,20
ALTER TABLE #Temp
ADD Emsg Varchar(2000)
–Process1:
Update #Temp
set Emsg=’Error’
where isnumeric(Sal)1
select * from #Temp
where
cast(Sal as float)<cast(DepAvgSal as float)
and isnull(Emsg,'')=''
–Process2:
select * from #Temp
where
cast(Sal as float)<cast(DepAvgSal as float)
and Emsg is null
OR
select * from #Temp
where
cast(Sal as float)<cast(DepAvgSal as float)
and isnumeric(Sal)=1
Drop Table #Temp
–My Question: Why we are getting "Error converting data type varchar to float." Error in Process1 why we are not getting in process2.
I am big fan of this blog
Thanks Garry.
I am a T SQL Newbie….but I think that the sequence depends on the output desired. – whether one wants the results to be ORDERed after selecting the TOP so many rows or maybe he wants to first ORDER them and then select the TOP so many rows….
maybe I am wrong
Can you tell the Logical Query Processing Phases order for Update,Insert,Delete Query
“You can download the poster from Itzik Ben-Gan’s T-SQL Querying page over here.”
This first line in your article contains a link to a web page that is not what the sentence claims it is. Can you update it to the correct page?
Thank you, I have fixed the issue.
Hi , This doubt may be a liitle of out of topic but i want to know what would be the order of execution if there are multiple and conditions like “where condn1 and condn2 and condn3 “? which condn is taken care first?
It makes all perfect sense to order data first and then get the top 5 rows .
If first execute the from then it mean it extract the all rows in buffer then it eliminating. Please correct me if i am wrong.