Table of contents
1.
Introduction 
2.
What is Order of Execution in SQL?
3.
SELECT Clause in MySQL
4.
FROM and JOINs in SQL
5.
WHERE Clause in SQL
6.
GROUP BY Clause in SQL
7.
HAVING Clause in SQL
8.
ORDER BY Clause in SQL
9.
LIMIT/OFFSET in SQL
10.
SQL Order of Execution Example
11.
Tips for Writing Efficient SQL Queries
12.
Frequently Asked Questions
12.1.
What is the order of execution of SQL query?
12.2.
What is the order of sequence in SQL?
12.3.
How to execute ORDER BY in SQL?
12.4.
What is the correct order of execution of SQL code Mcq?
13.
Conclusion
Last Updated: Apr 18, 2024
Medium

SQL Query Execution Order

Author yuvatimankar
0 upvote
Career growth poll
Do you think IIT Guwahati certified course can help you in your career?

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.

SQL Query execution order

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:

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 tableusing the JOIN clause joining the order table.

Code:

SELECT emp.employee_id, emp.first_name, orders.amount, orders.order_id
FROM employee emp
JOIN `order` orders ;

 

Output:

output

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:

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:

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:

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:

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:

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:

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

Also read, Natural Join in SQL

Tips for Writing Efficient SQL Queries

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:

 

Refer to our guided paths on Coding Ninjas Studio to upskill yourself in Data Structures and AlgorithmsCompetitive ProgrammingJavaScriptSystem Design, and many more! If you want to test your competency in coding, you may check out the mock test series and participate in the contests hosted on Coding Ninjas Studio! But suppose you have just started your learning process and are looking for questions asked by tech giants like Amazon, Microsoft, Uber, etc. In that case, you must have a look at the problemsinterview experiences, and interview bundles for placement preparations.

Nevertheless, consider our paid courses to give your career an edge over others!

Do upvote our blogs if you find them helpful and engaging!

Happy Learning!

Live masterclass