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
clauseON
clauseOUTER
clauseWHERE
clauseGROUP BY
clauseHAVING
clauseSELECT
clauseDISTINCT
clauseORDER BY
clauseTOP
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 theSELECT
clause are evaluated (such as theWHERE
clause). - In some databases (such as MySQL), using aliases created in the
SELECT
list is allowed inGROUP BY
andHAVING
clause, even though these clauses appear before (and are evaluated earlier than) theSELECT
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, thisSELECT
clause 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 theWHERE
clause or theON
clause. This is true because there's no logical difference between theON
andWHERE
(except for when using anOUTER JOIN
orGROUP BY ALL
option). - The
DISTINCT
clause is redundant whenGROUP BY
is used. Therefore, it would not remove any rows from the recordset.
This post was published (and was last revised ) by Daniyal Hamid. Daniyal currently works as the Head of Engineering in Germany and has 20+ years of experience in software engineering, design and marketing. Please show your love and support by sharing this post.