Logical order of query execution in SQL

What is the logical order of query execution? This is a very commonly asked interview question. Actually the way we write down a query, it does not execute at the same way inside the database engine. For us, the query execution is from top to bottom but logically it’s different. Most of us we think, SELECT runs first since it is the first statement in every SQL statement but NO. Check out logical and physical order of query processing below

Query processing order is ANSI standardized so it’s same across all RDBMS.

# PHYSICAL ORDER LOGICAL ORDER
1 SELECT FROM
2 DISTINCT ON
3 TOP OUTER ( ALL JOINS)
4 FROM WHERE
5 OUTER ( ALL JOINS) GROUP BY
6 ON CUBE|ROLLUP|GROUPING SETS 
7 WHERE HAVING
8 GROUP BY SELECT
9 CUBE|ROLLUP|GROUPING SETS DISTINCT
10 HAVING ORDER BY
11 ORDER BY TOP

However this logical order may vary in some cases, like an example below, the WHERE clause will execute first before JOIN. SQL Server optimizer will try to filter the records first before making a JOIN rather making a JOIN on all data then filter out the result. This is an ongoing debate however but not yet concluded in all RDBMS platforms.

 

Prasad Sahoo

Prasad Sahoo, is an enthusiast of Microsoft technologies in general and a passionate database professional, mainly focusing on SQL Server performance tuning and business intelligence. He has contributed a long time of his career working on SQL Server and other RDBMS. If he is not busy with SQL stuff then he must be watching some Hollywood movies.

2 thoughts on “Logical order of query execution in SQL

Leave a Reply

%d bloggers like this: