Order of Execution in SQL: Enhancing Query Performance
Basics of Python
5 Hrs. duration
9 Modules
1800+ Learners
Start Learning
Have you ever written an SQL query and wondered why it isn’t doing what you expected? Or perhaps it’s running slower than you expect?
That is because the order of execution in SQL matters more than you might think.
As we build SQL queries, we don’t care about the order in which it is being processed by the query; however, we do care about what it is we are trying to get. Well, here’s the deal: SQL doesn’t read your query from top to bottom like we do. It has its own internal sequence to make sure things are properly executed and properly returned.
It is especially important to understand this execution order not just to make your queries execute faster but also to ensure that you really are bringing back the right data every time. If the actual execution order is wrong, we might be bringing back bogus or slow results.
That’s why we’re going to walk through the actual steps SQL makes when it processes a query.
Let’s break it down in a way that’s easy to digest so we can get our queries running like a well-oiled machine.
Understanding the Specific Order of Execution in SQL
FROM Clause: Establishing the Data Foundation with Tables and Joins
The first thing SQL does is determine where your data originates from.
This is where the FROM clause comes in. Imagine it like the base of a building. Without it, not much is going on.
In this step, SQL identifies the tables you are querying and combines them, if necessary, using JOIN operations.
It’s at this stage that SQL sets up the data environment for the rest of the query.
Here’s a quick example:
SELECT Customers.customer_name, Orders.total_amount
FROM Customers
JOIN Orders ON Customers.customer_id = Orders.customer_id;
In this case, SQL first pulls data from both the Customers and Orders tables. It then matches rows from these tables based on the customer_id. This matching happens before any filtering or grouping takes place.
At this point, SQL is only concerned with getting all the relevant rows from the data source.
Let’s say our tables look like this:
Customers Table:
customer_id
customer_name
1
Arjun
2
Priya
3
Neha
4
Sanjay
5
Ritu
Orders Table:
order_id
customer_id
total_amount
101
1
500
102
2
1200
103
3
900
104
4
450
105
5
750
After the JOIN operation, SQL merges the rows like this:
customer_name
total_amount
Arjun
500
Priya
1200
Neha
900
Sanjay
450
Ritu
750
No conditions are applied at this stage. It’s just about getting the data ready for the next steps.
WHERE Clause: Filtering Data Early to Reduce Dataset Size
Once SQL knows where the data is coming from, it applies the WHERE clause. This is where we can filter out rows that don’t match our criteria.
For example, if we only want orders from customers who spent more than 700, the WHERE clause will help us do that:
SELECT Customers.customer_name, Orders.total_amount
FROM Customers
JOIN Orders ON Customers.customer_id = Orders.customer_id
WHERE Orders.total_amount > 700;
With this condition in place, SQL will only return customers who placed orders over 700. Here’s what the filtered result would look like:
customer_name
total_amount
Priya
1200
Neha
900
Ritu
750
The essence here is efficiency.
Filtering early diminishes the data size that SQL has to process later. The smaller a dataset is, the quicker the rest of your query will run.
GROUP BY Clause: Grouping Data for Aggregated Insights
After filtering the rows, we want to group them; this is where the GROUP BY clause comes in.
Apply the GROUP BY clause to group together or aggregate data so that we might be performing some form of SUM, COUNT, or AVG operation.
Suppose we want to know the total amount spent by each customer. We might just group the orders by customer and then aggregate the sum:
SELECT Customers.customer_name, SUM(Orders.total_amount) AS total_spent
FROM Customers
JOIN Orders ON Customers.customer_id = Orders.customer_id
GROUP BY Customers.customer_name;
After running this, SQL will group all orders by each customer and then calculate the total amount they spent.
Here’s what the result looks like:
customer_name
total_spent
Arjun
500
Priya
1200
Neha
900
Sanjay
450
Ritu
750
Grouping allows us to summarise our data effectively. Instead of seeing several individual transactions, we can now see the total amounts spent per customer.
HAVING Clause: Refining Groups After Aggregation
Now that we have the groups built, we may want to apply another filter, but this time based on the aggregated data.
Assume we are interested in displaying only customers who spent more than 1000 in total. Here is how we can use the HAVING clause:
SELECT Customers.customer_name, SUM(Orders.total_amount) AS total_spent
FROM Customers
JOIN Orders ON Customers.customer_id = Orders.customer_id
GROUP BY Customers.customer_name
HAVING SUM(Orders.total_amount) > 1000;
The result:
customer_name
total_spent
Priya
1200
The difference between WHERE and HAVING is subtile:
WHERE works on the individual rows before aggregation; HAVING filters after aggregation
SELECT Clause: Bringing Back Data After Filtering and Grouping
Once filtering and grouping are done, SQL moves on to the SELECT clause. This is the place where we specify what data we wish to have represented in the final result.
The SELECT clause does no processing. It simply selects the data we have already done work on.
For instance:
SELECT Customers.customer_name, SUM(Orders.total_amount) AS total_spent
FROM Customers
JOIN Orders ON Customers.customer_id = Orders.customer_id
GROUP BY Customers.customer_name;
Output:
customer_name
total_spent
Arjun
500
Priya
1200
Neha
900
Sanjay
450
Ritu
750
The SELECT statement is the reason we even run queries in the first place. It’s how we extract the results we need.
ORDER BY Clause: Sorting Results in a Defined Order
If we want to sort the results, the ORDER BY clause comes into play.
SELECT Customers.customer_name, SUM(Orders.total_amount) AS total_spent
FROM Customers
JOIN Orders ON Customers.customer_id = Orders.customer_id
GROUP BY Customers.customer_nam
ORDER BY total_spent DESC;
In this case, SQL orders the results by the total amount spent in descending order:
customer_name
total_spent
Priya
1200
Neha
900
Ritu
750
Arjun
500
Sanjay
450
LIMIT and OFFSET Clauses: Limiting and Skipping Rows in the Result Set
Once we have the data sorted, there’s often a need to limit the number of rows returned.
This is where the LIMIT and OFFSET clauses come into play.
LIMIT restricts how many rows are shown, while OFFSET skips a specified number of rows before displaying the rest. These clauses are especially useful when dealing with large datasets or when paginating results for display in a web app.
Let’s say we only want to see the top 3 customers who spent the most:
SELECT Customers.customer_name, SUM(Orders.total_amount) AS total_spent
FROM Customers
JOIN Orders ON Customers.customer_id = Orders.customer_id
GROUP BY Customers.customer_name
ORDER BY total_spent DESC
LIMIT 3;
This query will return:
customer_name
total_spent
Priya
1200
Neha
900
Ritu
750
If we want to skip the first two results and show the next two:
SELECT Customers.customer_name, SUM(Orders.total_amount) AS total_spent
FROM Customers
JOIN Orders ON Customers.customer_id = Orders.customer_id
GROUP BY Customers.customer_name
ORDER BY total_spent DESC
LIMIT 2 OFFSET 2;
The Role of Order of Execution in SQL in Query Optimisation
SQL follows a rule to process queries, but it’s also about performance. When SQL knows exactly what to do and when to do it, the query runs much faster.
Take an example when we filter data early in the WHERE clause. We reduce rows SQL has to process in later stages like GROUP BY or ORDER BY.
This leaves us with fewer rows to work with, less memory usage, and an earlier end.
Let’s make it simple: why does order matter?
Efficient Filtering: The WHERE clause filters rows that don’t have to be processed early in the life cycle of a query before “heavy” operations such as grouping or joining take place.
Smaller Data Sets: Because the filtering happens early, when GROUP BY or SELECT takes place, we will know that we are working with a smaller data set.
Less Processing: Sorting or grouping a small amount of rows would be several times faster than doing the same operation for a huge amount of rows.
Think about an example of searching for a specific book in a huge library
If we first filter out the books on the basis of genre, that is, like the WHERE clause, we dramatically reduce the number of books we have to go through, allowing us to get to the one we are looking for easily and quickly.
This is precisely what SQL does when it follows the proper order of operation.
Examples Illustrating Order of Execution in SQL with Realistic Scenarios
Let’s take a new example that deals with sales transactions.
We will demonstrate how SQL processes a query to find the total sales amount for each product sold in the first quarter of 2023, filtering for products that generated more than 5,000 in revenue.
We’ll be working with two tables:
Sales Table (contains information on individual sales):
sale_id
product_id
sale_date
sale_amount
1
101
2023-01-15
2000
2
102
2023-02-20
6000
3
101
2023-03-05
3000
4
103
2023-01-30
4000
5
102
2023-04-15
7000
Products Table (contains product details):
product_id
product_name
101
Laptop
102
Smartphone
103
Headphones
SQL Query:
We want to retrieve the total sales for each product in the first quarter of 2023 (January to March). Additionally, we want to see only products whose total sales exceeded 5,000.
Here’s the SQL query to achieve this:
SELECT Products.product_name, SUM(Sales.sale_amount) AS total_sales
FROM Sales
JOIN Products ON Sales.product_id = Products.product_id
WHERE Sales.sale_date BETWEEN '2023-01-01' AND '2023-03-31'
GROUP BY Products.product_name
HAVING SUM(Sales.sale_amount) > 5000
ORDER BY total_sales DESC;
Step-by-Step Breakdown of Order of Execution in SQL:
FROM Clause: SQL starts by determining the data source. In this case, it pulls all the rows from both the Sales and Products tables.
JOIN Clause: The next step is joining these two tables using the product_id. After the JOIN, SQL combines matching rows from both tables.
The intermediate result looks like this:
sale_id
product_id
sale_date
sale_amount
product_name
1
101
2023-01-15
2000
Laptop
2
102
2023-02-20
6000
Smartphone
3
101
2023-03-05
3000
Laptop
4
103
2023-01-30
4000
Headphones
5
102
2023-04-15
7000
Smartphone
WHERE Clause: After the JOIN, the WHERE clause filters the rows based on the date range. We want sales between January 1, 2023, and March 31, 2023, so SQL removes the sales made outside this period.
The filtered result set now looks like this:
sale_id
product_id
sale_date
sale_amount
product_name
1
101
2023-01-15
2000
Laptop
2
102
2023-02-20
6000
Smartphone
3
101
2023-03-05
3000
Laptop
4
103
2023-01-30
4000
Headphones
GROUP BY Clause: Now, SQL groups the data by product_name. This allows us to calculate the total sales for each product.
The result after grouping looks like this:
product_name
total_sales
Laptop
5000
Smartphone
6000
Headphones
4000
HAVING Clause: After grouping, SQL filters the groups based on the total sales. We want to display only the products whose total sales are greater than 5,000.
After applying the HAVING clause, the result is:
product_name
total_sales
Smartphone
6000
SELECT Clause: Now, the SELECT clause retrieves the columns that we want to display in the final output: product_name and total_sales.
ORDER BY Clause: Lastly, SQL orders the results in descending order of total_sales. Since there’s only one result in this case, no further ordering is needed.
Final Output:
product_name
total_sales
Smartphone
6000
The Common SQL Query Writing Mistakes and How to Avoid Them
Even if we remember the right execution order, it is very easy to mess up when writing SQL queries. Here are common mistakes and how to avoid them:
To use SELECT
Selecting all columns can be too tempting, but it causes queries to run slowly and returns more data than needed. Always specify columns.
Forgetting Indexing:
If your queries run slowly, check if the appropriate columns are indexed, especially those referenced in WHERE and JOIN conditions.
Overuse of Subqueries:
Subqueries can be slow. Join data using JOIN statements instead of nested queries.
Incorrect Filtering:
Make sure to use WHERE for individual row filtering and HAVING for filtering grouped results.
Good Practices to Improve SQL Query Performance: Write Efficient Queries
Index the correct columns: In general, index columns used in WHERE and JOIN clauses
Reduce data retrieval: AVOID SELECT *. Only fetch columns as needed.
Leverage query execution plans: The database tools will give you execution plans, which indicate how SQL will process the queries. Use this to identify bottlenecks and optimise your queries.
Limit your result set: Use LIMIT to control how many rows are returned, especially with large datasets.
Conclusion
The order of execution in SQL directly affects performance and accuracy. Understanding how SQL executes FROM, WHERE, GROUP BY, and ORDER BY will make your writing of queries much more efficient.
Correct indexing, early filtering, and avoiding such unnecessary operations as * in SELECT or complex subqueries significantly increase speed. Steps as small as limiting the rows returned in a query or switching from subqueries to JOINs make a huge difference in large datasets.
Optimising SQL queries ensures not only faster results but also reduces the strain on database resources, thereby making queries scalable and more manageable as data grows.
FAQs
What is the most important step in optimising SQL queries?
The most important step is indexing the right columns.
Indexes are like a map for the database, helping it to search for data much faster. If you are filtering, joining, or ordering on some columns, it is very true that these columns should be indexed.
Why shouldn’t I use SELECT * in my SQL queries?
The use of SELECT brings all columns of the table, even if you don't need them. This brings unnecessary data, which delays the query, especially with big tables.
What is the difference between WHERE and HAVING in SQL?
The WHERE clause filters rows before aggregation occurs while HAVING filters the result after aggregation.
Use WHERE if you want to filter your individual rows before grouping. Use HAVING if you want to filter your group data.
How do I determine if my query is running efficiently?
To determine how efficient your query is, make use of EXPLAIN or EXPLAIN ANALYZE to see how SQL is processing your query.
These tools show the execution plan and highlight slow operations. That's a great way to identify bottlenecks in your query.
Should I always use LIMIT in my queries?
You are not compelled, but it is indeed useful if you only care about a small fraction of the returned rows.
With large datasets, LIMIT also prevents returning so much data for SQL to process and return, which makes your query faster.
Hero Vired is a leading LearnTech company dedicated to offering cutting-edge programs in collaboration with top-tier global institutions. As part of the esteemed Hero Group, we are committed to revolutionizing the skill development landscape in India. Our programs, delivered by industry experts, are designed to empower professionals and students with the skills they need to thrive in today’s competitive job market.