When we ask a developer what was the first language he Learned/ Worked when he joined the IT profession, he would most likely say its SQL and he will surely say he has written his share of complex queries. But very few really understand how a query we write is executed. By understanding the order of execution we can improve the performance of the queries we write. This very
Order of Execution:
|1||From||Choose and join tables to get base data.|
|2||Where||Filters the base data.|
|3||Group by||Aggregates the base data.|
|4||Having||Filters the aggregated data.|
|5||Select||Returns the final data.|
|6||Order by||Sorts the final data.|
|7||Limit/OFFSET||Limits the returned data to a row count.|
These are the first statement to be executed to fetch a working set of data that will eventually be modified to resultant set. So if we have multiple tables linked with the on clause then they will be executed in Top to bottom approach with linking the tables with on clause.
The Result from the FROM clause is stored in a virtual table which is then tested with the where conditions. All the rows which satisfy the WHERE conditions are kept while the rest of the data is discarded.
Now the new result set will be grouped based on the common values in the columns which are specified in the GROUP BY clause. GROUP BY will only work when we have aggregate functions and it will return a result set with only distinct values.
After Grouping the result set having clause is tested and only the rows which satisfy HAVING clause will be kept while other rows are discarded.
Now all the SELECT logic’s like (case, aliasing, as, distinct) will be applied on the result set. Only the columns specified in the select list will be kept which other columns are discarded.
If ORDER BY is specified then rows are arranged as per the defied order.
The rows that fall outside the range specified by the LIMIT and OFFSET are discarded and the final result is returned back.