Do you think IIT Guwahati certified course can help you in your career?
No
Introduction
SQL queries are used to retrieve a set of records from our database table. In SQL queries, clauses are the building blocks. These clauses must be executed in a specific order to obtain the correct results. This order of execution is called SQL query execution Order.SQL Query execution order comes into the picture when we want to save time searching for certain entities with conditions. It helps us to get optimized results without wasting time.
So, in this article, we will cover the topic of SQL Query Execution Order. Let's get started.
What is Order of Execution in SQL?
Let's try to understand what SQL Query Execution Order is by an example. Suppose we want the names from a table whose age is over 27. So, in this case, the SQL query execution order retrieves data optimally and takes the minimum time possible.
So, SQL query execution order means optimizing search results from the database or where the query clauses are checked according to the need/requirement. Here we keep clauses in a particular order known as SQL query execution order. The table in which clauses are in SQL query execution order is as follows:
Order
Clause
Function
1
FROM
Tables are merged in order to get the base data.
2
WHERE
This clause filters the base data.
3
GROUP BY
This clause group the filtered base data.
4
HAVING
This clause filters the grouped base data.
5
SELECT
This clause returns the final data.
6
ORDER BY
This clause stored the final data.
7
LIMIT
The returned data is limited to row count.
SELECT Clause in MySQL
This clause is used to select the data from the database. For selecting complete data, we use the * operator. Let's suppose we only want data of first_name from the employee table; we will run the following command:
SELECT first_name FROM employee;
Output:
FROM and JOINs in SQL
The FROM clause specifies the table on which the query needs to do the operation. And JOIN merge two or more tables. Here we will get the employee_id and first_name columns from the employee table and the amount and order_id from the order table with the help of the SELECT clause from the employee table, using the JOIN clause joining the order table.
The output will be the cartesian product of both tables, as shown in the above screenshot.
Now, we will run the query where the ON clause is used to join all the columns after the FROM and JOIN clauses are executed. The ON clause is used to merge specific columns. It helps the WHERE clause in filtering the conditions.
Code:
SELECT emp.employee_id, emp.first_name, orders.amount, orders.order_id
FROM employee emp
JOIN `order` orders
ON emp.employee_id = orders.employee_id;
Output:
Here, we have used the ON clause to merge both tables.
WHERE Clause in SQL
The WHERE clause is used to filter certain records that satisfy the conditions mentioned in the query. It is checked second after the FROM clause. The operators which can be used along with the WHERE clause are as follows:
Operator
Description
>=
Greater than equal to
<=
Less than equal to
<>
Not equal
>
Greater than
<
Less than
BETWEEN
Between a range
Like
Search for a pattern
=
Equal to
Now we will run a query that will filter out the result by the order_id column where order_id is greater than equal to 3.
Code:
SELECT emp.employee_id, emp.first_name, orders.amount, orders.order_id
FROM employee emp
JOIN `order` orders
ON emp.employee_id = orders.employee_id
WHERE orders.order_id <=4 ;
Output:
The first line specifies the columns we want to retrieve from the "employee" and "order" tables
The second line specifies the "employee" table and assigns it the alias "emp" to simplify the query
The third line Joins the "order" table using the employee_id column as the join condition, connecting related records between the two tables
The fourth line adds a condition to the query to filter only those rows where the "order_id" from the "order" table is less than equal to 4
GROUP BY Clause in SQL
After filtering the data with the help of the WHERE Clause, using the GROUP BY Clause, we will group the sorted data. We will run the following query to find out the maximum number of orders the employees place.
Code:
SELECT MAX(orders.amount) AS max_amount_of_orders, emp.first_name
FROM employee emp
JOIN `order` orders
ON emp.employee_id = orders.employee_id
WHERE orders.order_id <= 4
GROUP BY emp.first_name;
Output:
As we can see from the output we have grouped the record by first_ name in which order_id is less than equal to 4.
HAVING Clause in SQL
After using GROUP BY clause, if we are not getting the desired result. This HAVING Clause is used when the WHERE clause cannot be used along with the aggregate functions. It takes a particular condition that is applied in the GROUP BY clause.
Syntax
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
Suppose we want to get the maximum amount that is less than or equal to a particular amount. We will ask the following query:
SELECT MAX(orders.amount) AS max_amount_of_orders, emp.first_name
FROM employee emp
JOIN `order` orders
ON emp.employee_id = orders.employee_id
WHERE orders.order_id <= 4
GROUP BY emp.first_name
HAVING MAX(orders.amount) <= 5000;
Output:
As we can see from the above output, the maximum amount that was less than 5000 was only Avisha's, i.e., 3000.
ORDER BY Clause in SQL
This clause is used to sort the data in ascending or descending order. Keyword ASC is used to sort data in ascending order, and the keyword DESC is used to sort the data in descending order.
Let's take an example; we want the order to be in descending order by order_id, so we will run the following query:
SELECT emp.employee_id, emp.first_name, orders.amount, orders.order_id
FROM employee emp
JOIN `order` orders ON emp.employee_id = orders.employee_id
WHERE orders.order_id <= 4
ORDER BY orders.order_id DESC;
Output:
LIMIT/OFFSET in SQL
After the result is sorted, it may be possible that we want a limited number of rows in the output because the data is large to analyze. In that case, we can use LIMIT and OFFSET clauses.
Syntax
LIMIT (OFFSET Number) (LIMIT number)
We will execute the command with the limit set to two. To do so, we will use the following query:
SELECT emp.employee_id, emp.first_name, orders.amount, orders.order_id
FROM employee emp
JOIN `order` orders
ON emp.employee_id = orders.employee_id
WHERE orders.order_id <= 4
ORDER BY orders.order_id DESC
LIMIT 2;
Output:
SQL Order of Execution Example
Let's suppose two tables: Employee and Orders. The table Employee has the data of the employee's name and IDs. The table Orders has the details of the employee's order; for ex: it stores the amount and the order id of the employee's id. We will be using two databases here.
Table1 - Employee
employee_id
first_name
1
Amit
2
Punit
3
Avisha
4
Amit
5
Roshan
Table2 - Orders
orders_id
amount
employee_id
1
2000
3
2
5000
10
3
3000
2
4
8000
1
5
1500
0
Questions: Suppose we want to get the employee's order details where the order id is greater than or equal to 3 with the limit set as 2. Also, the output should be in ascending order.
Query: Following is the query we will understand step by step.
SELECT employee.employee_id, employee.first_name, orders.amount, orders.order_id
FROM employee
JOIN orders
ON employee.employee_id = orders.employee_id
WHERE orders.order_id >=3
ORDER BY orders.order_id ASC
LIMIT 2
Use the following tips to write SQL queries that are effective:
When using columns often in WHERE clauses, make sure they are properly indexed.
Limit the number of times you use SELECT * to only get the columns you need.
WHERE clauses shouldn't use functions or calculations because they can make index usage more difficult.
Make use of the right JOIN types and optimize the JOIN conditions.
Use JOINs instead of subqueries whenever possible.
EXPLAIN or query profiling tools can be used to test query performance.
Frequently Asked Questions
What is the order of execution of SQL query?
An SQL query runs in the following order: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, and, if iteration is used, LIMIT/OFFSET. This order guarantees appropriate result filtering, grouping, selection, sorting, and limitation.
What is the order of sequence in SQL?
SQL queries are arranged as follows: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, and LIMIT/OFFSET. The stages for choosing, filtering, grouping, sorting, and limiting results in SQL queries are determined by this order.
How to execute ORDER BY in SQL?
Choose the column or columns by which you wish to sort the result set following the SELECT statement in order to use ORDER BY in SQL. Use DESC for descending order or ASC for ascending order (default).
What is the correct order of execution of SQL code Mcq?
SELECT selects columns, ORDER BY sorts results, FROM chooses tables, WHERE filters rows, GROUP BY groups rows, HAVING filters groups, and LIMIT/OFFSET handles iteration. This is the proper sequence in which SQL code should be executed.
Conclusion
In this article, we have discussed SQL Query Execution Order. We have covered the introduction, Syntax, and some examples of it. We hope this blog has helped you enhance your knowledge of SQL Query Execution Order. If you want to learn more, then check out our articles: