A Guide to SQL Common Table Expressions

Common table expressions are a relatively new SQL feature that is sometimes overlooked by experienced practitioners working with relational databases. Don’t be one of them! Learn how to use SQL CTEs and join the many data analysts enjoying the benefits of this great tool.

Common table expressions (CTEs), also known as WITH clauses, are used to create named subqueries that can be referenced in the main query. CTEs are not saved for future use and can be referenced only within the query where they are defined. The basic syntax is:

WITH cte_name AS
(SELECT … cte body...)
SELECT … main query ... 

How does this syntax work?

• First, we define our common table expression using the WITH keyword.
• Then, we assign a meaningful name to the CTE.
• Next, we write our subquery in the CTE body.
• Finally, we use our CTE in the main query by referencing its assigned name. For example, we can use it in the FROM or JOIN clause, as it basically is a table resulting from the subquery.

You can have multiple CTEs in one query. Moreover, one CTE can reference another CTE, resulting in nested CTEs. A CTE can also reference itself, resulting in a recursive CTE. All these possibilities make common table expressions a valuable tool for any data analyst working with relational databases. With SQL CTEs, you can improve query readability, break computations into parts, and nest computations.

You can learn about CTEs’ other advantages in this detailed guide. Or you can start practicing SQL CTEs right away with our interactive Recursive Queries course.

In this article, I’ll explain simple CTEs by walking you through a couple of real-world examples. Let’s start!

CTE Syntax Explained

To learn the syntax of common table expressions, we are going to analyze and compare digital cameras from several famous brands. Here is our data:

In our first example, we want to compare the price of each camera with the average price for this brand. Specifically, we are going to add another column showing the average camera price for this brand.

There are several possible approaches to accomplishing this task, one of which is using a common table expression. Here’s the query and the result:

WITH avg_price_brand AS
    (SELECT brand, AVG(price) AS average_for_brand
     FROM cameras
     GROUP BY brand)
SELECT c.id, c.brand, c.model, c.price, avg.average_for_brand
FROM cameras c
JOIN avg_price_brand avg
ON c.brand = avg.brand;

Now, let’s review our query in more detail.

• We start by defining a CTE that outputs the list of brands and the corresponding average prices.
  • We use the WITH keyword.
  • Then, we assign a name to our CTE (i.e. avg_price_brand).
  • In the CTE body, we select brands and calculate the average price for each brand.
• Then, in the main query, we use our CTE in the JOIN clause to get the additional column with the average prices into the result set.

As you can see, it’s very convenient to first define this auxiliary table with the brands and corresponding average prices and then simply reference it in the main query. Common table expressions make queries easier to follow and understand. Now, let’s consolidate your knowledge of CTEs with a couple more examples.

More SQL CTE Examples

In our next example, we are going to compare camera resolution across different brands. In particular, we want to calculate the average, maximum, and minimum number of megapixels for each brand and then display this information next to the actual number of megapixels for each model of the corresponding brands.

This time, we’ll have three CTEs, calculating the (1) maximum, (2) average, and (3) minimum camera resolution for each brand. In the main query, we’ll join all these common table expressions with our cameras table to get the result we need:

WITH max_megapixels AS
    (SELECT brand, MAX(megapixels) AS max_mp_brand
     FROM cameras
     GROUP BY brand),
     avg_megapixels AS
    (SELECT brand, AVG(megapixels) AS avg_mp_brand
     FROM cameras
     GROUP BY brand),
    min_megapixels AS
    (SELECT brand, MIN(megapixels) AS min_mp_brand
     FROM cameras
     GROUP BY brand)
SELECT c.brand, c.model, c.megapixels, max.max_mp_brand, avg.avg_mp_brand, min.min_mp_brand
FROM cameras c
JOIN max_megapixels max
ON c.brand = max.brand
JOIN avg_megapixels avg
ON C.brand = avg.brand
JOIN min_megapixels min
ON c.brand = min.brand;

When you use multiple CTEs in one query, note these syntactical details:

• Use the WITH keyword only once, when defining the first CTE.
• Separate the CTEs with a comma.

In our final example, we’ll use common table expressions to calculate the number of expensive (>$300) and cheap (< $300) cameras for each brand.

First of all, we need two CTEs that will calculate the number of cameras priced higher than $300 (i.e. expensive) and lower than $300 (i.e. cheap). Since some brands may not offer both expensive and cheap cameras, it will be safer to have a third CTE (i.e. brands) that simply outputs the list of camera brands. In our main query, we join all these three CTEs:

WITH brands AS
   (SELECT brand
    FROM cameras
    GROUP BY brand),
  expensive AS
  (SELECT brand, COUNT(*) AS expensive_cameras
   FROM cameras
   WHERE price > 300
   GROUP BY brand),
  cheap AS
  (SELECT brand, COUNT(*) AS cheap_cameras
   FROM cameras
   WHERE price < 300
   GROUP BY brand)
SELECT b.brand, e.expensive_cameras, ch.cheap_cameras
FROM brands b
FULL JOIN expensive e
ON b.brand = e.brand
FULL JOIN cheap ch
ON b.brand = ch.brand;

Here is the result of the query. Note that in this case, the NULL value reflects that there are no Kodak cameras above $300 in our database:

See how helpful CTEs are at breaking complex computations into parts? Learn more about when to use common table expressions in this comprehensive guide.

Time to Practice Common Table Expressions!

Now that you know how common table expressions can improve the readability and structure of your SQL queries, it’s time to practice! Coding challenges with real-world examples will help you master this advanced SQL feature so that you can confidently use it in your work.

I highly recommend LearnSQL.com’s interactive course on Recursive Queries. It includes 114 coding challenges that cover all types of common table expressions, including simple CTEs, nested CTEs, and recursive CTEs. The WITH syntax is usually not taught in a typical SQL course, making this course quite unique. However, there are a few other courses that suggest exercises covering simple and recursive CTEs, but they are mostly less detailed.

Thanks for reading, and happy learning!