What Is a Common Table Expression (CTE) in SQL?

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.

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 and how it integrates with select statements.

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:

CTEs are particularly useful for breaking down complex queries into more manageable parts.

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:

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.

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:

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:

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:

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;

It is important to manage recursive CTEs carefully to avoid an infinite loop, which can occur if the recursion does not have a proper termination condition.

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!