SQL CTEs Explained with Examples

Learn how you can leverage the power of Common Table Expressions (CTEs) to improve the organization and readability of your SQL queries.

The commonly used abbreviation CTE stands for Common Table Expression.

To learn about SQL Common Table Expressions through practice, I recommend the interactive Recursive Queries course at LearnSQL.com. It contains over 100 hands-on exercises on simple and complex recursive CTEs.

What does a CTE do? Why might you want to use one in your SQL code? Let’s answer those questions.

What Is a CTE?

A Common Table Expression is a named temporary result set. You create a CTE using a WITH query, then reference it within a SELECT, INSERT, UPDATE, or DELETE statement.

Let’s say you have a table called schools with the columns school_id, school_name, district_id, and the number of students. You need to write a query to display a list of schools alongside their district ID and the average number of students per school in that district.

Your logic might be as follows:

1. Create a table with the list of districts and the corresponding average number of students per school.
2. Join this table with the list of schools and display the required information.
3. Drop the table with the average number of students per school for each district.

If you use a CTE, you don’t need to create and drop a table. You can simply reference the temporary result set created by the WITH query, like you see below:

WITH avg_students AS (
SELECT district_id, AVG(students) as average_students
FROM schools
GROUP BY district_id)
SELECT s.school_name, s.district_id, avg.average_students
FROM schools s
JOIN avg_students avg
ON s.district_id = avg.district_id;

So, here you start by defining the temporary result set avg_students in the WITH query. In the brackets, there is a SELECT statement that defines this result set; it contains a list of districts and the corresponding average number of students per school. After the closing parenthesis, you start the main SELECT statement. Notice that you reference the temporary result set just like an ordinary table, using its assigned name (avg_students). The output will include the school name, the ID of its district, and the average number of students in this district.

In the sense that it can’t be used in any other SQL queries, the temporary result set is “dropped”. Of course, you can define it again if you need to.

Now that you have a basic understanding of Common Table Expressions and their syntax, it’s time to see how to use CTEs in real-world business cases.

CTEs in Action

Let’s start by exploring the data. Suppose you are a data analyst for a retail bank and you want to analyze the bonuses given to employees last month. The following table is the starting point:

Bonus_jan

Now let’s say you want to see the bonus paid to each employee along with the average bonus for their position. To this end, you first need to calculate the average bonus for every position. This can be done in a temporary result set (a CTE). The whole query will look like this:

WITH avg_position AS (
    SELECT position, AVG(bonus) AS average_bonus_for_position
    FROM bonus_jan
    GROUP BY position)
SELECT b.employee_id, b.first_name, b.last_name, b.position, b.bonus, ap.average_bonus_for_position
FROM bonus_jan b
JOIN avg_position ap
ON b.position = ap.position;

As you can see, we start by defining the temporary result set avg_position. Then there’s the main SELECT statement, where you join the bonus_jan table with the temporary result set avg_position to display information about each employee, their bonus, and the average bonus for that position:

Multiple CTEs in One Query

You can have multiple Common Table Expressions in one query – simply use one WITH keyword and separate the CTEs with commas.

Let’s say you want to compare each employee’s bonus to the average bonus for that position and the average bonus for that region. To do this, create two temporary result sets: one with the average bonus for each position and another one with the average bonus for each region. Here’s the entire query:

WITH avg_position AS (
    SELECT position, AVG(bonus) AS average_bonus_for_position
    FROM bonus_jan
    GROUP BY position),
    avg_region AS (
    SELECT region, AVG (bonus) AS average_bonus_for_region
    FROM bonus_jan
    GROUP BY region)    
SELECT b.employee_id, b.first_name, b.last_name, b.position, b.region, b.bonus, ap.average_bonus_for_position, ar.average_bonus_for_region
FROM bonus_jan b
JOIN avg_position ap
ON b.position = ap.position
JOIN avg_region ar
ON b.region = ar.region;

