Tired of redoing the same analysis every week?
Julius Notebooks is an AI-powered workspace that automates everything from cleaning data to creating charts. It works hard for you. Here’s why users love it:
AI-Powered Automation: Generate full analysis workflows with one prompt.
Reusable Workflows: Build once, reuse anytime for recurring reports. Time saver.
Seamless Visualization: Turn results into sleek charts automatically.
Real-Time Collaboration: Work together on the same notebook.
Massive Time Savings: Spend minutes on tasks that used to take hours.
Start automating your data analysis: Try Julius for free: Click here to try Julius.
Introduction
A good tool improves the way you work. A great tool improves the way you think. ~ Anonymous
The battle of tools in tech never really ends. The funny part? Even when you’re talking about a single tool like SQL, debates still break out over how to use it. You’ve probably written a perfectly working query, only to have someone say, “You should’ve used a CTE instead.”
Yes, the battle between Common Table Expressions (CTEs) and subqueries is an ongoing one. So how do you decide when to use a subquery and when to pull out the CTE hammer?
What exactly is a Common Table Expression? What even is a subquery? And what advantages does one have over the other? That’s exactly what we’re going to explore in this article.
What is a Subquery?
If you take a query and put it inside another query, then it becomes a subquery. A subquery is simply a query within another query. Think of it as a query that helps another query get the job done. Before you serve food on the table, the table must first be cleaned. Think of the subquery as the process that cleans the table (by fetching necessary data) to enable you to serve the food (the final result). Here is an example:
In this case, the inner query finds all the payment codes where is_automatic = 1. The outer query then uses those codes to fetch transactions from the premium_transactions table. It’s simple and straightforward. You just let the inner query do the filtering work (clean the table) and then use its result in the main query (serve the food). As you can see here, in most databases, subqueries do not necessarily need a name.
However, subqueries can sometimes become too hard to read, especially when they’re nested multiple levels deep. Here is an example:
This deeply nested subquery is calculating the average order total for each customer, but only for orders placed after January 1, 2023, and only including order items that cost more than the average price of electronics products. This is quite a complex piece of work
What is a Common Table Expression?
A CTE (Common Table Expression) is like a temporary view or table that you can reference multiple times within the same query. A CTE definition starts with the WITH keyword and has a name. Here is an example:
Here we have a query that is divided into two parts: the CTE and the main query. The CTE is called avg_salary. It calculates the average salary from the employees table. The main query uses the CTE to retrieve the employees whose salary is above average. The most interesting thing here is that this CTE can be used multiple times in the query if needed.
Here is another example of a multi-step CTE:
This query identifies high-value customers by first calculating each customer’s total spending in the sales_per_customer CTE, then filtering for those who spent over $5,000 in the top_customers CTE, and finally retrieving the complete customer details for these top spenders from the customers table. Essentially, it finds all customers whose total purchases exceed $5,000 and returns their full customer records. You can see how the logic of the query is broken down into easy-to-follow steps.
CTE vs. Subquery: When to Use Each
Now that you have an understanding of the difference between a subquery and a CTE, how do you decide which one to use?
Here is when you should use a CTE:
You want your SQL to be readable and organized: You write queries for yourself, your future self, and others, so the goal should be to make the query easy to understand and follow: Here is how the deeply nested query (covered under the subquery section) would look if we used CTE:
This is much easier to read and follow than a deeply nested CTE.
You’re building incremental logic (step-by-step transformations): If your query depends on incremental or layered logic, CTEs are superior to subqueries. With a subquery, incremental steps quickly become hard to read, debug, and maintain. The query we examined earlier serves as a great example:
Here, we have a query that is easy to follow:
Step 1: Calculate total spending per customer
Step 2: Filter to high-value customers
Step 3: Enrich with full customer details
Each CTE represents a clean, understandable transformation that feeds into the next, making the analytical thought process transparent and the query much more maintainable than a single complex query with nested subqueries.
You need to reuse a subquery multiple times in the same statement: If you find yourself trying to use a subquery multiple times, then switching to a CTE will be a great choice. Here is an example of a query that uses the same subquery multiple times
Instead of the query above, you can use a CTE. See below:
With a CTE, the min/max is calculated once and reused. This avoids recalculating the same complex subquery multiple times. This makes the query easy to maintain.
When to use a subquery?
If any of the cases described above are not of concern, then you can use a subquery. For example, if you are updating a table, then you can use a subquery:
This is clear and concise. Using a CTE here might not be necessary because the subquery is only used once and the logic is simple.
You can also use a subquery if you are checking for existence with the EXISTS operator. See below:
Here is a rule to follow:
Use CTEs for complex, multi-step transformations and when you need to reuse logic. Use subqueries for simple, single-purpose operations where the added clarity of CTEs isn’t necessary.
Wrap-up
Someone famously said, “A good tool improves the way you work. A great tool improves the way you think.” When you are writing your queries, stop and think about how the method you are using is impacting performance, readability, and maintenance. Apply these same principles when choosing between common table expressions and subqueries. Thanks for reading.