Chapter 11 — Key Takeaways

The big idea

A CTE (WITH name AS (...)) names a temporary result, turning nested subqueries into a readable, top-to-bottom pipeline. Recursive CTEs add something nothing else in SQL can do: traverse hierarchies of unknown depth.

Plain CTEs

  • Define named steps up front; reference them like tables in the main query.
  • Chain multiple CTEs (comma-separated); each can use the previous → a readable pipeline.
  • CTE vs. subquery: prefer a CTE for clarity, reuse, or recursion. Since PostgreSQL 12, simple single-use CTEs are inlined by default (MATERIALIZED/NOT MATERIALIZED to override).

Recursive CTEs

Structure — always the same:

WITH RECURSIVE t AS (
    <anchor / base case>          -- runs once: the starting rows
    UNION ALL
    <recursive term>             -- references t; runs until no new rows
)
SELECT ... FROM t;
  • Down (descendants): anchor = root(s); recursive term joins children to the CTE.
  • Up (ancestors): anchor = target node; recursive term joins to the parent.
  • Carry a level/depth counter (increment in the recursive term) for indentation and roll-ups.
  • Roll-up pattern: propagate an ancestor id (e.g., root_id) down to all descendants, then GROUP BY it. (Case Study 1.)

Safety (recursion's sharp edge)

  • Cycles → infinite recursion. UNION ALL can't detect revisits. Guard with the CYCLE clause (or a manual path array) and/or a depth limit (WHERE depth < N). (Case Study 2.)
  • Defend at two layers: the query (cycle/depth guard) and the data (validation preventing cycles). Integrity belongs in the data, not just the query.
  • Use UNION ALL (not UNION); ensure the recursive term references the CTE.

Performance (theme #5)

Index the recursion column (parent_category_id, manager_id). For huge, hot trees, consider a closure table or materialized path (Chapter 21) — trade write cost for fast reads.

You can now…

  • ☐ Write single and chained CTEs for readable pipelines.
  • ☐ Choose CTE vs. subquery (clarity, reuse, recursion).
  • ☐ Write recursive CTEs down (descendants) and up (ancestors) with a depth column.
  • ☐ Roll a tree up by mapping each node to its root.
  • ☐ Guard recursion against cycles and runaway depth — in the query and the data.

Looking ahead

Chapter 12 — Window Functions. Rankings, running totals, moving averages, and per-row comparison to a group — analytics without collapsing rows. The single most underused feature in SQL, and a game-changer once it clicks.

One sentence to carry forward: Name your steps with CTEs for clarity, use WITH RECURSIVE to walk any hierarchy — and always guard recursion against cycles in both the query and the data.