What separates competent from capable.
WHAT MOST PEOPLE KNOW
Selecting, filtering, sorting, and simple joins.
WHAT DISTINGUISHES CAPABILITY
Aggregation with grouping and filtering on aggregates Window functions Common table expressions Set operations Subqueries, and when to avoid them Understanding how the query is actually executed
WHAT AN EXECUTION ORDER MISUNDERSTANDING CAUSES
Attempting to filter on an alias defined later, or on an aggregate in the wrong clause.
WHAT THE ACTUAL ORDER IS
Sources are joined Rows are filtered Rows are grouped Groups are filtered Expressions are computed Results are sorted Results are limited
WHY THAT MATTERS
It explains most confusing errors, and why filtering before grouping is faster than after.
WHAT TO FILTER EARLY
Everything possible.
WHY
Less data flows through every subsequent step.
WHAT TO AVOID
Selecting every column when few are needed Functions applied to a column in a filter, which prevents index use Correlated subqueries where a join would serve
WHAT TO LEARN TO READ
The query plan.
WHY
It is the only reliable way to know what the database actually does.
