SQL Statement Query Clause Evaluation Order – Which Part of SQL Statements Execute First and in What Order?

Which Part of SQL Statements Execute First and in What Order.

It is very important to understand which parts of the SQL Statement executes first and which part comes next until full completion.

The diagram below gives an overview of the execution order.

The steps in which the SQL statement is executed are as below:

Step 1: FROM clause (cross product and join operators)

Step 2: WHERE clause (row conditions)

Step 3: GROUP BY clause (sort on grouping columns, compute aggregates)

Step 4: HAVING clause (group conditions)

Step 5: ORDER BY clause

Step 6: eliminate columns not in SELECT

This is Conceptual not actual as DBMS use many shortcuts

A bit of advice is that.

Row operations in the FROM and WHERE CLAUSES occur first and Errors in formulation usually occur in row operations. Hence it is advisable to always use small tables to understand relationship of rows operations (FROM , WHERE) and from there move on to the group operations (which are GROUP , HAVING operations ).

For large problems, execute row operations separately to ensure that results are being returned are what you expect before grouping the data.

Grouping only occurs one time.

This  advice and tip is part of Data Warehousing for Business Intelligence by  University of Colorado System on Coursera