Order of Execution in SQL

In SQL queries, it is often necessary to perform a lot of complex instructions to select data and manipulate it. SQL queries usually follow a simple structure, but a key principle in the background decides how your operations are handled during the order of execution in SQL. In order to write better SQL queries that are efficient and accurate, it is crucial to know this specific order of execution. In this blog, you will understand the order of execution in SQL, along with the steps and examples in detail.

Table of Contents:

• What is the Order of Execution in SQL?
• Importance of Order of Execution in SQL
• Stages of SQL Query Execution
  1. FROM and JOINs in SQL
  2. WHERE Clause in SQL
  3. GROUP BY Clause in SQL
  4. HAVING Clause in SQL
  5. SELECT Clause in SQL
  6. DISTINCT Clause in SQL
  7. ORDER BY Clause in SQL
  8. LIMIT / OFFSET in SQL
• Order of Execution for Subqueries in SQL
• Order of Execution for Window Functions in SQL
• Common Mistakes in Understanding the SQL Order of Execution
• Best Practices for Order of Execution in SQL Queries
• Conclusion

What is the Order of Execution in SQL?

The order of execution in SQL defines the order of processing of each clause used in a query by a Database Management System (DBMS). The processing order is different from the order in which you write the clauses in your query. For example, if you gave someone a list of instructions, they are to be completed in a particular order to get the desired result. Similarly, the SQL engine follows a specific way of handling your query by gathering the required information, and this is known as the order of execution in SQL.

Level Up Your Tech Skills – Take Our SQL Pro Course Now

Learn from basics to advanced queries with hands-on lessons, live Q&A, and earn a recognized certificate.

Explore Program

Importance of Order of Execution in SQL

1. Provides the opportunity to improve performance: If you know the order of execution in SQL, you can form the query more wisely so that less data is processed at each stage, resulting in faster execution time.
2. Provides the opportunity for error tracing: If your query does not give the results you expect, it is helpful to know the order of execution in SQL so you can follow the process and isolate the point of runtime logic error that occurred.
3. Provides the opportunity to prevent unintended or misleading results: Not knowing the order of execution in SQL could lead you to apply features at the wrong stage or position in the query, resulting in potentially misleading or incorrect data.
4. Provides better predictability of results: Knowing the order of execution in SQL enables a predictable way to understand how your queries will be formed in the processing sequence and expectations of the results.

Stages of SQL Query Execution

Let’s create a table to understand the order of execution in SQL. Let’s say we have a company named “Priya’s Pottery” that sells handmade ceramic items. We are going to analyse their sales data.

Example:

Customers Table:

-- Create Customers Table  
CREATE TABLE Customers (  
    CustomerID INT PRIMARY KEY,  
    CustomerName VARCHAR(255),  
    City VARCHAR(255) );  

-- Insert data into Customers Table  
INSERT INTO Customers (CustomerID, CustomerName, City) VALUES (101, 'Anjali Sharma', 'Delhi'),  
(102, 'Rahul Verma', 'Mumbai'),  
(103, 'Pooja Singh', 'Bangalore'),  
(104, 'Sameer Khan', 'Delhi'),  
(105, 'Deepika Reddy', 'Hyderabad');

Products Table:

-- Create Products Table  
CREATE TABLE Products (  
    ProductID INT PRIMARY KEY,  
    ProductName VARCHAR(255),
    Category VARCHAR(255),
    Price DECIMAL(10, 2)
);

-- Insert data into Products Table
INSERT INTO Products (ProductID, ProductName, Category, Price) VALUES
(1, 'Ceramic Mug', 'Kitchenware', 350.00),
(2, 'Decorative Plate', 'Home Decor', 800.00),
(3, 'Flower Vase', 'Home Decor', 1200.00),
(4, 'Dinner Set', 'Kitchenware', 2500.00),
(5, 'Tea Set', 'Kitchenware', 1800.00);

Orders Table:

