Order of Execution in SQL
An SQL query comprises various clauses, such as SELECT, FROM, WHERE, GROUPBY, HAVING, and ORDERBY clauses. Each clause has a specific role in the query. In this article, we will discuss the importance and implications of the order of execution in SQL.
SQL, or Structured Query Language, is a standard language for managing data (create/read/update/delete) in a relational database. Using SQL, you can create a new table (or database) and insert, update, retrieve, or delete data from the existing table.
However, you must follow an order when writing any SQL query to fetch or insert data from the table. The order of Execution in SQL is similar to the mathematical operation BODMAS.
In this article, we will learn the importance and implications of the order of execution (or SQL order of operation) with an example. Understanding the order of operation allows you to write more efficient and accurate queries. It also helps you predict the outcome of queries, troubleshoot issues, and optimize performance.
Must Check: SQL Online Course and Certification
Must Check: Database and SQL Online Course and Certifications
So, let’s begin the article.
Defining Order of Execution in SQL
An SQL query comprises various clauses like SELECT, FROM, WHERE, GROUPBY, HAVING, and ORDERBY clauses. Each clause has a specific role in the query. Let’s understand each of them briefly.
When you write any query, your query is processed in the following steps:
- Getting Data (FROM/JOIN)
- Row Filter (WHERE)
- Grouping (GROUP BY)
- Group Filter (HAVING)
- Return Expression (SELECT)
- Order & Paging (ORDER BY & LIMIT/OFFSET)
| Clause | Function |
| FROM / JOIN | When you write any query, SQL starts by identifying the tables for the data retrieval and how they are connected. |
| WHERE | It acts as a filter; it filters the record based on the conditions specified by the users. |
| GROUP BY | The filtered data is grouped based on the specified condition. |
| HAVING | It is similar to the WHERE clause but applied after grouping the data. |
| SELECT | The clause selects the columns to be included in the final result. |
| DISTINCT | Remove the duplicate rows from the result. Once you apply this clause, you are only left with distinct records. |
| ORDER BY | It sorts the results (increasing/decreasing/A->Z/Z->A) based on the specified condition. |
| LIMIT / OFFSET | It determines the number of records to return and from where to start. |
You have clearly understood the theoretical aspect of the order of execution in SQL until now. Let’s take an example to better understand the concept.
Let’s consider a simple dataset with two tables: Customers and Orders.
- The Customers table has 5 columns: customer_id, first_name, last_name, age, and country.
- Orders Table has 4 columns: order_id, item, amount, customer_id
Customers Table
| customer_id | first_name | last_name | age | country |
|---|---|---|---|---|
| 1 | John | Doe | 31 | USA |
| 2 | Robert | Luna | 22 | USA |
| 3 | David | Robinson | 22 | UK |
| 4 | John | Reinhardt | 25 | UK |
| 5 | Betty | Doe | 28 | UAE |
Orders Table
| order_id | item | amount | customer_id |
|---|---|---|---|
| 1 | Keyboard | 400 | 4 |
| 2 | Mouse | 300 | 4 |
| 3 | Monitor | 12000 | 3 |
| 4 | Keyboard | 400 | 1 |
| 5 | Mousepad | 250 | 2 |
Problem Statement: Find the amount spent by each customer in the USA.
SELECT Customers.first_name, Customers.last_name, SUM(Orders.Amount) as AmountFROM CustomersJOIN Orders ON Customers.customer_id = Orders.customer_idWHERE Customers.country = 'USA'GROUP BY Customers.first_name, Customers.last_nameORDER BY Amount DESC;
Output
| first_name | last_name | Amount |
|---|---|---|
| John | Doe | 400 |
| Robert | Luna | 250 |
Explanation
- FROM and JOIN: We start by identifying the ‘Customers‘ and ‘Orders‘ tables and joining them on ‘customer_id‘.
- WHERE: It will filter the record to include only those where ‘country‘ = ‘USA‘.
- GROUP BY: Group the remaining entries (after filtering by WHERE clause) by ‘first_name‘ and ‘last_name‘.
- SELECT: SELECT the ‘first_name‘, ‘last_name‘, and the sum of ‘Amount‘ for each group.
- ORDER BY: Finally, the result is sorted by ‘Amount‘ in descending order.
Now, let’s take an example and reshuffle the order of execution in sql.
Case-1: Let you want to filter the record based on the ‘Amount’ using the WHERE clause.
SELECT Customers.first_name, Customers.last_name, SUM(Orders.Amount) as AmountFROM CustomersJOIN Orders ON Customers.customer_id = Orders.customer_idWHERE Orders.Amount >300GROUP BY Customers.first_name, Customers.last_nameORDER BY Amount DESC;
Output
| first_name | last_name | Amount |
| David | Robinson | 12000 |
| John | Doe | 400 |
| John | Reinhardt | 400 |
Case-2: Filter the record based on the ‘Amount’ using the HAVING clause.
SELECT Customers.first_name, Customers.last_name, SUM(Orders.Amount) as AmountFROM CustomersJOIN Orders ON Customers.customer_id = Orders.customer_idGROUP BY Customers.first_name, Customers.last_nameHAVING Amount > 300ORDER BY Amount DESC;
Output
| first_name | last_name | Amount |
| David | Robinson | 12000 |
| John | Reinhardt | 700 |
| John | Doe | 400 |
Now, let’s see what happened in both cases:
Since the WHERE clause is processed before the SELECT clause in the Order of Execution, so, in the first case, SQL won’t recognize the Amount and will give the error.
It just filters out the record of the customer who purchased orders greater than 300.
However, the best way to filter the aggregate function is to use the HAVING clause.
Since the HAVING clause is processed after the GROUP BY clause. So, in the second case, the HAVING clause filters the group to include only those where the total Amount is greater than 300.
Best-suited Database and SQL courses for you
Learn Database and SQL with these high-rated online courses
Tips for Writing Efficient SQL Queries
- The first thing you must know while writing the SQL queries is the correct order of SQL query execution.
- Since a lot of people think SQL processes queries from top to bottom as they have written.
- But SQL processes queries in the order: FROM, JOIN, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, and finally, LIMIT/OFFSET.
- One of the common mistakes is using aliases defined in the SELECT clause within the WHERE clause.
- Because SQL processes the WHERE clause before the SELECT clause.
- Use the HAVING clause if you need to filter your query based on the result of an aggregate function.
- While joining multiple tables, start with the smallest table or the table that allows you to filter out the most data early on.
Conclusion
An SQL query comprises various clauses, such as SELECT, FROM, WHERE, GROUPBY, HAVING, and ORDERBY clauses. Each clause has a specific role in the query.
In this article, we have learned the importance and implications of order of execution (or SQL order of operation) with an example. Understanding the order of operation allows you to write more efficient and accurate queries. It also helps you predict the outcome of queries, troubleshoot issues, and optimize performance.
Hope you will like the article.
Keep Learning!!
Keep Sharing!!
Related Reads
Read Later
FAQs
What is the full form of SQL?
The full form of SQL is Structured Query Language. It is a standard language for managing data in a relational database. Using SQL, you can create a new table, insert, update, retrieve, or delete data from the existing table. The order of Execution in SQL is similar to the mathematical operation BODMAS.
What is the order of execution in SQL?
An SQL query comprises of various clauses like SELECT, FROM, WHERE, GROUPBY, HAVING, and ORDERBY clauses. Each clause has a specific role in the query. Understanding the order of operation allows you to write more efficient and accurate queries. It also helps to predict the outcome of queries, troubleshoot issues, and optimize performance.
What is the correct order of execution for a given query?
The correct order of execution in SQL is FROM, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY and LIMIT.
What order does ORDER BY query in SQL?
The ORDER BY clause comes after FROM, WHERE, GROUP BY, HAVING, SELECT, DISTINCT.
Top Picks & New Arrivals
Comments
(1)
V
5 months ago
Report
Reply to Vinay Anand