Common table expression

A common table expression (CTE) is a named, temporary result set defined with a WITH clause at the start of a SQL query, which can then be referenced one or more times later in that same query as if it were a table. It exists only for the duration of that query and is not stored anywhere.

CTEs let a complex query be broken into readable, sequential steps — filter, then aggregate, then join — instead of nesting subqueries inside each other, which becomes hard to read and debug past a couple of levels. Some databases also support recursive CTEs, which repeatedly reference themselves to walk hierarchical or graph-like data, such as an organizational chart or a bill of materials, that a single non-recursive query cannot traverse.

CTEs are the standard way analysts and analytics engineers structure multi-step transformation logic, and they are the basic building block of models in tools like dbt, where each model is effectively a named, reusable query. A common misconception is that a CTE is automatically materialized or cached the way a materialized view is; in most databases it is not — the CTE's definition is inlined or re-executed by the query planner each time it is referenced, so it offers no inherent performance benefit over an equivalent subquery.