What A CTE Is
A common table expression, or CTE, is a named temporary result defined with the WITH keyword. It exists only for the duration of the query that follows. Think of it as a named building block you can reference like a table.
Why Use Them
CTEs make queries readable by breaking logic into named steps:
- Each CTE handles one clear stage, like filtering or aggregating.
- Later parts of the query reference the CTE by name.
- You can chain several CTEs separated by commas.
CTE vs Subquery
A CTE and a subquery in the FROM clause often produce the same plan. The CTE wins on clarity, especially when the same intermediate result is referenced more than once, since you write it just one time.
A Note On Materialization
Some databases materialize a CTE into a temporary result, while others inline it like a view. Materialization can help or hurt, so check your engine if a CTE based query is unexpectedly slow.
Key idea
A CTE uses WITH to name a temporary result, making multi step queries readable and letting you reference an intermediate result by name more than once.