WITH: Single and Multiple CTEs

Concept

A common table expression is a named temporary result set, defined with WITH, that exists only for the duration of one statement. It is a derived table with a name - and the name is the whole point, because it turns a nest of parentheses into a readable sequence of steps.

Syntax

WITH cte_name AS (
    SELECT ...
)
SELECT ...
FROM   cte_name;

-- Several CTEs, separated by commas; later ones may use earlier ones
WITH first_cte AS (
    SELECT ...
),
second_cte AS (
    SELECT ... FROM first_cte ...
)
SELECT ... FROM second_cte;

Example

-- One CTE: employees above their department average
WITH dept_avg AS (
    SELECT dept_id, AVG(salary) AS avg_salary
    FROM   employees
    WHERE  dept_id IS NOT NULL
    GROUP BY dept_id
)
SELECT e.first_name,
       e.dept_id,
       e.salary,
       ROUND(d.avg_salary, 2) AS dept_average
FROM   employees e
JOIN   dept_avg d ON d.dept_id = e.dept_id
WHERE  e.salary > d.avg_salary
ORDER BY e.dept_id, e.salary DESC;

-- Several CTEs, each a readable step
WITH order_lines AS (
    SELECT order_id,
           SUM(quantity * unit_price) AS line_value,
           COUNT(*)                   AS line_count
    FROM   order_items
    GROUP BY order_id
),
customer_totals AS (
    SELECT o.customer_id,
           COUNT(*)          AS order_count,
           SUM(ol.line_value) AS lifetime_value
    FROM   orders o
    JOIN   order_lines ol ON ol.order_id = o.id
    WHERE  o.status <> 'cancelled'
    GROUP BY o.customer_id
)
SELECT c.name,
       c.country,
       ct.order_count,
       ct.lifetime_value
FROM   customers c
JOIN   customer_totals ct ON ct.customer_id = c.id
ORDER BY ct.lifetime_value DESC;

Explanation

Read the second query top to bottom: summarise the lines, then summarise per customer, then attach customer details. Written as nested subqueries the same logic would be three levels of parentheses read inside out. Nothing about the result changes - only whether the next person can follow it.

Each CTE may reference any CTE defined before it in the same WITH clause. They cannot reference later ones, unless the CTE is recursive and references itself.

CTE, subquery, view or temp table?

Important rules

• A CTE exists only for the statement that defines it.
• WITH can precede SELECT, and in PostgreSQL and SQL Server also INSERT, UPDATE and DELETE.
• Referencing the same CTE twice may re execute it. PostgreSQL materialises it once by default before version 12; MySQL may merge or materialise depending on the query.
• A CTE cannot be indexed. If the intermediate result is large and reused, a temporary table is usually faster.
• Support: MySQL 8.0+, MariaDB 10.2+, PostgreSQL, SQL Server 2005+, Oracle 11g R2+, SQLite 3.8.3+. Not MySQL 5.7.

Common mistakes

• Expecting a CTE to persist beyond the statement.
• Referencing a CTE defined later in the same WITH clause.
• Forgetting the comma between CTEs, or adding one before the final SELECT.
• Building a ten step CTE chain over huge tables and being surprised by the cost - a CTE is not a free cache.
• Using CTEs on MySQL 5.7, where they do not exist.

Best practices

• Name each CTE after the thing it produces: order_lines, customer_totals - not cte1.
• Keep each CTE to one clear job; chain them rather than writing one enormous block.
• Filter early inside the first CTE so later steps carry fewer rows.
• If the same CTE logic appears in several queries, promote it to a view.
• If an intermediate result is large and used more than once, materialise it into a temporary table.

Practice

1. Rewrite the "employees above their department average" query using a derived table instead of a CTE, and compare readability.
2. Build a two step CTE that finds each country's best selling product category.
3. Explain when you would promote a CTE to a view and when to a temporary table.