CTE's : My secret weapon for readable and reusable SQL
Over the years, I’ve relied heavily on joins and subqueries to pull data and generate reports from multiple tables. Often, the queries would span dozens of lines, especially in complex scenarios where the same combination of joins and subqueries had to be repeated multiple times across the codebase.
I never really questioned it… until one day, a seemingly simple problem led to a long, messy, and difficult-to-read query. It wasn’t efficient, and definitely not something I could reuse or easily explain.
That’s when I discovered Common Table Expressions (CTEs) and it genuinely felt like a game changer.
CTEs brought clarity, reusability, and in many cases, even performance improvements. They helped me break down complex logic into readable blocks and eliminated the need to repeat subqueries.
In this post, I’ll walk you through a simple real-world example: we’ll look at how to solve the same problem first with traditional subqueries and then with a cleaner, more maintainable CTE-based approach.
I'm currently experimenting with the DVD Rental database—a sample dataset that's perfect for practicing SQL queries.
Here’s a simple use case I wanted to solve:
Find the movie(s) that was rented the most.
Like any structured problem solver, I prefer to break the problem into smaller steps before jumping into the query. For this one, the process looked like:
1.Find the highest rented movie count
2.Compare each movie’s rent count with that highest count
Using Joins and Subqueries:
To solve this, I initially used joins and a nested subquery. Here's what that looked like:
SELECT FILM.TITLE ,COUNT(RENTAL.RENTAL_ID) AS RENT_COUNT
FROM FILM
JOIN INVENTORY
ON FILM.FILM_ID=INVENTORY.FILM_ID
JOIN RENTAL
ON INVENTORY.INVENTORY_ID=RENTAL.INVENTORY_ID
GROUP BY FILM.TITLE
HAVING COUNT(RENTAL.RENTAL_ID)=(SELECT MAX(FILM_RENTAL_COUNT) FROM
(SELECT INVENTORY.FILM_ID,COUNT(RENTAL.RENTAL_ID) AS FILM_RENTAL_COUNT
FROM RENTAL
JOIN INVENTORY
ON RENTAL.INVENTORY_ID=INVENTORY.INVENTORY_ID
GROUP BY INVENTORY.FILM_ID
)
)
Lets walk through the above query with joins and nested queries:
The innermost query gets the rental count for each movie (by film_id).
The middle query finds the maximum rental count from that list.
The outer query then filters to only return movies with a rental count equal to that maximum.
While the subquery version gets the job done, it comes with a few drawbacks:
Redundant Joins: The same RENTAL and INVENTORY join is written multiple times, which adds unnecessary repetition.
Nested Complexity: The logic is tucked away inside layers of subqueries, making it harder to read, understand, or debug at a glance.
Using Common Table Expressions(CTE’s):
Now, let’s look at how we can write a better, more maintainable SQL query using a Common Table Expression (CTE).
CTEs allow us to encapsulate complex or repetitive logic into a single, named block. Think of it as a temporary result set—similar to a virtual table—that exists only during the execution of your query. This makes your code easier to manage, read, and debug.
Here’s how we can use a CTE to achieve the same result effectively:
WITH RENTED_MOVIES AS
(SELECT FILM.TITLE ,COUNT(RENTAL.RENTAL_ID) AS RENT_COUNT
FROM RENTAL
JOIN INVENTORY
ON RENTAL.INVENTORY_ID=INVENTORY.INVENTORY_ID
JOIN FILM
ON INVENTORY.FILM_ID=FILM.FILM_ID
GROUP BY FILM.TITLE)
SELECT * FROM RENTED_MOVIES WHERE RENT_COUNT=(SELECT MAX(RENT_COUNT) FROM RENTED_MOVIES)
Let’s understand what we have done here:
We start by defining a CTE called RENTED_MOVIES which is a temporary table accessible only within the query. Inside it, we performed the necessary joins and aggregations to get the rental count for each movie.
Once the CTE is defined, we can reference it just like a table. In this case, we simply filter the results to find the movie(s) that have the maximum rental count.
Advantages of CTE’s over sub/nested queries:
No Repetition: The joins and aggregations are written once and reused.
Better Readability: The query is logically separated into parts that are easier to follow.
Improved Maintainability: If we need to tweak the logic later, we only have to do it in one place.
Performance Matters: Comparing Query Costs and Execution times
While improving code readability and maintainability is important, performance is often the real deal-breaker—especially in large datasets or production environments.
To test the performance of both versions, I ran each query using the EXPLAIN ANALYZE feature in my PostgreSQL environment.
Here’s what I observed:
Query version | Estimated costs | Execution time |
Sub query approach | 1296..1308 | 35.029ms |
CTE approach | 746..768 | 12.147ms |
This tells us:
CTE is faster in this case: About 65% quicker execution time than the subquery approach.
Estimated costs are also lower, about 40% reduction in cost by simply refactoring the query logic using a CTE.
Let’s break down why this happens:
Repeated Joins in Subquery: The subquery version repeats the same join logic inside both the main query and the nested subquery. This means the database has to reprocess the same joins twice.
CTE as a Reusable Block: In the CTE version, the rental counts are computed once and stored temporarily. This allows the database to optimize around a single result set instead of recalculating things.
Conclusion: Readable, Reusable—and Faster
What started as a simple question—“Which movie was rented the most?”—led us to compare two different SQL approaches: one using nested subqueries, and the other using a Common Table Expression (CTE).
While both queries produced the correct result, the CTE version clearly stood out in two major ways:
Cleaner and more maintainable code
Better performance
CTEs are a powerful tool to help us write more efficient and scalable SQL. If you’re still relying on nested subqueries, I encourage you to give CTEs a try — you might be surprised at how much cleaner and faster your queries become!
When Simple Problems Meet Complex Realities
In real-world scenarios, queries often become more complex, involving large tables, intricate relationships, and evolving business rules. Subqueries can become a bottleneck when they repeat costly joins or aggregations. This is where CTEs really shine — especially when you’re working with large datasets or need to refactor existing queries.
Always experiment with different query patterns (subqueries, CTEs, temp tables) and analyze them using tools like EXPLAIN PLAN. The goal isn’t just to make a query work, but to make it simple, maintainable, and efficient.
Happy learning!
