Effective SQL

Foreword xiii

Acknowledgments xv

About the Authors xvii

About the Technical Editors xix





Introduction 1

A Brief History of SQL 1

Database Systems We Considered 5

Sample Databases 6

Where to Find the Samples on GitHub 7

Summary of the Chapters 8



Chapter 1: Data Model Design 11

Item 1: Verify That All Tables Have a Primary Key 11

Item 2: Eliminate Redundant Storage of Data Items 15

Item 3: Get Rid of Repeating Groups 19

Item 4: Store Only One Property per Column 21

Item 5: Understand Why Storing Calculated Data Is Usually a Bad Idea 25

Item 6: Define Foreign Keys to Protect Referential Integrity 30

Item 7: Be Sure Your Table Relationships Make Sense 33

Item 8: When 3NF Is Not Enough, Normalize More 37

Item 9: Use Denormalization for Information Warehouses 43



Chapter 2: Programmability and Index Design 47

Item 10: Factor in Nulls When Creating Indexes 47

Item 11: Carefully Consider Creation of Indexes to Minimize Index and Data Scanning 52

Item 12: Use Indexes for More than Just Filtering 56

Item 13: Don't Go Overboard with Triggers 61

Item 14: Consider Using a Filtered Index to Include or Exclude a Subset of Data 65

Item 15: Use Declarative Constraints Instead of Programming Checks 68

Item 16: Know Which SQL Dialect Your Product Uses and Write Accordingly 70

Item 17: Know When to Use Calculated Results in Indexes 74



Chapter 3: When You Can't Change the Design 79

Item 18: Use Views to Simplify What Cannot Be Changed 79

Item 19: Use ETL to Turn Nonrelational Data into Information 85

Item 20: Create Summary Tables and Maintain Them 90

Item 21: Use UNION Statements to "Unpivot" Non-normalized Data 94



Chapter 4: Filtering and Finding Data 101

Item 22: Understand Relational Algebra and How It Is Implemented in SQL 101

Item 23: Find Non-matches or Missing Records 108

Item 24: Know When to Use CASE to Solve a Problem 110

Item 25: Know Techniques to Solve Multiple-Criteria Problems 115

Item 26: Divide Your Data If You Need a Perfect Match 120

Item 27: Know How to Correctly Filter a Range of Dates on a Column Containing Both Date and Time 124

Item 28: Write Sargable Queries to Ensure That the Engine Will Use Indexes 127

Item 29: Correctly Filter the "Right" Side of a "Left" Join 132



Chapter 5: Aggregation 135

Item 30: Understand How GROUP BY Works 135

Item 31: Keep the GROUP BY Clause Small 142

Item 32: Leverage GROUP BY/HAVING to Solve Complex Problems 145

Item 33: Find Maximum or Minimum Values Without Using GROUP BY 150

Item 34: Avoid Getting an Erroneous COUNT() When Using OUTER JOIN 156

Item 35: Include Zero-Value Rows When Testing for HAVING COUNT(x) < Some Number 159

Item 36: Use DISTINCT to Get Distinct Counts 163

Item 37: Know How to Use Window Functions 166

Item 38: Create Row Numbers and Rank a Row over Other Rows 169

Item 39: Create a Moving Aggregate 172



Chapter 6: Subqueries 179

Item 40: Know Where You Can Use Subqueries 179

Item 41: Know the Difference between Correlated and Non-correlated Subqueries 184

Item 42: If Possible, Use Common Table Expressions Instead of Subqueries 190

Item 43: Create More Efficient Queries Using Joins Rather than Subqueries 197



Chapter 7: Getting and Analyzing Metadata 201

Item 44: Learn to Use Your System's Query Analyzer 201

Item 45: Learn to Get Metadata about Your Database 212

Item 46: Understand How the Execution Plan Works 217



Chapter 8: Cartesian Products 227

Item 47: Produce Combinations of Rows between Two Tables and Flag Rows in the Second That Indirectly Relate to the First 227

Item 48: Understand How to Rank Rows by Equal Quantiles 231

Item 49: Know How to Pair Rows in a Table with All Other Rows 235

Item 50: Understand How to List Categories and the Count of First, Second, or Third Preferences 240



Chapter 9: Tally Tables 247

Item 51: Use a Tally Table to Generate Null Rows Based on a Parameter 247

Item 52: Use a Tally Table and Window Functions for Sequencing 252

Item 53: Generate Multiple Rows Based on Range Values in a Tally Table 257

Item 54: Convert a Value in One Table Based on a Range of Values in a Tally Table 261

Item 55: Use a Date Table to Simplify Date Calculation 268

Item 56: Create an Appointment Calendar Table with All Dates Enumerated in a Range 275

Item 57: Pivot Data Using a Tally Table 278



Chapter 10: Modeling Hierarchical Data 285

Item 58: Use an Adjacency List Model as the Starting Point 286

Item 59: Use Nested Sets for Fast Querying Performance with Infrequent Updates 288

Item 60: Use a Materialized Path for Simple Setup and Limited Searching 291

Item 61: Use Ancestry Traversal Closure for Complex Searching 294



Appendix: Date and Time Types, Operations, and Functions 299

IBM DB2 299

Microsoft Access 303

Microsoft SQL Server 305

MySQL 308

Oracle 313

PostgreSQL 315



Index 317