Quick Look Into SQL Order Execution
How to understand better the execution of your everyday code.
Have you ever wondered how SQL executes the queries that you write? In fact, a particular order must be followed to execute it, even if we can’t see it. It is very different from the order we write the clauses, keep reading to find out why this order is very logical.
We will use this query as an example to illustrate the ordering:
SELECT TOP (10)
FROM [Table1] t1
JOIN [Table2] t2 ON t1.FK = t2.PK
WHERE [Column1] = 'John'
GROUP BY [Column1]
HAVING SUM([Column2])> 100
ORDER BY [Column1]
The execution order for SQL queries is the following:
1- FROM (and JOINS):
First of all, SQL specifies the table on which the query needs to retrieve the data. If you have JOINS in your query, they will also be executed at this step. In our example, we specify Table1 and join it to Table2.
Secondly, we use the WHERE clause after the tables are joined to establish the working dataset and filter the records according to the conditions given in the query. In our example, we filter the results so that Column1 has the value ‘John’.
3- GROUP BY:
The third step is grouping the data we specified previously by breaking up the data into distinct chunks. These groups are under the criteria we defined. In this case we are grouping our records by the values in Column1.
Having is applied after GROUP BY because it uses a condition that is applied in GROUP BY, it can be used with aggregate functions. In our example, we want to filter our data where the Column2 SUM is higher than 100.
It is at this moment that our results are finally computed. In the case that you are using DISTINCT in your query, it will execute after right after the SELECT statement. Here you can specify all the columns that you want to be returned.
6- ORDER BY:
The next step after your data is selected, will be to order it under the rules you specified in your query. In the example above, we want to order by Column1.
In the end, every row above the number we selected at TOP is not selected. Returning only the first 10 records in our example.
As we have seen, this order differs from the way we are used to writing our SQL queries. Here’s a quick diagram to showcase the differences.
I hope that you have found this article useful and learned how SQL executes your code internally.
Have a great rest of your day!