What is a CTE and when would you use one?

A common table expression is a named result set defined with WITH and referenced later in the query.

The main benefit is structure: a multi-step transformation reads top-to-bottom instead of inside-out through nested subqueries, and a CTE can be referenced more than once.

Recursive CTEs additionally handle hierarchies — an anchor member, UNION ALL, and a recursive member referencing the CTE itself. Be aware some engines materialise CTEs, which can act as an optimisation fence.