Back to articles list May 5, 2021 - 6 minutes read A Guide to SQL Common Table Expressions Kateryna Koidan Kateryna is a data science writer from Kyiv, Ukraine. She worked for BNP Paribas, the leading European banking group, as an internal auditor for more than 6 years. More recently, she decided to pursue only the favorite part of her job—data analysis. Now she is continuing her self-education with deep-learning courses, enjoys coding for data analysis and visualization projects, and writes on the topics of data science and artificial intelligence. Kateryna is also a proud mother of two lovely toddlers, who make her life full of fun. Tags: sql learn sql CTE 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: cameras idbrandmodelmegapixelsprice 11CanonEOS 6D Mark II26.21399.00 12CanonEOS Rebel T724.1449.00 13CanonPowerShot ELPH 320 HS16.1249.95 14CanonPowerShot ELPH 500 HS12.199.97 15KodakPIXPRO Astro Zoom AZ652-BK20254.90 16KodakPIXPRO Astro Zoom AZ252-RD16122.55 17KodakEasyShare Z95012199.00 18PanasonicLumix G716597.99 19PanasonicLumix DMC-ZR314.1279.95 20PanasonicLumix DMC-G112.1399.99 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; idbrandmodelpriceaverage_for_brand 11CanonEOS 6D Mark II1399.00549.50 12CanonEOS Rebel T7449.00549.50 13CanonPowerShot ELPH 320 HS249.95549.50 14CanonPowerShot ELPH 500 HS99.97549.50 15KodakPIXPRO Astro Zoom AZ652-BK254.90192.33 16KodakPIXPRO Astro Zoom AZ252-RD122.55192.33 17KodakEasyShare Z950199.00192.33 18PanasonicLumix G7597.99426.00 19PanasonicLumix DMC-ZR3279.95426.00 20PanasonicLumix DMC-G1399.99426.00 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; brandmodelmegapixelsmax_mp_brandavg_mp_brandmin_mp_brand CanonEOS 6D Mark II220.127.116.11512.1 CanonEOS Rebel T718.104.22.168512.1 CanonPowerShot ELPH 320 HS22.214.171.124512.1 CanonPowerShot ELPH 500 HS126.96.36.199512.1 KodakPIXPRO Astro Zoom AZ652-BK20201612 KodakPIXPRO Astro Zoom AZ252-RD16201612 KodakEasyShare Z95012201612 PanasonicLumix G7161614.06712.1 PanasonicLumix DMC-ZR314.11614.06712.1 PanasonicLumix DMC-G112.11614.06712.1 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: brandexpensive_camerascheap_cameras KodakNULL3 Panasonic21 Canon22 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! Tags: sql learn sql CTE You may also like What Is a CTE? We explain what CTE is and how to use common table expressions in SQL scripts. Read more When Should I Use a Common Table Expression (CTE)? Find out what CTEs are, what their main features are, and when to use them. Learn its syntax through examples that show how to use them in practice. Read more Long SQL Query vs. Recursive SQL Query Explore recursive CTE hierarchy in SQL server and find out how to tune your SQL query performance. Read more How to Organize SQL Queries with CTEs Common table expressions (CTEs) allow you to structure and organize your SQL queries. It is a necessity when you begin to move deeper into SQL. Read more Simplify SQL Code: Recursive Queries in DBMS Are you repeating the same query in every report? Are they getting too complicated? Use recursive queries to simplify SQL code! Read more How to Draw a Christmas Tree in SQL Learn how to use SQL to manipulate all kinds of data, from huge analytical queries to brief single-purpose statements. Read more Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.