The Common Table Expression, commonly known as CTEs, refers to a temporary named result that we can reference within a SELECT, INSERT, UPDATE, or DELETE statement in SQL.
CTEs are incredibly useful as we can define them within the scope of a single statement which allows us to reference it multiple times within that statement.
This makes working with complex queries involving multiple tables and complex joins much easier and more intuitive. In addition, we can use multiple CTEs to break down the query into more readable and maintainable chunks.
This tutorial explores the fundamentals of working with multiple CTEs in SQL including the basic syntax, example usage, and various best practices.
Multiple CTEs in SQL
The following shows the syntax on how we can define multiple CTEs statements in generic SQL:
cte1 AS (SELECT ...),
cte2 AS (SELECT ...),
cte3 AS (SELECT ...)
SELECT ...
In this case, we use the WITH keyword to define the CTEs. Next, we define each CTE as an individual subquery within the parentheses. Finally, we also separate the multiple CTEs by a comma, and each CTE contains a unique name that is assigned to it. We can then use this name to reference the CTE in subsequent queries.
As mentioned, we can use the multiple CTEs in SQL to perform the complex queries involving multiple tables and joins.
An example syntax query is shown in the following:
cte1 AS (SELECT * FROM table1 WHERE condition1),
cte2 AS (SELECT * FROM table2 WHERE condition2),
cte3 AS (SELECT * FROM cte1 JOIN cte2 ON cte1.id = cte2.id)
SELECT * FROM cte3 WHERE condition3
In the previous example, we define three CTEs namely: cte1, cte2, and cte2. The first CTE selects the data from Table 1 based on the specified condition.
The second CTE selects the data from Table 2 based on a different condition. The third CTE then joins the results of the first two CTEs based on the common ID column. Finally, the SELECT statement at the end selects all the columns from the third CTE and applies the defined condition.
Example Usage
If you are still unsure on how we can use multiple CTEs, let us take a real-world database such as Sakila to demonstrate.
Problem:
In our case, we want to determine the top 10 most rented movies in May 2005, the rental count, and the total revenue.
As you can imagine, this is a complex query that involves multiple tables and multiple joins. Instead of writing each query and then figuring out a way to bring them together, we can use multiple CTEs to accomplish this.
For that, we can use the query as demonstrated in the following:
-- CTE 1: Fetch the rental IDs and movie IDs for rentals in May 2005
january_rentals AS (
SELECT rental_id, inventory_id
FROM rental
WHERE rental_date >= '2005-05-01:00:00:00' AND rental_date < '2005-06-01:23:59:59'
),
-- CTE 2: Fettch the movie titles and rental prices for all movies
movie_info AS (
SELECT title, rental_rate
FROM film
),
-- CTE 3: Join the rental and movie info to get rental counts and revenue
rental_info AS (
SELECT
may_rentals.inventory_id,
movie_info.title,
COUNT(*) AS rental_count,
SUM(movie_info.rental_rate) AS total_revenue
FROM may_rentals
JOIN inventory ON may_rentals.inventory_id = inventory.inventory_id
JOIN film ON inventory.film_id = film.film_id
JOIN movie_info ON movie_info.title = film.title
GROUP BY may_rentals.inventory_id, movie_info.title
)
-- Final query: Select the top 10 movies by rental count and revenue
SELECT
title,
rental_count,
total_revenue
FROM rental_info
ORDER BY rental_count DESC, total_revenue DESC
LIMIT 10;
Let us break down the previous query and explore it step by step:
- The first part is CTE 1 which is labeled as “may_rentals”. This CTE selects the rental_ID and the movie_ID for all the rentals in May 2005. We use this data to join the film and inventory tables which allow us to fetch the movie titles and rental rates.
- The second part is CTE 2 which is labeled as “movie_info”. This CTE allows us to select the movie titles and the rental rates for all the movies in the film table. We use this data to calculate the total revenue for each rented movie in that period.
- The third section is CTE 2 which is labeled as “rental_info”. In this CTE, we join the may_rentals CTE with the inventory and the film tables to get the movie title, rental_rates, and the inventory ID for each rental. We then join the result with the movie_info CTE to get the rental count and total revenue for each movie and then group the result based on the inventory ID and the movie title.
- The final part is the select statement that selects the top 10 movies by rental count and total revenue from the rental_info CTE.
We hope that the provided steps clarify how to define the multiple CTEs in SQL. For example, if we run the previous query, we should get the resulting table as shown in the following:
Conclusion
You now learned about the fundamentals of working with multiple CTEs in SQL. You also discovered how to answer complex questions and break down complex queries into smaller, more readable, and more manageable chunks using multiple CTEs as demonstrated using the Sakila database.
