Common Table Expressions: CTE Basics

1. What a Common Table Expression actually is

A Common Table Expression, or CTE, is a named, temporary result set defined inside a single SQL statement using the WITH clause. Unlike a view, a CTE exists only for the duration of the statement in which it is declared, and is discarded afterward. It is not an object in the database schema, but a structural aid that lets you break a complex query into named, logically separated steps.

The core idea behind a CTE is readability through naming. Instead of nesting a subquery anonymously inside parentheses, you give it a descriptive name with WITH active_customers AS (...), which can then be used in the main query like an ordinary table in FROM or JOIN. This naming is the decisive difference from a subquery: the reader does not have to parse the contents of the parentheses first to understand what the intermediate result set represents, the name already says it.

All common relational databases support CTE syntax following the SQL-99 standard: PostgreSQL since version 8.4, MySQL since version 8.0, SQL Server since 2005, and Oracle since 9i. The basic syntax is nearly identical across all of them, which makes a CTE one of the most portable advanced SQL techniques, one that carries over between database systems without the core logic needing to change.

2. The WITH clause in detail

A CTE's syntax starts with the WITH keyword, followed by the expression's name, an optional list of column names in parentheses, the AS keyword, and the actual query in parentheses. After the closing parenthesis comes the main query, which references the CTE by name as if it were a table. Order matters here: the WITH clause always sits at the very start of the statement, before SELECT, INSERT, UPDATE, or DELETE.

The optional column list after the CTE name is useful when the expressions in the inner query do not carry unique names, for example computed values like aggregations without an alias. Without an explicit column list, the CTE inherits the column names from the inner SELECT list, exactly like a normal subquery. A type error or an ambiguous column is caught as soon as the CTE definition is parsed, not only when the main query runs.

-- Basic CTE: named result set used like a table
WITH active_customers AS (
    SELECT customer_id, first_name, last_name, country
    FROM customers
    WHERE status = 'active'
      AND last_order_date >= CURRENT_DATE - INTERVAL '90 days'
)
SELECT country, COUNT(*) AS customer_count
FROM active_customers
GROUP BY country
ORDER BY customer_count DESC;

-- CTE with explicit column list for clarity
WITH monthly_totals (order_month, total_revenue) AS (
    SELECT DATE_TRUNC('month', order_date), SUM(amount)
    FROM orders
    GROUP BY DATE_TRUNC('month', order_date)
)
SELECT order_month, total_revenue
FROM monthly_totals
WHERE total_revenue > 10000;

3. CTEs versus nested subqueries

The practical advantage of a CTE shows most clearly when compared with deeply nested subqueries. A query with three or four nested SELECT blocks forces the reader to think from the inside out, while the actual execution order does run inside out, but the reading flow in the code goes from outside in. A CTE reverses that: every step is declared top to bottom, in exactly the order you think through the problem, first filter the raw data, then aggregate, then join.

A further readability gain comes from reuse within the same query. A subquery needed twice must either be written out twice or extracted into a view. A CTE is defined once and can be referenced any number of times in the main query without duplicating the code. That not only reduces line count but also the risk that two copies of the same logic drift apart on a later change.

When debugging, the structure of a CTE pays off further: a single CTE block can be run in isolation by temporarily turning it into a standalone query. With nested subqueries, you instead have to manually unwind the parenthesis structure to test an inner step separately, which is error prone at several nesting levels.

-- Nested subqueries: hard to read top-down, logic buried in parentheses
SELECT p.product_name, s.total_qty
FROM products p
JOIN (
    SELECT product_id, SUM(qty) AS total_qty
    FROM (
        SELECT oi.product_id, oi.qty
        FROM order_items oi
        JOIN orders o ON o.order_id = oi.order_id
        WHERE o.order_date >= CURRENT_DATE - INTERVAL '30 days'
    ) recent_items
    GROUP BY product_id
) s ON s.product_id = p.product_id
WHERE s.total_qty > 50;

