SQL Query Execution Order
FREE
Overview
SQL queries are used to access a set of records present in our database tables. SQL queries are made up of one or more clauses. A particular order must be followed to execute these clauses to obtain correct results. This order of execution is known as SQL query execution order.
Introduction
In this digital era, we don't want to waste our time searching for names having salaries above Rs.10000. We want our results to be optimized without wasting any time and not in a tedious manner. Here comes the SQL query execution order, sometimes also called SQL order of operations, so that the data we are looking for is retrieved in the most optimized way and takes the least execution time possible.
SQL query execution order means optimizing our search results from the database or where the query clauses are evaluated according to the requirements.
Like how we plan something step by step and reach the final result, similarly, we use clauses in a particular order known as the SQL query execution order. Here is the table that shows the order in which the clauses are used.
| Order | Clause | Function |
|---|---|---|
| 1 | FROM | Tables are joined to get the base data. |
| 2 | WHERE | The base data is filtered. |
| 3 | GROUP BY | The filtered base data is grouped. |
| 4 | HAVING | The grouped base data is filtered. |
| 5 | SELECT | The final data is returned. |
| 6 | ORDER BY | The final data is sorted. |
| 7 | LIMIT | The returned data is limited to row count. |
Let us assume the following two tables: Customers and Orders.
The customer's table contains data on the customer's name and IDs. The order table contains the details of the customer's order; for example, it contains the amount and the order id of the customer's id.
Note: We will be using these two databases in the article.
Table 1 - Customers
| customer_id | first_name |
|---|---|
| 1 | John |
| 2 | Robert |
| 3 | David |
| 4 | John |
| 5 | Betty |
Table 2 - Order
| order_id | amount | customer_id |
|---|---|---|
| 1 | 200 | 4 |
| 2 | 500 | 10 |
| 3 | 300 | 3 |
| 4 | 800 | 1 |
| 5 | 150 | 2 |
So, here is the question that we will solve using the SQL query execution order.
Let’s say that we want to get the customers' order details where the order id is less than equal to 4 with the limit set as 2. Also, the result should be in ascending order.
This is the query that we will understand step by step.
SELECT Clause in MySQL
The SELECT statement is used to select data from the database. To select complete data, we use the * operator.
Suppose we want only first_name data from the Customer table. To do that, we will run the following command.
Result
In the result, we will get the first_name column only.
| first_name |
|---|
| John |
| Robert |
| David |
| John |
| Betty |
FROM and JOINs in SQL
The FROM clause is used to specify the table on which the query needs to do the operation. JOIN joins two or more tables.
Here we will be fetching the customer_id and first_name columns from the Customers table and amount and order_id from the Orders table using the SELECT clause from the table Customers, and joining the Orders table using JOIN clause.
Let's look at the following query and the results obtained after running this query.
Query
Result
In the result, we will get the customer_id and first_name columns from the Customers table and also the two columns amount and order_id that we have joined from the Orders table.
| customer_id | first_name | amount | order_id |
|---|---|---|---|
| 5 | Betty | 200 | 1 |
| 4 | John | 200 | 1 |
| 3 | David | 200 | 1 |
| 2 | Robert | 200 | 1 |
| 1 | John | 200 | 1 |
| 5 | Betty | 500 | 2 |
| 4 | John | 500 | 2 |
| 3 | David | 500 | 2 |
| 2 | Robert | 500 | 2 |
| 1 | John | 500 | 2 |
| 5 | Betty | 300 | 3 |
| 4 | John | 300 | 3 |
| 3 | David | 300 | 3 |
| 2 | Robert | 300 | 3 |
| 1 | John | 300 | 3 |
| 5 | Betty | 800 | 4 |
| 4 | John | 800 | 4 |
| 3 | David | 800 | 4 |
| 2 | Robert | 800 | 4 |
| 1 | John | 800 | 4 |
| 5 | Betty | 150 | 5 |
| 4 | John | 150 | 5 |
| 3 | David | 150 | 5 |
| 2 | Robert | 150 | 5 |
| 1 | John | 150 | 5 |
The result will be the Cartesian product of both tables, as shown in the above image.
Now we will run the following command where the ON clause is used to join all the different columns after the From and Join Clauses are executed. The ON clause is used to join specific columns. It helps in filtering the conditions for the WHERE clause.
After the execution of the above command, we get the following result.
Result
| customer_id | first_name | amount | order_id |
|---|---|---|---|
| 4 | John | 200 | 1 |
| 3 | David | 300 | 3 |
| 1 | John | 800 | 4 |
| 2 | Robert | 150 | 5 |
The above result shows the results only where both the tables have the same customer_id. Rest rows are removed where customer_id is not the same.
WHERE Clause in SQL
The WHERE clause filters certain records that meet the conditions mentioned in the query. It is evaluated second after the FROM clause.
We can use the following operators with the WHERE clause.
| Operator | Description |
|---|---|
| = | Equal |
| >= | Greater than equal to |
| <= | less than equal to |
| > | Greater than |
| < | Less than |
| <> | Not equal |
| BETWEEN | Between a range |
| Like | Search for a pattern |
Now we will run the next command and visualize the result.
Query
Here, we will filter the results by the order_id column where order_id is less than a certain number.
Result
| customer_id | first_name | amount | order_id |
|---|---|---|---|
| 1 | John | 800 | 4 |
| 3 | David | 300 | 3 |
| 4 | John | 200 | 1 |
The result shows that the table is filtered where the order_id is less than or equal to 4 only.
GROUP BY Clause in SQL
After the data is filtered using the WHERE clause, we group the sorted data using the GROUP BY clause.
- Group By clause is used to group the data.
- Partition the set of tuples into groups based on certain criteria.
- Groups are formed based on certain attributes.
- Aggregate functions are calculated for each group.
Suppose we want to determine the maximum amount of orders placed by customers.
To find that, we will run the following query.
Query
Result
| Maximum_amount | first_name |
|---|---|
| 800 | John |
| 300 | David |
From the above result, we can conclude that the maximum amount is grouped according to the customers' names.
HAVING Clause in SQL
After the GROUP BY clause, if we are not getting the expected result. The HAVING clause is used when the WHERE clause cannot be used with the aggregate functions. It takes a certain condition that is applied in the GROUP BY clause.
Syntax
We use the HAVING clause in the following manner.
This is applied to the filtered data and not the single rows.
Suppose we want to get the maximum amount that is less than or equal to a certain amount. To get that, we will execute the following query.
Query
In this example, we have used MAX to get the maximum amount of orders that are less than or equal to 400.
Result
| Maximum_amount | first_name |
|---|---|
| 300 | David |
From the above result, we can see that the maximum amount that was less than 400 was only David's, i.e., 300.
ORDER BY Clause in SQL
ORDER BY clause is used to sort the data in ascending or descending order. We use the keyword ASC to sort the data in ascending order and the keyword DESC to sort the data in descending order.
For example,
Let's execute the ORDER BY clause in our example. We want the order to be in ascending order by order_id.
Result
| customer_id | first_name | amount | order_id |
|---|---|---|---|
| 4 | John | 200 | 1 |
| 1 | John | 150 | 3 |
| 3 | David | 300 | 3 |
| 1 | John | 800 | 4 |
We will get the result as shown. The result is in ascending order after the execution of the WHERE clauses result.
LIMIT / OFFSET in SQL
After the result is sorted, it might be the case that we want a limited number of rows in the result because the data is too large to analyze with perfection. We can do that by using the LIMIT and OFFSET clauses. For example,
After sorting our result using the ORDER BY clause, we got two rows. Suppose that we want only one row to be shown in the result.
Suppose we want the result after row 1 and to a certain limit that is 3 for now. So, to get the data after a certain amount, we set the offset value.
Syntax
We will run the command where the limit is set as two, and the data starts after row 2. To do that, we will run the following query.
Query
Result
| customer_id | first_name | amount | order_id |
|---|---|---|---|
| 3 | David | 300 | 3 |
| 1 | John | 800 | 4 |
As a result, we will only get the results after row two and till two rows.
Conclusion
- The SQL query execution order is the order of clauses to execute while sorting our data.
- We execute the From/Join clause first to join our tables and can also create some temporary tables.
- We use the WHERE clause after the tables are joined to set up the working dataset and filter the data according to the conditions given in the query.
- After the data is sorted, we group the sorted data using the GROUP BY clause to break up the data into distinct chunks where every chunk has one key and a list of rows that fit the key.
- We use the HAVING clause to sort the data by COUNT or sum that has a certain condition.
- We use the LIMIT clause to limit the rows in the data to our requirement.