Chapter 9 — Further Reading

Official reference (everyone)

  • PostgreSQL Docs: "Subquery Expressions" (EXISTS, IN, NOT IN, ANY/SOME, ALL, row-comparison). The authoritative semantics, including the NULL behavior behind Case Study 1. https://www.postgresql.org/docs/current/functions-subquery.html
  • PostgreSQL Docs: "Scalar Subqueries" and "Table Expressions → Subqueries (derived tables)." Where each kind of subquery is allowed and what it must return.

On the NULL trap (everyone)

  • Any "NOT IN vs NOT EXISTS" article. Reinforce Case Study 1; this is among the most-written-about SQL gotchas for good reason.
  • "SQL NULL and the three-valued logic" explainers. The root cause; worth a second pass after Chapters 3, 5, and 9 together.

Subqueries vs. joins, and de-correlation (💻 Developer · 🏗️ DBA)

  • Articles on "correlated subquery performance" / "de-correlation." The optimizer transformation behind Case Study 2 — and when it can't apply.
  • Markus Winand, use-the-index-luke.com — sections on subqueries and EXISTS. How these forms interact with indexes (preview of Chapter 23).

Going deeper (🔬 CS Student)

  • LATERAL subqueries (PostgreSQL). "For each outer row, run this subquery and join its results" — a correlated subquery you can put in FROM and select columns from. Powerful for "top N per group." Learn it after CTEs (Chapter 11).
  • Relational calculus. Subqueries with EXISTS/ALL map closely to existential/universal quantifiers in logic — the calculus side of Chapter 4's algebra.

Reference (this book)

  • Appendix I — SQL Cookbook: anti-join recipes (NOT EXISTS, LEFT JOIN … IS NULL), "above average," "top N per group."
  • Appendix C — SQL Quick Reference: subquery syntax at a glance.

Do, don't just read

  • Write the same anti-join three ways (NOT EXISTS, LEFT JOIN … IS NULL, and NOT IN) and deliberately introduce a NULL to watch NOT IN break.
  • Rewrite a correlated subquery (review count per product) as a join-to-aggregate, and compare with EXPLAIN ANALYZE once you reach Chapter 24 (load generate_data.sql first).

Next: Chapter 10 — Set Operations: UNION, INTERSECT, EXCEPT.