Back to articles list August 26, 2020 - 7 minutes read SQL CTEs Explained with Examples 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 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. However, that simple fact doesn’t add much to your understanding. 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: Create a table with the list of districts and the corresponding average number of students per school. Join this table with the list of schools and display the required information. 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. school_namedistrict_idaverage_students Happy Kid2238 Smart2238 Sun5176 Montessori5176 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 employee_idfirst_namelast_namepositionoutletregionbonus 1MaxBlackmanager123South2305.45 2JaneWolfcashier123South1215.35 3KateWhitecustomer service specialist123South1545.75 4AndrewSmartcustomer service specialist123South1800.55 5JohnRudermanager105South2549.45 6SebastianCornellcashier105South1505.25 7DianaJohnsoncustomer service specialist105South2007.95 8SofiaBlancmanager224North2469.75 9JackSpidercustomer service specialist224North2100.50 10MariaLecashier224North1325.65 11AnnaWinfreymanager211North2390.25 12MarionSpencercashier211North1425.25 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: employee_idfirst_namelast_namepositionbonusaverage_bonus_for_position 2JaneWolfcashier1215.351367.88 6SebastianCornellcashier1505.251367.88 10MariaLecashier1325.651367.88 12MarionSpencercashier1425.251367.88 7DianaJohnsoncustomer service specialist2007.951863.69 9JackSpidercustomer service specialist2100.501863.69 3KateWhitecustomer service specialist1545.751863.69 4AndrewSmartcustomer service specialist1800.551863.69 5JohnRudermanager2549.452428.73 1MaxBlackmanager2305.452428.73 8SofiaBlancmanager2469.752428.73 11AnnaWinfreymanager2390.252428.73 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: employee_idfirst_namelast_namepositionregionbonusaverage_bonus_for_positionaverage_bonus_for_region 2JaneWolfcashierSouth1215.351367.881847.11 6SebastianCornellcashierSouth1505.251367.881847.11 10MariaLecashierNorth1325.651367.881942.28 12MarionSpencercashierNorth1425.251367.881942.28 7DianaJohnsoncustomer service specialistSouth2007.951863.691847.11 9JackSpidercustomer service specialistNorth2100.501863.691942.28 3KateWhitecustomer service specialistSouth1545.751863.691847.11 4AndrewSmartcustomer service specialistSouth1800.551863.691847.11 5JohnRudermanagerSouth2549.452428.731847.11 1MaxBlackmanagerSouth2305.452428.731847.11 8SofiaBlancmanagerNorth2469.752428.731942.28 11AnnaWinfreymanagerNorth2390.252428.731942.28 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: Calculate the average bonus across employees for each outlet (CTE: avg_per_outlet). Find the minimum average bonus across outlets (CTE: min_bonus_outlet). Find the maximum average bonus across outlets (CTE: max_bonus_outlet). 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: outletaverage_bonus_for_outletmin_average_bonus_for_outletmax_average_bonus_for_outlet 1052020.881716.782020.88 1231716.781716.782020.88 2111907.751716.782020.88 2241965.301716.782020.88 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! 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 Subquery vs. CTE: A SQL Primer SQL subqueries and CTEs seem similar, but even pros may not know all their quirks. Learn their similarities, differences, and best use cases. 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 Improving Query Readability with Common Table Expressions What is a Common Table Expression, or CTE? Where do you use them, and why? This post answers your questions. Read more How to Organize SQL Queries When They Get Long Long queries are very hard for beginners to structure and understand. Learn the best practices for writing and formatting complex SQL code! Read more Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.