A CTE (Common Table Expression) is a query you give a name to at the start of a statement, with WITH, so you can use it afterwards as if it were a table. It does the same job as a subquery, but it reads from top to bottom, in the order you think through the problem.

Syntax
WITH cteName AS (
    SELECT ...
)
SELECT ...
FROM cteName;

A CTE only exists for the duration of the statement. Unlike a view, it isn’t stored in the database.

A first CTE

We want the customers who spent more than the average spend per customer. Without a CTE you would nest one subquery inside another. With a CTE, we first work out each customer’s spend and then use it twice:

PostgreSQL in your browser · Ctrl+Enter to run · changes are rolled back afterwards
name lastName spent
Helen Ward 4999.00
Peter Davis 4304.25
Mavin Pettitt 3199.78

The average spend of the five customers with orders is $3,170.50, and only three are above it. Try running just the SELECT inside the CTE to see the intermediate table.

Several CTEs in one query

You can define several, separated by commas, and each one can use the previous ones. It’s the clearest way to build a complex query step by step:

PostgreSQL in your browser · Ctrl+Enter to run · changes are rolled back afterwards
name orders biggestOrder
Helen 2 2999.50
Mavin 2 2199.80

Notice that the final WHERE can use the orders alias: inside the summary CTE it is already a named column.

CTE vs subquery, view and temporary table

Where it lives Reusable When to use it
Subquery Inside the query No Short calculations used once
CTE In the statement Several times within the same statement Long queries that read better in steps
View In the database In any query Logic shared by several queries or users
Temporary table In tempdb until dropped Across the whole session Large intermediate results queried many times

Recursive CTEs

A CTE can refer to itself. This is the standard way to walk hierarchies such as an org chart or a category tree. It has two parts joined by UNION ALL: the anchor, which gives the starting point, and the recursive part, which repeats until it returns no rows.

The simplest example generates the numbers 1 to 5:

WITH numbers AS (
    SELECT 1 AS n           -- anchor
    UNION ALL
    SELECT n + 1            -- recursive part
    FROM numbers
    WHERE n < 5
)
SELECT n FROM numbers;
n
1
2
3
4
5
In SQL Server you just write WITH. PostgreSQL, MySQL and SQLite require WITH RECURSIVE for recursive CTEs, which is why this example can’t run in the page editor. SQL Server stops recursion after 100 levels to prevent infinite loops; you can change that with OPTION (MAXRECURSION n).

Common mistakes

  • Forgetting the previous semicolon. In SQL Server, if the previous statement doesn’t end with ;, WITH can be mistaken for another clause. That’s why you often see ;WITH.
  • Using the CTE in a second statement. It only exists for the statement right after it.
  • Sorting inside the CTE. An ORDER BY inside the CTE doesn’t guarantee the order of the final result; put it in the outer SELECT.

Practice

  1. Change the first CTE to return the customers who spend less than the average.
  2. Add a column with each customer’s total spend to the second query.
  3. Write a CTE with the September 2022 orders and use it to count how many there are per product description.