A CTE (WITH clause) is a named, reusable result set. A subquery is an inline expression. Which to pick depends on three things: reuse, readability, and the planner.
Use a CTE when…
- You reference the same intermediate result more than once.
- The query becomes hard to read top-down without naming the steps.
- You want to reason about lineage step-by-step (CTEs become explicit nodes).
Use a subquery when…
- The intermediate result is used exactly once and is short.
- You need the planner to inline aggressively (older Postgres treated CTEs as fences).
- You want a correlated reference, which CTEs don't allow.
Lineage tip
Lineage graphs draw CTEs as their own stage nodes. If your goal is auditing 'where did this column come from?', CTEs make the answer easier to point to.