A common table expression, or CTE, defines a named temporary result set within the execution scope of a single query.
Introduced in the SQL nineteen ninety-nine standard, CTEs are specified using the WITH keyword before the primary SELECT statement.
They provide a clean, literate programming style for SQL by breaking complex transformations into named sequential steps.
In contrast to deeply nested subqueries, CTEs read from top to bottom, mirroring how human analysts think about data workflows.
A statement can define multiple CTEs separated by commas, where later CTEs can query previously defined CTEs in the sequence.
For example, an initial CTE can clean dates, a second can compute regional totals, and the main query can rank the top regions.
CTEs exist strictly during query execution and do not consume permanent database storage or metadata catalogue space.
Recursive CTEs reference their own output, enabling queries to traverse hierarchical structures like file trees and graph networks.
A recursive CTE consists of an anchor member that seeds the recursion, paired with a recursive member linked by UNION ALL.
The recursion terminates automatically when the recursive query returns an empty result set on the latest iteration.
In many database systems, CTEs are treated as optimisation fences or inline views depending on the query planner engine.
Modern planners inline CTEs into the main query plan, optimising filters and indexes across boundaries seamlessly.
However, some engines historically materialised CTEs unconditionally, which could prevent the engine from pushing down WHERE filters.
Understanding whether your database inlines or materialises CTEs helps you decide between CTEs and temporary tables for big data.
Analysts should use CTEs as the default architecture for complex queries, enhancing code readability, debugging, and peer review.