-- Create Orders Table
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    ProductID INT,
    Quantity INT,
    OrderDate DATE,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID),
    FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);

-- Insert data into Orders Table
INSERT INTO Orders (OrderID, CustomerID, ProductID, Quantity, OrderDate) VALUES
(1001, 101, 1, 2, '2024-01-15'),
(1002, 102, 3, 1, '2024-02-01'),
(1003, 103, 1, 3, '2024-02-10'),
(1004, 101, 2, 1, '2024-03-05'),
(1005, 104, 5, 1, '2024-03-12'),
(1006, 102, 4, 1, '2024-04-01'),
(1007, 105, 1, 2, '2024-04-18'),
(1008, 103, 3, 1, '2024-05-01');

1. FROM and JOINS in SQL

The first part of the SQL order of execution is the FROM clause, where the source table(s) for the data are determined. If there are multiple tables, it will be the JOIN clauses that ultimately determine how the tables will join together based on their matching columns or join conditions. Only then will the database engine know how to load the correct data from all the tables requested.

Example:

Let’s take an example of queries that display orders along with the customer names and product details.

SELECT
O.OrderID,
C.CustomerName,
P.ProductName,
O.Quantity
FROM
Orders AS O
JOIN
Customers AS C ON O.CustomerID = C.CustomerID
JOIN
Products AS P ON O.ProductID = P.ProductID;

Output:

Explanation: Here, in this example, the Orders, Customers, and Products tables are linked together. A virtual table is formed by combining the data from all three tables connected by the specified CustomerID and ProductID relationships.

2. WHERE Clause in SQL

After the data is created with the FROM and JOIN operations, the next processing operation is the WHERE clause. The WHERE clause acts to filter the set of rows returned based on the conditions defined. Any rows that meet the conditions in the WHERE clause will then continue to the next step in the order of execution in SQL. Designed to eliminate rows that are not needed as early as possible, the WHERE clause is valuable for reducing rows of data in future execution steps.

Example:

If you want to see only the orders that were ordered by customers from Delhi.

SELECT
O.OrderID,
C.CustomerName,
P.ProductName,
O.Quantity
FROM
 Orders AS O
JOIN
 Customers AS C ON O.CustomerID = C.CustomerID
JOIN
 Products AS P ON O.ProductID = P.ProductID
WHERE
 C.City = 'Delhi';

Output:

Explanation: Here, in this example, the joined result from the Orders, Customers, and Products tables contains only the rows where City is equal to ‘Delhi.’ This means that orders from customers in different cities are filtered out.

3. GROUP BY Clause in SQL

The next step after the WHERE Clause is the GROUP BY Clause. The GROUP BY clause is used to group together rows with the same value in one or more columns in the data to create a summary row in the data. The GROUP BY clause is usually accompanied by an aggregate function (SUM, COUNT, AVG, MAX, MIN, etc), which allows an operation to be performed on each group.

Example:

Let’s find the total quantity of products that were ordered by each customer.

SELECT
 C.CustomerName,
 SUM(O.Quantity) AS TotalQuantityOrdered
FROM
 Orders AS O
JOIN
 Customers AS C ON O.CustomerID = C.CustomerID
GROUP BY
 C.CustomerName;

Output:

Explanation: Here, in this example, after the joins and if there was a WHERE clause, the data has been grouped by CustomerName. This means that all the rows associated with the same CustomerName are grouped together, and the SUM(O.Quantity) is determined for all of the CustomerName grouped rows.

4. HAVING Clause in SQL

The HAVING clause filters based on the results returned by a SQL GROUP BY statement. While a WHERE clause filters individual rows before the GROUP BY is executed, the HAVING clause filters each group after the group(s) have been created and the aggregation has been performed. This is another important consideration in the order of execution in SQL.

Example:

Let’s find customers who have ordered products that have a quantity greater than 2.

SELECT
   C.CustomerName,
   SUM(O.Quantity) AS TotalQuantityOrdered
FROM
   Orders AS O
