SQL Deep Dive

What you'll learn

• Predict what a query returns by reasoning about SQL's logical evaluation order and NULL behaviour.
• Choose the right join, including semi-joins and anti-joins, and avoid fan-out double counting.
• Summarise data with GROUP BY, HAVING, FILTER, ROLLUP and conditional-aggregation pivots.
• Use window functions for ranking, top-N per group, running totals, moving averages and shares.
• Solve analytical patterns: recursive hierarchies, LATERAL top-N, gap filling, gaps-and-islands and deduplication.
• Change data safely with MERGE, UPDATE ... FROM and transactions, and write index-friendly queries checked with EXPLAIN.

Syllabus

How SQL Thinks

1. Thinking in Sets
2. Logical Order of Evaluation
3. NULL and Three-Valued Logic

Joins Without Surprises

1. Inner and Outer Joins
2. Semi-Joins and Anti-Joins
3. Fan-Out and Double Counting

Aggregation and Reporting

1. GROUP BY, HAVING and FILTER
2. Subtotals With ROLLUP and GROUPING SETS
3. Pivoting With Conditional Aggregation

Window Functions

1. Ranking and Top-N per Group
2. Running Totals, Moving Averages and LAG
3. Shares, Comparisons and Bands

CTEs, LATERAL and Set Operations

1. CTEs and Recursive Queries
2. LATERAL Joins
3. UNION, INTERSECT and EXCEPT

Analytical Patterns

1. Filling Gaps in Time Series
2. Gaps and Islands
3. Finding and Removing Duplicates

Changing Data Safely

1. MERGE and Upserts
2. UPDATE ... FROM and DELETE ... USING
3. A Safe Workflow for Data Changes

Writing Fast Queries

1. Index-Friendly (Sargable) Predicates
2. Composite Index Column Order
3. Reading EXPLAIN ANALYZE
4. An SQL Review Checklist