CTEs and subqueries

Naming the steps of a query

A common table expression lets you name an intermediate result, so a three-stage query reads as three named steps rather than as nested subqueries you have to unpick from the inside.

Recursive CTEs traverse hierarchies — category trees, org charts, dependency graphs — in a single statement, with a base case and a step, and a termination condition you must get right.

Modern PostgreSQL usually inlines a CTE into the surrounding query, but marking one as materialised forces evaluation once. That is occasionally the fix for a plan that recomputes an expensive step repeatedly.