SQL Quick Reference
Joins, aggregation, window functions and the query-shape patterns interviews and dashboards both live on.
Joins
- INNER JOIN
- only rows with a match on both sides
- LEFT JOIN
- all left rows; NULLs where right is missing
- Anti-join
- LEFT JOIN …
WHERE right.id IS NULL— "customers with no orders" - Self join
- table joined to itself (employees → managers)
- Join order of eval
- FROM/JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER → LIMIT
Aggregation
- GROUP BY + HAVING
- HAVING filters groups; WHERE filters rows (before grouping)
- COUNT(DISTINCT x)
- unique values;
COUNT(*)counts rows, NULLs included - Conditional agg
SUM(CASE WHEN status=1 THEN 1 ELSE 0 END)— pivot in one pass- NULL traps
- NULL ≠ NULL; use
IS NULL; aggregates skip NULLs;COALESCEdefaults
Window functions
- ROW_NUMBER()
OVER (PARTITION BY grp ORDER BY x DESC)— top-N per group- RANK vs DENSE_RANK
- RANK skips after ties (1,1,3); DENSE_RANK doesn’t (1,1,2)
- LAG / LEAD
- previous / next row — deltas, retention, sessionization
- Running total
SUM(x) OVER (ORDER BY date)- Moving average
AVG(x) OVER (ORDER BY d ROWS 6 PRECEDING)— 7-day window
Performance instincts
- EXPLAIN ANALYZE
- read the plan before guessing; seq scan on big table = missing index
- Index what you filter
- WHERE/JOIN/ORDER BY columns; composite index order matters
- Sargable predicates
WHERE f(col)=xkills index use — move the function to the constant- EXISTS vs IN
- EXISTS short-circuits; prefer it for "has at least one" checks
- N+1 queries
- the app-side killer — batch with joins or
= ANY(array)