What Is The Order Of Execution Of An SQL Query?

Knowing the order in which an SQL query is executed can help us a great deal in optimizing our queries. This is especially true with large and complex queries where knowing the order of execution can save us from unwanted results, and help us create queries that execute faster.

SELECT Statement Execution Order

Consider the SQL SELECT statement syntax:

SELECT DISTINCT <TOP_specification> <select_list>
FROM <left_table>
<join_type> JOIN <right_table>
ON <join_condition>
WHERE <where_condition>
GROUP BY <group_by_list>
HAVING <having_condition>
ORDER BY <order_by_list>

In SQL, the first clause that is processed is the FROM clause, while the SELECTclause, which appears first in an SQL query, is processed much later. The phases involved in the logical processing of an SQL query are as follows:

1. FROM clause
2. ON clause
3. OUTER clause
4. WHERE clause
5. GROUP BY clause
6. HAVING clause
7. SELECT clause
8. DISTINCT clause
9. ORDER BY clause
10. TOP clause

In practice this order of execution is most likely unchanged from above. With this information, we can fine-tune our queries for speed and performance.

You must remember though, that the actual physical execution of an SQL statement is determined by the database's query processor and the order of execution can vary in different DBMS.

---

Tips & Cautions

• Aliases created in the SELECT list cannot be used by earlier steps. This restriction is imposed because the column value may not yet have been determined when the clauses that appear before the SELECT clause are evaluated (such as the WHERE clause).
• In some databases (such as MySQL), using aliases created in the SELECT list is allowed in GROUP BY and HAVING clause, even though these clauses appear before (and are evaluated earlier than) the SELECT clause.
• Expression aliases cannot be used by other expressions within the same SELECT list. This is because the logical order in which the expressions are evaluated does not matter and is not guaranteed. For example, this SELECTclause might not work as expected, and is therefore, not supported: SELECT a + 1 AS x, x + 1 AS y
• When using an INNER JOIN, it doesn’t matter if you specify your logical expressions in the WHERE clause or the ON clause. This is true because there's no logical difference between the ON and WHERE (except for when using an OUTER JOIN or GROUP BY ALL option).
• The DISTINCT clause is redundant when GROUP BY is used. Therefore, it would not remove any rows from the recordset.