19th Jan 2022 8 minutes read What Is a Common Table Expression (CTE) in SQL? Ignacio L. Bisso sql learn sql cte Table of Contents CTEs Help Simplify Queries Learning SQL Common Table Expressions by Example Using CTEs in Advanced SQL Queries Nested CTEs in SQL Queries Recursive Queries and Common Table Expressions SQL CTEs Are a Powerful Resource Updated on: July 19, 2024 The common table expression (CTE) is a powerful construct in SQL that helps simplify a query. CTEs work as virtual tables (with records and columns), created during the execution of a query, used by the query, and eliminated after query execution. A CTE is defined using a CTE query definition, which specifies the structure and content of the CTE. CTEs often act as a bridge to transform the data in source tables to the format expected by the query. Question: What Is a Common Table Expression in SQL? A Common Table Expression (CTE) is like a named subquery. It functions as a virtual table that only its main query can access. CTEs can help simplify, shorten, and organize your code. A common table expression, or CTE, is a temporary named result set created from a simple SELECT statement that can be used in a subsequent SELECT statement. Each SQL CTE is like a named query, whose result is stored in a virtual table (a CTE) to be referenced later in the main query. The best way to learn common table expressions is through practice. I recommend LearnSQL.com's interactive Recursive Queries course. It contains over 100 exercises that teach CTEs starting with the basics and progressing to advanced topics like recursive common table expressions. CTEs Help Simplify Queries Let's start with the syntax of a common table expression. WITH my_cte AS ( SELECT a,b,c FROM T1 ) SELECT a,c FROM my_cte WHERE .... The name of this CTE is my_cte, and the CTE query is SELECT a,b,c FROM T1. The CTE starts with the WITH keyword, after which you specify the name of your CTE, then the content of the query in parentheses. The main query comes after the closing parenthesis and refers to the CTE. Here, the main query (also known as the outer query) is SELECT a,c FROM my_cte WHERE …. There are many entry-level examples in this excellent introductory article on CTEs. Other introductory articles include “Improving Query Readability with Common Table Expressions” and “When Should I Use a Common Table Expression (CTE)?” which explain common table expressions. Learning SQL Common Table Expressions by Example In this section, we present some examples of SQL queries using common table expressions. All examples are based on a database for a chain of cellular phone stores. The table sales, shown below, has one record per product sold: branchdateselleritemquantityunit_price Paris-12021-12-07CharlesHeadphones A2180 London-12021-12-06JohnCell Phone X22120 London-22021-12-07MaryHeadphones A1160 Paris-12021-12-07CharlesBattery Charger150 London-22021-12-07MaryCell Phone B2290 London-12021-12-07JohnHeadphones A0575 London-12021-12-07SeanCell Phone X12100 In the first example, we obtain a report with the same records from the sales table but add an extra column with the price of the most expensive item sold in the same branch that day. To obtain the price of the most expensive item, we use a common table expression like this: WITH highest AS ( SELECT branch, date, MAX(unit_price) AS highest_price FROM sales GROUP BY branch, date ) SELECT sales.*, h.highest_price FROM sales JOIN highest h ON sales.branch = h.branch AND sales.date = h.date This query defines an SQL CTE called highest whose result creates a virtual table. The virtual table has columns branch, date, and highest_price, containing the branch name, the date, and the highest unit price sold that day in this branch, respectively. Then, the outer query is executed, which uses the highest virtual table as if it were a regular table. Finally, we join the result of the CTE highest with the sales table. The result of the entire query is shown below: branchdateselleritemquantityunit_pricehighest_ price Paris-12021-12-07CharlesHeadphones A218080 London-12021-12-06JohnCell Phone X22120120 London-22021-12-07MaryHeadphones A116090 Paris-12021-12-07CharlesBattery Charger15080 London-22021-12-07MaryCell Phone B229090 London-12021-12-07JohnHeadphones A0575100 London-12021-12-07SeanCell Phone X12100100 In the next example, we generate a report with the highest daily revenue by branch. WITH daily_revenue AS ( SELECT branch, date, SUM(unit_price * quantity) AS daily_revenue FROM sales WHERE EXTRACT(YEAR FROM date) = 2021 GROUP BY 1,2 ) SELECT branch, MAX(daily_revenue) max_daily_revenue FROM daily_revenue GROUP BY 1 ORDER BY 2 DESC In this query, a CTE called daily_revenue has columns branch, date, and daily_revenue for each date in 2021. Then in the outer query, we obtain the highest amount of revenue for each branch in 2021. The report is ordered by max_daily_revenue in descending order. Below are the results of this query. branchmax_daily_revenue London-1575 London-2240 Paris-1135 Using CTEs in Advanced SQL Queries You can define two or more CTEs and use them in the main query. In the next example, we show you how to divide and organize a long query using SQL CTEs. By naming different parts of the query, CTEs make the query easy to read. Suppose we want a report with the total monthly revenue in London in 2021, but we also want the revenue for each branch in London in the same report. Here, we create two CTEs then join them in the main query. WITH london1_monthly_revenue AS ( SELECT EXTRACT(MONTH FROM date) as month, SUM(unit_price * quantity) AS revenue FROM sales WHERE EXTRACT(YEAR FROM date) = 2021 AND branch = 'London-1' GROUP BY 1 ), london2_monthly_revenue AS ( SELECT EXTRACT(MONTH FROM date) as month, SUM(unit_price * quantity) AS revenue FROM sales WHERE EXTRACT(YEAR FROM date) = 2021 AND branch = 'London-2' GROUP BY 1 ) SELECT l1.month, l1.revenue + l2.revenue AS london_revenue, l1.revenue AS london1_revenue, l2.revenue AS london2_revenue FROM london1_monthly_revenue l1, london2_monthly_revenue l2 WHERE l1.month = l2.month In the query above, we define two CTEs, london1_monthly_revenue and london2_monthly_revenue, to obtain the monthly revenue in 2021 for each branch in London. Finally, we join both CTEs using the month column and calculate the total revenue for London by adding up the revenues of the two branches. The result of the query is below: monthlondon_revenuelondon1_revenuelondon2_revenue 121055815240 In the following example, we obtain a report to inform each branch of the date on which the largest ticket (i.e., amount of the item-quantity combination) was sold and the amount of this ticket. To do this, we need to create a CTE that ranks the tickets (the column position is the ranking) for each branch by the ticket amount. WITH tickets AS ( SELECT distinct branch, date, unit_price * quantity AS ticket_amount, ROW_NUMBER() OVER ( PARTITION BY branch ORDER by unit_price * quantity DESC ) AS position FROM sales ORDER BY 3 DESC ) SELECT branch, date, ticket_amount FROM tickets WHERE position =1 In the query above, we create a CTE with the columns branch, date, ticket_amount, and position. Then in the outer query, we filter only for those records with position = 1 to obtain what we want, the largest ticket by branch. The result of the query is shown below: branchdateticket_amount London-12021-11-2450 London-22021-11-1270 Paris-12021-12-780 Nested CTEs in SQL Queries The next example shows a nested CTE. The idea is to generate a report with all the items priced over $90 and the quantity of these items sold by the London-2 branch. WITH over_90_items AS ( SELECT DISTINCT item, unit_price FROM sales WHERE unit_price >=90 ), london2_over_90 AS ( SELECT o90.item, o90.unit_price, coalesce(SUM(s.quantity), 0) as total_sold FROM over_90_items o90 LEFT JOIN sales s ON o90.item = s.item AND s.branch = 'London-2' GROUP BY o90.item, o90.unit_price ) SELECT item, unit_price, total_sold FROM london2_over_90; The first CTE is over_90_items, which selects all the items priced greater than or equal to $90. The second CTE is london2_over_90, which selects the quantity sold by London-2 for each item included in over_90_items. This query has a nested CTE – note the FROM in the second CTE referring to the first. We use LEFT JOIN sales because London-2 may not have sold every item in over_90_items. The result of the query is: itemunit_pricetotal_sold Cell Phone X11000 Cell Phone X21200 Cell Phone B2907 Before going to the next section, I have some articles to suggest on common table expressions. Both “SQL CTEs Explained with Examples” and “Where Can I Find Good SQL CTE Exercises?” have a lot of examples and exercises. Recursive Queries and Common Table Expressions In relational databases, it’s common to have tables representing hierarchies of data like employee-manager, part-subpart, or parent-child. To traverse these hierarchies in any direction (from top to bottom or from bottom to top), databases use a construct called recursive common table expressions (recursive CTEs). RECURSIVE is a reserved word to define a CTE for traversing a recursive data structure. The form of the recursive query is like this: WITH RECURSIVE cte_name AS ( CTE_query_definition -- non recursive query term UNION ALL CTE_query_definition -- recursive query term ) SELECT * FROM cte_name; Recursive queries are outside the scope of this introductory article, but I have three others to suggest for those who want more on the topic: “How to Organize SQL Queries with CTEs,” “Do it in SQL: Recursive SQL Tree Traversal,” and “Get to Know the Power of SQL Recursive Queries.” They explain recursive queries in detail with lots of examples. SQL CTEs Are a Powerful Resource Common table expressions are a powerful resource of the SQL language. They let us create more readable queries and manage format differences between table data and report data. In this article, we have covered what a CTE is and how to use them in different types of queries. We have also mentioned CTEs may be used in recursive queries. I recommend the course Recursive Queries at LearnSQL.com, where you interactively learn how to work with common table expressions in SQL. You also learn how to process recursive data structures like graphs and trees in SQL using recursive CTEs. Also, check out the track Advanced SQL, where you go beyond the basics to become an SQL master. If you need to prepare for an SQL interview, there is an interesting article “Top 5 SQL CTE Interview Questions” discussing common table expressions from another perspective. If you have learned how to use CTEs, then you are one step further down the path toward an SQL developer. Keep growing! Tags: sql learn sql cte