After you’ve defined the temporary results sets avg_position and avg_region, you write the main SELECT statement to display the average position and region bonuses along with each employee’s information:

Nested CTEs

Common Table Expressions can be also nested. This means having multiple CTEs in the same query where at least one CTE refers to another CTE. This will be clearer after we look at an example.

Let’s say you want to evaluate the performance of different outlets in the bank’s retail network. In particular, you’d like to compare the average bonus across employees for each outlet with the minimum and the maximum average bonus across outlets.

The logic might be as follows:

1. Calculate the average bonus across employees for each outlet (CTE: avg_per_outlet).
2. Find the minimum average bonus across outlets (CTE: min_bonus_outlet).
3. Find the maximum average bonus across outlets (CTE: max_bonus_outlet).
4. Output each outlet’s ID along with the average bonus for this outlet and the minimum and maximum average bonuses across outlets.

To create the CTEs min_bonus_outlet and max_bonus_outlet, you’ll need to reference the first CTE, avg_per_outlet. Here’s the entire query:

WITH avg_per_outlet AS (
    SELECT outlet, AVG(bonus) AS average_bonus_for_outlet
    FROM bonus_jan
    GROUP BY outlet),
    min_bonus_outlet AS (
    SELECT MIN (average_bonus_for_outlet) AS min_avg_bonus_for_outlet
    FROM avg_per_outlet),
    max_bonus_outlet AS (
    SELECT MAX (average_bonus_for_outlet) AS max_avg_bonus_for_outlet
    FROM avg_per_outlet)    
SELECT ao.outlet, ao.average_bonus_for_outlet, min.min_avg_bonus_for_outlet,
max.max_avg_bonus_for_outlet
FROM avg_per_outlet ao
CROSS JOIN min_bonus_outlet min
CROSS JOIN max_bonus_outlet max;

Note that there are three different Common Table Expressions; two of them (min_bonus_outlet and max_bonus_outlet) refer to another CTE (avg_per_outlet). This makes them nested CTEs.

In the main SELECT statement, we display the outlet’s ID, the average bonus for all employees in this outlet, and the minimum and maximum average bonuses across outlets. To this end, we cross join all three of our temporary result sets. Here’s the output of this query:

For more CTES examples, check out LearnSQL.com’s introductory guides on what a CTE is and when you should use it.

Why Use a CTE?

Now, you may have noticed that in most cases, you could use one or more subqueries instead of CTEs. So why use a CTE?

• Common Table Expressions better organize long queries. Multiple subqueries often look messy.
• CTEs also make a query more readable, as you have a name for each of the Common Table Expressions used in a query.
• CTEs organize the query so that it better reflects human logic. With CTEs, you start by defining the temporary result set(s) and then refer to it/them in the main query. With subqueries, you start with the main query and then place the subqueries in the middle of the query.
• Finally, there is also a specific category of CTEs called recursive CTEs that are allowed to reference themselves. These CTEs can solve problems that cannot be addressed with other queries. Recursive queries are especially useful in working with hierarchical data.

Learn more about recursive CTEs in our in-depth guides on the capabilities of SQL recursive queries and hierarchical queries in PostgreSQL and Oracle.

Time to Practice Common Table Expressions!

Are you excited about leveraging the power of CTEs in your SQL queries? If you want to write CTEs like a pro, you need lots of practice.

LearnSQL.com offers a comprehensive course on Recursive Queries. It includes 114 interactive exercises covering simple CTEs, nested CTEs, and recursive CTEs. This course is a perfect opportunity to learn how to manage your SQL queries with Common Table Expressions, how and when to nest CTEs, and how to use recursive CTEs.

Would you like to learn more about SQL CTEs? Check out our beginner-friendly guides:

• What Is a CTE?
• When Should I Use a Common Table Expression (CTE)?
• How to Organize SQL Queries with CTEs

Happy learning!