CODEWITHSUNDEEP
sqlsql-servert-sql
Aiden
Aiden

Reputation: 139

SQL Server Execute Order

As I know the order of execute in SQL is

FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY

So I am confused with the correlated query like the below code.

Is FROM WHERE clause in outer query executed first or SELECT in inner query executed first? Can anyone give me idea and explanation? Thanks

SELECT 
    *, COUNT(1) OVER(PARTITION BY A) pt  
FROM
    (SELECT 
         tt.*,
         (SELECT COUNT(id) FROM t WHERE data <= 10 AND ID < tt.ID) AS A
     FROM  
         t tt
     WHERE  
         data > 10) t1

Upvotes: 0

Views: 366

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

As I know the order of execute in SQL is FROM-> WHERE-> GROUP BY-> HAVING -> SELECT ->ORDER BY

False. False. False. Presumably what you are referring to is this part of the documentation:

The following steps show the logical processing order, or binding order, for a SELECT statement. This order determines when the objects defined in one step are made available to the clauses in subsequent steps.

As the documentation explains, this refers to the scoping rules when a query is parsed. It has nothing to do with the execution order. SQL Server -- as with almost any database -- reserves the ability to rearrange the query however it likes for processing.

In fact, the execution plan is really a directed acyclic graph (DAG), whose components generally do not have a 1-1 relationship with the clauses in a query. SQL Server is free to execute your query in whatever way it decides is best, so long as it produces the result set that you have described.

Upvotes: 2

Related Questions