Chapter 11 — Further Reading

Official reference (everyone)

  • PostgreSQL Docs: "WITH Queries (Common Table Expressions)." The complete reference — CTEs, RECURSIVE, the CYCLE and SEARCH clauses, and MATERIALIZED/NOT MATERIALIZED. https://www.postgresql.org/docs/current/queries-with.html
  • PostgreSQL Docs: notes on CTE materialization (v12+). When CTEs are inlined vs. materialized, and how to control it — relevant for Chapter 24.

Recursion deep dives (🔬 CS Student · 💻 Developer)

  • Articles/tutorials on "recursive CTE" patterns: trees, graphs, transitive closure, shortest paths, generating series. Seeing the anchor/recursive-term shape applied to many problems cements it.
  • "Cycle detection in recursive CTEs." Reinforces Case Study 2 — the CYCLE clause and the manual path-array technique.
  • Graph traversal in SQL. Recursive CTEs can do breadth/limited depth-first traversal; interesting for understanding their power and limits.

Hierarchy modeling (💻 Developer · 🏗️ DBA) — pairs with Chapter 21

  • "Models for hierarchical data in SQL" (adjacency list vs. nested set vs. materialized path vs. closure table). Recursive CTEs query the adjacency list model; the alternatives trade write cost for read speed. You'll study these patterns in Chapter 21.
  • Joe Celko, Trees and Hierarchies in SQL for Smarties. The classic deep treatment of representing and querying hierarchies.

Readability & style (everyone)

  • Blog posts on "CTEs for readable SQL." The case for naming steps; how CTEs turn 80-line nested queries into legible pipelines.
  • dbt's modeling guides. Modern analytics engineering leans heavily on CTEs for layered, testable transformations — a great real-world example of the pipeline style.

Reference (this book)

  • Appendix C — SQL Quick Reference: CTE and WITH RECURSIVE syntax.
  • Appendix I — SQL Cookbook: recursive recipes (tree roll-up, ancestor path, org chart).

Do, don't just read

  • Build the category tree and org chart yourself, both directions, with depth columns.
  • Reproduce the cycle bug: point two categories at each other, watch the query hang (cancel with Ctrl+C), then fix it with the CYCLE clause.
  • Refactor your messiest nested query into CTEs and feel the difference in readability.

Next: Chapter 12 — Window Functions: analytics without collapsing rows.