-- Same logic as a CTE chain: each step reads top-down
WITH recent_items AS (
    SELECT oi.product_id, oi.qty
    FROM order_items oi
    JOIN orders o ON o.order_id = oi.order_id
    WHERE o.order_date >= CURRENT_DATE - INTERVAL '30 days'
),
sales_by_product AS (
    SELECT product_id, SUM(qty) AS total_qty
    FROM recent_items
    GROUP BY product_id
)
SELECT p.product_name, s.total_qty
FROM products p
JOIN sales_by_product s ON s.product_id = p.product_id
WHERE s.total_qty > 50;

4. Combining multiple CTEs in one query

A single WITH clause can define several CTEs, separated by commas, where each later CTE may access all CTEs defined before it. This chaining is the real tool for complex analytics: the first CTE filters raw data, the second aggregates the filtered data, the third combines the result with another data source, and the main query ties everything together at the end. Each step stays simple and checkable on its own.

The direction of reference matters: a CTE can only access CTEs that appear before it in the WITH clause, not ones that follow later. This restriction enforces a natural top-down dependency order that matches the reading order and rules out circular references from the start, except for a CTE explicitly marked RECURSIVE, which is allowed to reference itself.

In practice, a chain of several CTEs often replaces an entire ETL script that would otherwise create multiple temporary tables. The advantage over real intermediate tables: the optimizer sees the whole logic in one statement and can, in many cases, push filters earlier than would be possible with manually created intermediate tables.

-- Multiple CTEs chained together, each building on the previous
WITH filtered_orders AS (
    SELECT order_id, customer_id, order_date, amount
    FROM orders
    WHERE order_date >= '2026-01-01'
      AND status <> 'cancelled'
),
customer_totals AS (
    SELECT customer_id, SUM(amount) AS total_spent, COUNT(*) AS order_count
    FROM filtered_orders
    GROUP BY customer_id
),
ranked_customers AS (
    SELECT customer_id, total_spent, order_count,
           RANK() OVER (ORDER BY total_spent DESC) AS spend_rank
    FROM customer_totals
)
SELECT c.first_name, c.last_name, r.total_spent, r.spend_rank
FROM ranked_customers r
JOIN customers c ON c.customer_id = r.customer_id
WHERE r.spend_rank <= 10;

5. Distinguishing from the recursive CTE

An ordinary CTE, as shown in the examples so far, is also called a non-recursive CTE: it is evaluated once and returns a fixed result set. Distinct from that is the recursive CTE, introduced with WITH RECURSIVE, which references itself within its own definition and can thereby produce iteratively growing result sets, for example for hierarchical structures like category trees or org charts.

For the basics covered in this post, it is important to know that any CTE without self-reference is not recursive, even though the RECURSIVE keyword must still be written in front of the entire WITH clause in some databases as soon as at least one recursive CTE appears in the same statement. PostgreSQL and SQL Server handle the RECURSIVE keyword slightly differently, MySQL 8 follows PostgreSQL's behavior. The details of recursive CTEs and their termination conditions are a distinct, deeper topic on their own.

6. Materialization: how the database executes a CTE

A common misconception is assuming a CTE is always materialized as a separate intermediate step, meaning physically computed and cached, before the main query touches it. Historically that was true in PostgreSQL before version 12: every CTE formed an optimization boundary, the so called optimization fence, across which the planner could not push filters or joins. Since PostgreSQL 12, a CTE is inlined by default like a subquery, unless it is recursive, referenced multiple times, or has side effects.

PostgreSQL has allowed explicit control since version 12 with MATERIALIZED and NOT MATERIALIZED right after the AS keyword. MATERIALIZED forces the old optimization fence semantics, which makes sense when a CTE is used multiple times and recomputing it would be expensive. NOT MATERIALIZED lets the optimizer dissolve the CTE's boundaries and push predicates in from outside, which is usually faster for single use.

SQL Server and Oracle have never had a fixed optimization fence for CTEs, there they are typically folded into the execution plan like views and either inlined or materialized depending on the cost estimate. The practical advice is: whenever a CTE has a performance problem, always check the actual execution plan instead of assuming a fixed materialization behavior, since this differs between database systems and even between versions of the same system.

