Do You Know The Actual Order Of a Select Statement

Today many developers in the world are familiar with SQL, and most developers have written a SELECT statement at least once in their lives.

Alright, do we know the actual order of execution of the SELECT statement?

In this blog, I will show you the execution order of a SELECT statement by database in a nutshell.

Defining Execution Order

Initially, we are going to give you the execution order and then explain it with more example. As you see below, the image shows the execution order of a simple SELECT statement which has a WHERE condition.

No matter how complex a query you create. Your database will execute the query with a certain order specified beforehand by the standard SQL order of execution.

Let’s take a look at an example. We have a SELECT query that needs to retrieve data from a CONSUMER table based on specific conditions.

SELECT * FROM CONSUMER WHERE NAME = 'nurullah'

The FROM clause plays a crucial role in selecting and joining tables. It is the first part of a query that gets executed. This means that when you’re executing queries, they take place right at the beginning. In this example, CONSUMER table are being executed at first.

The second clause that is going to execute is WHERE clause. It filters data that comes from the table chosen in the FROM clause. It will return only rows that meet the specified criteria.

Finally, SELECT clause is executed.

GROUP BY and HAVING CLAUSE

Now we will go through a more complex SELECT statement where we will be adding the GROUP BY and HAVING clauses.

The GROUP BY clause is used to group data in a table based on one or more columns and the HAVING clause is used to filter the results of a GROUP BY clause based on a specified condition.

In the example below, after executing FROM and WHERE clause, the filtered data is grouped by name using GROUP BY clause and with HAVING clause we specify condition on grouped data.

SELECT NAME, SUM(ORDER_AMOUNT) as total_amount
  FROM ORDERS
    WHERE INSERT_DATE >= '2020-01-01' 
      GROUP BY NAME
        HAVING SUM(ORDER_AMOUNT) > 400

Let’s Add ORDER BY and LIMIT

ORDER BY sorts the final data based on specific column and LIMIT clause limits the returned data to a row count. They are lastly executed after SELECT clause.

Finally, you can think of JOIN and FROM together. They are executed before WHERE clause.