Chapter 8: Further Reading

IBM Documentation

  • DB2 SQL Reference — Subqueries IBM Knowledge Center: "Subqueries" section in the SQL Reference for your DB2 version. Covers scalar subqueries, quantified predicates (ANY, ALL, SOME), and the formal rules for subquery placement. https://www.ibm.com/docs/en/db2

  • DB2 SQL Reference — Common Table Expressions IBM Knowledge Center: "common-table-expression" in the SQL Reference. Includes syntax diagrams, recursive CTE rules, and column naming conventions.

  • DB2 SQL Reference — Fullselect The formal grammar for how subqueries compose into fullselects, subselects, and table references. Dense but authoritative.

  • DB2 EXPLAIN Facility IBM Knowledge Center: "Explain facility" and "db2exfmt" tool. Essential for understanding how DB2 executes subqueries, whether CTEs are materialized or inlined, and where semi-join or anti-join operators appear.

Books

  • Janmohamed, Zamil, et al. "DB2 SQL PL: Essential Guide for DB2 UDB on Linux, UNIX, Windows, i5/OS, and z/OS." IBM Press. Covers CTEs (including recursive CTEs) in the context of DB2 SQL PL programming. Good for understanding how subqueries interact with procedural logic.

  • Mullins, Craig S. "DB2 Developer's Guide." IBM Press. Comprehensive DB2 reference with chapters on query optimization that explain how the optimizer handles subqueries, semi-joins, and CTE materialization decisions.

  • Celko, Joe. "SQL for Smarties: Advanced SQL Programming." Morgan Kaufmann. Language-agnostic but deeply relevant. Celko's treatment of subqueries, EXISTS vs. IN, and set-based thinking is among the best in print. Chapters on hierarchical data are particularly relevant to recursive CTEs.

  • Date, C.J. "SQL and Relational Theory." O'Reilly Media. For the theoretically minded reader. Date explains the relational algebra behind subqueries, semi-joins, and anti-joins. Helps you understand why SQL works the way it does, not just how.

  • Molinaro, Anthony. "SQL Cookbook." O'Reilly Media. Practical recipe-based guide with chapters on subqueries, hierarchical queries, and advanced SELECT techniques. Includes DB2-specific notes alongside Oracle, SQL Server, and PostgreSQL variants.

Articles and Papers

  • "Unnesting Arbitrary Queries" by Thomas Neumann and Alfons Kemper. Academic paper on how modern query optimizers decorrelate (unnest) correlated subqueries. Reading this gives you insight into when DB2 can transform a correlated subquery into a join and when it cannot.

  • "Outerjoin Simplification and Reordering for Query Optimization" by Cesar Galindo-Legaria and Arnon Rosenthal. Foundational paper on join reordering and subquery transformation in cost-based optimizers. The techniques described here are implemented in DB2's optimizer.

Online Resources

  • DB2 for LUW Performance Tuning Wiki Community-maintained resource with practical tips on CTE materialization behavior, subquery optimization patterns, and EXPLAIN plan interpretation.

  • Stack Overflow — [db2] Tag Active community for DB2 questions. Search for "CTE materialization db2" or "correlated subquery performance db2" for real-world problem discussions.

  • IBM Data Management Magazine (archived) Historical articles on DB2 SQL features, including deep dives on CTE behavior across DB2 versions.

  • Chapter 5: SELECT and WHERE — Foundation for all subquery placement.
  • Chapter 6: Joins — Semi-joins and anti-joins are the execution model for EXISTS and NOT EXISTS.
  • Chapter 7: Aggregation and GROUP BY — CTEs frequently contain aggregations; understanding GROUP BY is prerequisite.
  • Chapter 9: INSERT, UPDATE, DELETE, and MERGE — Subqueries and CTEs in data modification statements.
  • Chapter 10: Window Functions — ROW_NUMBER and other window functions often replace correlated subqueries for "top-N per group" patterns.
  • Chapter 14: Query Optimization and EXPLAIN — Deep dive into how DB2 executes subqueries, CTE materialization decisions, and performance tuning.