JOIN
   Customers AS C ON O.CustomerID = C.CustomerID
GROUP BY
   C.CustomerName
HAVING
   SUM(O.Quantity) > 2;

Output:

Explanation: Here, in this example, with the data grouped by CustomerName and the Total Quantity Ordered calculated for each group, the HAVING clause will now check if TotalQuantityOrdered is greater than 2. If that condition is met, only those customers who satisfy the condition will display in the final output.

5. SELECT Clause in SQL

The SELECT clause in SQL executes much later in the order of execution. Once the filtering and the grouping of data have been determined, the SELECT clause simply reports what columns to show in the final output. This is where any expression, or any aggregate (where GROUP BY was created the aggregate), will be finalized for the report.

Example:

Using the SELECT Clause, we can select or fetch the customer name and total quantity that has been ordered from a filtered group.

SELECT
    C.CustomerName,
    COUNT(O.OrderID) AS NumberOfOrders 
FROM
    Orders AS O
JOIN
    Customers AS C ON O.CustomerID = C.CustomerID
GROUP BY
    C.CustomerName
ORDER BY
    C.CustomerName;

Output:

Explanation: Here, in this example, this query calculates the total number of orders for all the customers. The SELECT clause displays all the data from the table.

6. DISTINCT Clause in SQL

When you use the DISTINCT keyword in your SELECT clause, it is applied after the SELECT clause has determined the final set of columns. DISTINCT is used to eliminate duplicate rows from the returned results.

Example:

Let’s find all the unique product categories that are ordered by the customers.

SELECT DISTINCT
    P.Category
FROM
    Orders AS O
JOIN
    Products AS P ON O.ProductID = P.ProductID;

Output:

Explanation: Here, in this example, after the JOIN operation, the SELECT clause could show all categories with duplicates (there could be several ‘Kitchenware’ types, etc.). In the end, the DISTINCT keyword will make sure that only one occurrence of each unique category (i.e., ‘Kitchenware’, ‘Home Decor’, etc.) are displayed in the output.

7. ORDER BY Clause in SQL

The ORDER BY clause sorts the final result set. This is one of the last operations to take place in the order of execution in SQL. You can sort in either ascending (ASC) or descending (DESC) order by one or more columns.

Example:

Let’s sort the customer’s orders in descending order based on the total quantity they ordered.

SELECT
    C.CustomerName,
    SUM(O.Quantity) AS TotalQuantityOrdered
FROM
    Orders AS O
JOIN
    Customers AS C ON O.CustomerID = C.CustomerID
GROUP BY
    C.CustomerName
HAVING
    SUM(O.Quantity) > 2
ORDER BY
    TotalQuantityOrdered DESC;

Output:

Explanation: Here, in this example, the ORDER BY clause sorts the result set based on the condition given, which is sorting them in descending order based on the highest order first.

8. LIMIT / OFFSET in SQL

LIMIT/OFFSET is the last step in the order of execution in SQL. It limits the number of rows returned by the query. LIMIT controls the total number of rows to return, and OFFSET controls how many rows to skip from the start of the output (before the rows start to be returned). The LIMIT clause is commonly used in MySQL.

Example:
Let’s fetch the top 2 customers based on the total number of orders they have placed.

SELECT
   C.CustomerName,
   SUM(O.Quantity) AS TotalQuantityOrdered
FROM
   Orders AS O
JOIN
   Customers AS C ON O.CustomerID = C.CustomerID
GROUP BY
   C.CustomerName
HAVING
   SUM(O.Quantity) > 2
ORDER BY
   TotalQuantityOrdered DESC
LIMIT 2;

Output:

Explanation: Here, in this example, the LIMIT set will be applied to the final result table, which limits the value to 2. That means it shows the result of the top 2 values in the table.

Order of Execution for Subqueries in SQL

The Order of execution mostly depends on the type of subquery that is used in the table.

Order of Execution for Window Functions in SQL