-- PostgreSQL 12+: explicit materialization control
WITH expensive_calc AS MATERIALIZED (
    SELECT customer_id, SUM(amount) AS total
    FROM orders
    GROUP BY customer_id
)
SELECT * FROM expensive_calc WHERE total > 1000
UNION ALL
SELECT * FROM expensive_calc WHERE total < 100;

-- NOT MATERIALIZED lets the planner push predicates into the CTE
WITH filtered AS NOT MATERIALIZED (
    SELECT * FROM orders
)
SELECT * FROM filtered WHERE order_id = 42;

7. CTEs before INSERT, UPDATE and DELETE

The WITH clause is not limited to SELECT statements. In PostgreSQL, SQL Server, and Oracle, a CTE can also precede INSERT, UPDATE, or DELETE to first compute the set of rows to be affected in a readable way, before the actual data change touches it. That is especially useful for complex conditions where the filter logic itself already requires several joins or aggregations.

MySQL 8 supports CTEs before UPDATE and DELETE as well, though with restrictions on directly modifying the same table referenced inside the CTE. A join back to the target table on the primary key usually helps here. The advantage remains the same across all systems: the selection logic is stated once, readably, and kept separate from the actual change operation, which makes code review of data-modifying statements considerably easier.

-- CTE before UPDATE: readable selection logic, separate from the write
WITH stale_accounts AS (
    SELECT account_id
    FROM accounts
    WHERE last_login < CURRENT_DATE - INTERVAL '365 days'
      AND status = 'active'
)
UPDATE accounts
SET status = 'inactive'
WHERE account_id IN (SELECT account_id FROM stale_accounts);

-- CTE before DELETE with a join for row-limited deletion
WITH duplicate_rows AS (
    SELECT id,
           ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at) AS rn
    FROM subscribers
)
DELETE FROM subscribers
WHERE id IN (SELECT id FROM duplicate_rows WHERE rn > 1);

8. Scope and visibility of CTEs

A CTE is visible exclusively within the statement in which it was defined. As soon as the statement finishes, the CTE no longer exists, there is no persisted state and no way to reference the same CTE again in a subsequent statement without redeclaring it. That fundamentally distinguishes a CTE from a temporary table, which persists across multiple statements within a session.

Within a chain of several CTEs, strict lexical top-down scoping applies: a later CTE sees all earlier ones, but no CTE sees itself, except in the recursive case, and no CTE sees one defined only later. Name collisions with existing tables in the schema resolve in favor of the CTE: if a CTE carries the same name as a real table, the CTE definition wins inside the query, which in practice can cause unintended confusion and should therefore be avoided.

9. Common mistakes and anti-patterns

The most common mistake is assuming a CTE is automatically more performant than a subquery just because it is more readable. Readability and performance are two independent properties. A poorly written CTE with unnecessarily forced early materialization can, on older PostgreSQL versions, even be slower than the equivalent subquery, because the optimizer can no longer push predicates in. The second common mistake is chaining too many CTEs without real added value, where each individual CTE only performs a trivial column rename, which tends to hurt readability rather than help it.

A third mistake concerns multiply referenced CTEs that contain expensive aggregations: if a CTE is used twice in the main query without MATERIALIZED, the database may execute the underlying computation twice instead of computing it once and reusing the result. In that case it is worth explicitly checking the execution plan with EXPLAIN ANALYZE to see whether a real temporary table or a materialized CTE would be the better choice.

10. Summary

A Common Table Expression solves a single but far reaching problem: it makes complex queries readable top to bottom, instead of hiding them inside nested parentheses. The WITH clause names intermediate results, several CTEs can be chained together, and each individual CTE can be checked and tested in isolation. That is the central difference from an anonymous subquery, which can only be understood as a whole.

It remains important to consider readability and performance separately: a CTE is not automatically faster, its materialization behavior differs between database systems and versions. Anyone referencing a CTE multiple times or using it in performance-critical code should check the actual execution plan instead of relying on assumptions. For hierarchical data with self-reference, the recursive CTE with WITH RECURSIVE is the next logical step.

11. FAQ: Common Table Expressions