Chapter 6: Further Reading

IBM Official Documentation

DB2 for LUW (Linux, UNIX, Windows)

• SQL Reference: JOIN syntax IBM Knowledge Center — "joined-table" syntax diagrams and rules https://www.ibm.com/docs/en/db2/11.5?topic=queries-joined-tables

• LATERAL correlation in FROM clause IBM Knowledge Center — "lateral-correlation" reference https://www.ibm.com/docs/en/db2/11.5?topic=clause-lateral-correlation

• Table functions in FROM clause IBM Knowledge Center — "TABLE function reference" https://www.ibm.com/docs/en/db2/11.5?topic=functions-table

• EXPLAIN facility and access plan analysis IBM Knowledge Center — "Explaining SQL statements" https://www.ibm.com/docs/en/db2/11.5?topic=optimization-explaining-sql-statements

DB2 for z/OS

• SQL Reference: Joins IBM Knowledge Center — DB2 13 for z/OS SQL reference, joined tables https://www.ibm.com/docs/en/db2-for-zos/13?topic=queries-joined-tables

• LATERAL support in DB2 for z/OS 12+ IBM Knowledge Center — Lateral correlation enhancements https://www.ibm.com/docs/en/db2-for-zos/13?topic=subselect-lateral-correlation

SQL Standard References

• ISO/IEC 9075-2:2023 — SQL Foundation Section 7.7 "joined table" defines the semantics of INNER, OUTER, CROSS, and LATERAL joins in the SQL standard. DB2 conforms closely to this specification.

• C.J. Date, "SQL and Relational Theory," 3rd Edition (O'Reilly, 2015) Chapter 7 covers the formal relational algebra behind SQL joins, including semijoin, antijoin, and division operations. Excellent for understanding the theoretical foundation beneath the syntax.

Books

• Sheryl Larsen and Susan Visser, "DB2 Developer's Guide," 6th Edition (IBM Press, 2012) Chapter on SQL joins provides DB2-specific examples and performance guidance. Covers platform differences between LUW and z/OS.

• Graeme Birchall, "DB2 SQL Cookbook" (free online resource) http://db2-sql-cookbook.org/ Extensive collection of DB2 SQL patterns including self-joins, anti-joins, lateral correlations, and recursive joins. Organized by problem type rather than syntax, making it excellent for practitioners.

• Joe Celko, "SQL for Smarties: Advanced SQL Programming," 5th Edition (Morgan Kaufmann, 2014) Chapters 10-12 cover joins, subqueries, and their interrelationships in depth. Celko's treatment of the anti-join pattern and the difference between WHERE and ON in outer joins is particularly thorough.

• Markus Winand, "SQL Performance Explained" (self-published, 2012) https://use-the-index-luke.com/ Focuses entirely on how databases execute SQL, with detailed coverage of join algorithms (nested loop, hash, merge) and their index requirements. The website companion is freely accessible and regularly updated.

Articles and Technical Papers

• "Join Elimination in DB2" — IBM developerWorks Technical article explaining how DB2's optimizer can remove unnecessary joins when foreign key constraints guarantee match completeness. Relevant to Section 6.10.

• "Lateral Thinking: Using LATERAL Joins in DB2" — IDUG Solutions Journal Practical examples of LATERAL joins for top-N-per-group queries, pivoting, and working with array-valued columns.

• "The Art of SQL" by Stephane Faroult (O'Reilly, 2006) Chapter 5, "A Small Join," covers the practical considerations of writing efficient join queries, including how join order affects performance and when to use subqueries instead.

Performance and Optimization

• IBM Redbook: "DB2 11 for z/OS Technical Overview" (SG24-8180) Chapter on query optimization covers the three join methods in detail with EXPLAIN plan examples.

• IBM Redbook: "DB2 Performance Management for LUW" (SG24-7927) Covers RUNSTATS, EXPLAIN, and access plan analysis with focus on join performance.

• "EXPLAIN Yourself" — A practical guide to reading DB2 access plans IDUG conference presentation (available in IDUG archives). Walks through how to interpret EXPLAIN output for different join types and identify suboptimal plans.

Online Resources

• IDUG (International DB2 Users Group) https://www.idug.org/ Conference proceedings, technical articles, and community forums. The "SQL Tips" section frequently covers join patterns and optimization.

• Stack Overflow — [db2] tag https://stackoverflow.com/questions/tagged/db2 Active community for DB2-specific SQL questions. Search for "db2 outer join" or "db2 lateral" for relevant discussions.

• DB2Night Show — Podcast/Webcast series Regular episodes on DB2 performance topics, including several dedicated to join optimization and EXPLAIN plan analysis.

Related Chapters in This Book