“Given the authors’ reputations, I expected to be impressed. I was blown away! . . . Most SQL books sit on my shelf. This one will live on my desk.”
–Roger Carlson, Microsoft Access MVP (2006-2015)
“Rather than stumble around reinventing wheels or catching glimpses of the proper approaches, do yourself a favor: Buy this book.”
—Dave Stokes, MySQL Community Manager, Oracle Corporation
Effective SQL brings together practical solutions and insights so you can solve complex problems with SQL and design databases that simplify data management in the future. It’s the only modern book that brings together advanced best practices and realistic example code for all of these versions of SQL: IBM DB2, Microsoft Access, Microsoft SQL Server, MySQL, Oracle Database, and PostgreSQL.
Drawing on their immense experience as world-class database consultants and instructors, the authors identify 61 proven approaches to writing better SQL. Wherever SQL versions vary, the authors illuminate the key nuances, so you can get the most out of whatever version you prefer. This full-color guide provides clear, practical explanations; expert tips; and plenty of usable code. Going far beyond mere syntax, it addresses issues ranging from optimizing database designs to managing hierarchies and metadata. If you already know SQL’s basics, this guide will help you become a world-class SQL problem-solver.
- Craft better logical data models, and fix flawed models
- Implement indexes that improve query performance
- Handle external data from sources you don’t control
- Extract and aggregate the information you need, as efficiently as possible
- Write more flexible subqueries
- Analyze and retrieve metadata using your database platform of choice
- Use Cartesian Products and Tally Tables to solve problems you can’t address with conventional JOINs
- Model hierarchical data: managing SQL’s tradeoffs and shortcomings
Table of Contents:
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
