Joins & aggregation
22 steps in 5 sets of SQL.
Where SQL starts being worth learning properly. Aggregation first, then GROUP BY and HAVING, then the joins themselves: inner, left, cross, self, and the anti-join pattern that catches people out.
Set operations close it. Twenty-two steps, and the mental model to build is that a join produces rows and everything else filters or folds them.
Aggregation
- Aggregate functionsCOUNT, SUM, AVG, MIN and MAX collapse many rows into one summary row.
- Counting unique valuesCOUNT DISTINCT answers how many different values a column holds.
- What COUNT countsCOUNT(*) counts rows; COUNT(column) counts values. NULLs decide the difference.
- The row behind the maximumMAX gives you the value. Getting the row it came from is a different question.
Grouping
- GROUP BYGROUP BY splits rows into buckets and aggregates each bucket.
- Grouping on several columnsGroup by two columns and every distinct pair becomes a row.
- HAVING filters groupsWHERE filters rows before grouping; HAVING filters the groups after.
- Collapsing a group into a stringSometimes the answer is not a number but the list itself.
Joins
- INNER JOINAn inner join keeps only the rows that match on both sides.
- LEFT JOINA left join keeps every left row, filling the right side with NULL when nothing matches.
- USING and column namesWhen both sides name the column the same thing, USING says so once.
- RIGHT and FULL OUTER JOINLEFT keeps the left side's rows. RIGHT and FULL keep the others.
- CROSS JOINEvery row on the left paired with every row on the right — on purpose, this time.
- Self joinA table can join itself when a row points at another row of the same kind.
- Joining three tablesJoins chain: each new table joins whatever has been assembled so far.
- Finding rows with no matchThe anti join: an outer join, then keep only the rows that failed to match.
- Aggregating across a joinGroup after joining to summarize the child rows per parent.
Set operations
- UNION and UNION ALLUNION stacks two result sets that share a column shape.
- Sorting a UNIONA UNION's ORDER BY belongs to the whole result, not to the last branch.
- EXCEPT and INTERSECTSet difference and set overlap, straight from relational algebra.
Encore
- sales_report.sqlA monthly sales report built from chained CTEs, one step per stage.
- funnel_report.sqlCounting how many sessions made it to each step, then the drop between them.