Window functions, such as RROW_NUMBER(), RANK(), LAG(), LEAD(), and aggregate functions used with OVER()., are executed after the WHERE, GROUP BY, and HAVING clauses in the SQL query processing, and before DISTINCT, ORDER BY, and LIMIT/OFFSET. They are able to operate on a “set” of rows (which represents a “window of rows”), which are associated with the current row processed when the window function executes. The results of the window functions are not summary results. Therefore, they do not collapse rows into a summary row like GROUP BY would do.

Example:
To find the order ID of each customer who purchased.

SELECT
  O.OrderID,
  C.CustomerName,
  P.ProductName,
  O.Quantity,
  ROW_NUMBER() OVER (PARTITION BY C.CustomerID ORDER BY O.OrderDate) AS CustomerProductOrderRank
FROM
Orders AS O
JOIN
Customers AS C ON O.CustomerID = C.CustomerID
JOIN
Products AS P ON O.ProductID = P.ProductID;

Output:

Explanation: Here, in this example, the ROW_NUMBER() window definitions are defined. For each customer (PARTITION BY C.CustomerID), the orders are ordered by OrderDate and ranked accordingly.
The calculations for the window function and any aggregations are determined before processing the final SELECT list or applying the overall ORDER BY clause to the results.

Common Mistakes in Understanding the SQL Order of Execution

1. Assuming Written Order is Execution Order: The most common error is assuming that SQL clauses execute in the same order they are written in the query text. The “order of execution” in SQL follows an internal logic that must be understood, and not the order of the written syntax.
2. Using SELECT Aliases in WHERE Clause: You will get an error if you try to filter data by using an alias in the WHERE clause because the WHERE clause is executed before the SELECT clause in SQL execution order. This means that the alias defined in the SELECT clause has not yet been defined.
3. Confusing WHERE and HAVING: A common trap is not understanding the differences in using WHERE or HAVING. WHERE filters rows individually from all the rows returned with the query, and does this before anything is grouped, while HAVING filters rows by group and after aggregating the selected rows returned.
4. Filtering Data in the Wrong Stage: Filtering data in the wrong execution stage can result in wrong result sets or processing too many rows that don’t need processing, a practice that affects both accuracy and performance.
5. Resulting in Inefficient Queries: Misunderstanding the order of execution in SQL may result in inefficient query processing and even some poorly optimized queries, which also process many more rows than necessary, resulting in slower queries and the use of more resources.

Best Practices for Order of Execution in SQL Queries

1. Filter Early: Always filter as much data as possible using the WHERE clause as early as you can. Filtering early allows you to reduce the volume of data passed on to the next steps and, therefore, potentially improve your performance considerably.
2. Understand When Aggregation Takes Place: Understand that, for example, WHERE clauses must refer to filter conditions for each individual row, while HAVING clauses will refer to aggregated group conditions.
3. Use aliases where they are allowed: For example, aliases created in the SELECT clause cannot be used in WHERE or GROUP BY conditions due to the order of execution. However, they can be used in ORDER BY and HAVING clauses, depending on the database system you are using.
4. Test and analyze: You should always test your queries, and you should always analyze execution plans to understand the order of execution in SQL and how your database system is interpreting them. This can help you determine what is causing your queries to run slowly and fix it.

Learn SQL for Free – Start Today!

Begin your data journey with beginner-friendly lessons and real practice.

Explore Program

Conclusion

The order of execution in SQL is a fundamental concept that anyone working with databases should understand. Execution order defines the exact order of processing a SQL query, starting from joining tables to formatting the final results. By understanding that the logical order of execution is different from how linear SQL statements are written, you can write more accurate, efficient, and debuggable queries in SQL. Having the knowledge about the order of execution in SQL will allow you to perform highly efficient and precise operations for retrieving data and improve your knowledge about the database.

To learn more about SQL functions, check out this SQL course and also explore SQL Interview Questions prepared by industry experts.

Order of Execution in SQL – FAQs