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
SELECT clause, 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:
- FROM clause
- ON clause
- OUTER clause
- WHERE clause
- GROUP BY clause
- HAVING clause
- SELECT clause
- DISTINCT clause
- ORDER BY clause
- 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
SELECTlist 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
SELECTclause are evaluated (such as the
- In some databases (such as MySQL), using aliases created in the
SELECTlist is allowed in
HAVINGclause, even though these clauses appear before (and are evaluated earlier than) the
- Expression aliases cannot be used by other expressions within the same
SELECTlist. 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
WHEREclause or the
ONclause. This is true because there's no logical difference between the
WHERE(except for when using an
GROUP BY ALLoption).
DISTINCTclause is redundant when
GROUP BYis used. Therefore, it would not remove any rows from the recordset.