Order Of Execution in SQL Query

This blog will mainly talk about the importance of Order of Execution in SQL query.

5/3/20242 min read

There are chances that you might have read a lot of blog posts on the order of execution in SQL. In this blog we will deep dive into the order of execution and also discuss why the order is critical for the execution of the query.

Let's see if you write a sql, then what is the order of execution:

FROM, JOIN, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, and LIMIT

Lets write the below query and understand the order of execution:

SELECT state_name,sum(value)

FROM `bigquery-public-data.america_health_rankings.ahr`

where state_name='Maine'

group by all

having sum(value)>1

order by state_name

LIMIT 1

Now if you see the above query then the below steps occurs:

  1. First the from clause i.e. the table `bigquery-public-data.america_health_rankings.ahr` gets loaded first.

  2. Then the where clause gets executed i.e. where state_name='Maine'(all the records where state_name='Maine' gets filtered from the table `bigquery-public-data.america_health_rankings.ahr`)

  3. Then the group by statement gets executed . In the above sql the group by doesn't makes any sense as the data is only for state_name='Maine'.

  4. Then the having clause gets executed which is "having sum(value)>1".

  5. In the penultimate step, the order by clause gets executed which is "order by state_name" in the above case.

  6. In the final step, the limit statement gets executed.

Now we should understand why our query engine is executing the sql statements in the above way. The answer is to optimize the execution and give the results in less amount of time.

Lets understand how it happens. For example lets change the order of execution . If we execute the "Order by" clause after the "from" clause, then basically it will start sorting all the records which will take a large amount of time but if we are executing the "order by" after the "where" clause then it sorts the records for the particular set of data.

Interview Question

Now a question arises , can we change the order of execution and the answer is "Yes" but it is not recommended as a part of standard practices as altering the order of execution can sometimes lead to incorrect results or performance issues if not done carefully

We can influence or control the order of execution in the following ways:

  1. Indexing: It changes the order of execution by allowing the database to quickly locate specific data, which can change the order in which certain clauses are executed.

  2. Subqueries and CTEs: They can be used to control the order of execution in particular use cases, like when a subquery is used to filter data before joining tables

  3. Database-specific optimizations: Few database systems or query optimizers may employ optimizations that can rearrange the order of execution for improved performance, as long as the final result is consistent with the logical order

.