Joins & Aggregation

Joins: the set theory behind combining tables

INNER, LEFT, RIGHT and FULL joins explained with Venn diagrams, foreign keys, and the Cartesian product they're all built from.

Aggregate functions and GROUP BY

COUNT, SUM, AVG, MIN, MAX, and how GROUP BY turns rows into summarized buckets - plus why HAVING exists and WHERE can't do its job.

Subqueries and CTEs - thinking in multiple steps

Nesting queries inside queries, and using WITH clauses to name and reuse intermediate results cleanly.

Window functions - per-row analytics without collapsing rows

RANK, ROW_NUMBER, running totals, and the OVER clause - the advanced tool that GROUP BY structurally cannot do.

Self-joins, EXISTS, and conditional aggregation

Joining a table to itself, the EXISTS/NOT EXISTS pattern, the NOT IN NULL trap, and pivoting rows into columns with CASE.