Chapter 10 — Further Reading

Official reference (everyone)

  • PostgreSQL Docs: "Combining Queries (UNION, INTERSECT, EXCEPT)." The authoritative spec, including the ALL variants, union compatibility, and how ORDER BY/LIMIT apply to the combined result. https://www.postgresql.org/docs/current/queries-union.html
  • PostgreSQL Docs: "SELECT" → the UNION/INTERSECT/EXCEPT clause grammar. Precedence and parenthesization details.

Concepts (🔬 CS Student)

  • Relational algebra set operations (revisit Chapter 4 and Appendix E). UNION/INTERSECT/EXCEPT are direct implementations of ∪/∩/−, with the set-vs-bag wrinkle (ALL).
  • Set vs. bag semantics — why SQL offers both deduplicated and ALL variants, and the performance trade-off of deduplication.

Practical patterns (💻 Developer · 📊 Analyst · 🏗️ DBA)

  • Articles on "UNION vs UNION ALL performance." Reinforce the default-to-ALL rule and the dedup cost.
  • Data-reconciliation with EXCEPT — search for migration/ETL validation guides; the two-direction EXCEPT pattern (Case Study 2) is a standard QA technique.
  • EXCEPT vs NOT EXISTS vs LEFT JOIN … IS NULL — comparisons of the three anti-join spellings (ties back to Chapters 4, 6, 9).

Reference (this book)

  • Appendix C — SQL Quick Reference: set-operation syntax.
  • Appendix I — SQL Cookbook: reconciliation and "in both / in one not the other" recipes.
  • Appendix E — Relational Algebra Reference: the algebra these operators implement.

Do, don't just read

  • Run the Case Study 1 collision yourself: create two tiny tables with an identical row in each, then compare UNION vs UNION ALL counts. Watch UNION lose the "duplicate."
  • Reconcile two tables with EXCEPT both ways. Insert a deliberate discrepancy and confirm exactly one EXCEPT surfaces it.
  • Rewrite an EXCEPT anti-join as NOT EXISTS and note which reads better for your case.

Next: Chapter 11 — CTEs and Recursive Queries: readable, reusable SQL — and recursion.