Querying
Common Table Expressions
A Common Table Expression (CTE) is a named temporary result set defined by a `WITH` clause that precedes a `SELECT`, `INSERT`, `UPDATE`, or `DELETE` statement.
Overview
A Common Table Expression (CTE) is a named temporary result set defined by a WITH clause that precedes a SELECT, INSERT, UPDATE, or DELETE statement. Introduced in SQL-99, a CTE exists only for the duration of the statement that references it and is not stored in the catalog. Conceptually it resembles a derived table or inline view, but because it is named, it can be referenced multiple times within the same statement, which improves readability and avoids repeating the same subquery text. A CTE may be recursive, in which case it references itself and is typically used to traverse hierarchical or graph-shaped data such as organizational charts, bill-of-materials structures, or transitive closures. Non-recursive CTEs are the more common form and behave much like a view scoped to a single statement. Because a CTE is a logical construct, the optimizer is free to inline, materialize, or otherwise transform it; performance therefore depends on the database engine and the shape of the query rather than on the CTE syntax itself. CTEs are widely supported across major relational systems, though exact feature coverage varies.
Syntax and usage
The general form is WITH name [(column, ...)] AS (query) [, name2 AS (query2)] SELECT .... The optional column list renames the CTE's output columns. Multiple CTEs are separated by commas and later CTEs can reference earlier ones. A recursive CTE is written as WITH RECURSIVE name AS (anchor UNION ALL recursive) SELECT ...; the anchor term supplies the base rows and the recursive term references name to produce further rows. Standard SQL uses WITH RECURSIVE, while some vendors accept plain WITH for recursive definitions or require a different keyword. CTEs may appear before SELECT, INSERT ... SELECT, UPDATE, and DELETE, and in some systems before CREATE TABLE AS. A CTE can be referenced in the main query's FROM, joined to other tables, and used in WHERE, GROUP BY, and ORDER BY like any table expression. Scoping is statement-local: the name is visible only to the statement it introduces. Vendor differences include support for materialization hints, SEARCH and CYCLE clauses in newer standards, and whether a CTE can be referenced more than once without being re-evaluated.
How it works
Logically, a CTE defines a named relation whose definition is evaluated when the statement runs. The database does not necessarily compute it once and reuse the result. Most optimizers treat a CTE as a candidate for inlining, merging its definition into the surrounding query, or materializing it into a temporary work table. Inlining often allows predicate pushdown and better join ordering; materialization can help when the CTE is referenced several times or when the optimizer would otherwise recompute an expensive expression. For recursive CTEs, evaluation proceeds iteratively: the anchor query produces the initial working set, then the recursive term is evaluated against rows produced in the previous iteration, and results are accumulated until an iteration returns no new rows. This fixed-point process terminates when the recursive term yields an empty set, so a poorly bounded recursion can run indefinitely or exhaust resources. Because the CTE is a logical name rather than a physical object, its columns and types are inferred from the defining query. Nullability and type coercion follow the same rules as the underlying expressions, and the CTE name does not persist after the statement completes.
Examples
A simple non-recursive CTE that aggregates orders before joining: WITH totals AS (SELECT customer_id, SUM(amount) AS total FROM orders GROUP BY customer_id) SELECT c.name, t.total FROM customers c JOIN totals t ON t.customer_id = c.id WHERE t.total > 1000. A recursive CTE walking an employee hierarchy: WITH RECURSIVE chain AS (SELECT id, manager_id, 1 AS depth FROM employees WHERE manager_id IS NULL UNION ALL SELECT e.id, e.manager_id, c.depth + 1 FROM employees e JOIN chain c ON e.manager_id = c.id) SELECT * FROM chain ORDER BY depth. Multiple CTEs chained together: WITH recent AS (SELECT * FROM orders WHERE order_date >= DATE '2024-01-01'), ranked AS (SELECT customer_id, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC) AS rn FROM recent) SELECT * FROM ranked WHERE rn = 1. Each example shows a CTE referenced in the main query's FROM clause, and the recursive example relies on UNION ALL to accumulate rows across iterations.
Common pitfalls
A frequent mistake is assuming a CTE is always evaluated once. On several engines a non-recursive CTE referenced multiple times may be inlined and recomputed, so a CTE is not automatically a performance optimization; it can be slower than a temporary table or a repeated subquery. Another pitfall is unbounded recursion: a recursive CTE without a termination condition, or with cyclic data, can loop until an error or resource limit. Some systems require an explicit depth or cycle guard. Column naming can also surprise users: without an explicit column list, the CTE inherits names from the defining query, and duplicate or ambiguous names cause errors. Ordering inside a CTE is generally not guaranteed to be preserved by the outer query, so ORDER BY in a CTE should not be relied on for final output ordering. Finally, scoping errors occur when a CTE is referenced outside the statement that defines it, or when a later CTE in the same WITH list is referenced by an earlier one, which is not allowed.
Best practices
Use CTEs to break complex queries into readable, named steps rather than deeply nested subqueries. Give each CTE a descriptive name and an explicit column list when the derived names would be unclear. Keep each CTE focused on one logical transformation so it can be reasoned about and tested independently. When a CTE is referenced multiple times and the engine tends to inline it, consider whether a temporary table or a materialized view is more appropriate for the workload. For recursive CTEs, always include a termination condition and, where supported, a depth or cycle guard; test with cyclic input. Avoid relying on ORDER BY inside a CTE for final result ordering. Check the execution plan to confirm whether the engine inlined or materialized the CTE, since this affects performance. Finally, prefer standard WITH RECURSIVE syntax for portability, and document any vendor-specific hints or clauses used.
Frequently asked questions
Is a CTE the same as a subquery? Functionally similar, but a CTE is named and can be referenced multiple times in the same statement, while a subquery is written inline each time. A CTE also improves readability for multi-step logic. Does a CTE improve performance? Not inherently. It is a logical construct; whether it is inlined or materialized depends on the optimizer. It can help or hurt depending on the query and engine. Can a CTE be recursive? Yes. A recursive CTE references itself and is defined with WITH RECURSIVE in standard SQL, using an anchor term and a recursive term combined with UNION ALL. It is commonly used for hierarchies and graph traversal, and must have a termination condition to avoid infinite iteration.