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|
|3||TOP||OUTER ( ALL JOINS)|
|5||OUTER ( ALL JOINS)||GROUP BY|
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.
SELECT * FROM Employee emp LEFT OUTER JOIN Location loc ON emp.LocationID = loc.LocationID WHERE loc.LocationID = 2