Cheat sheet · No. VI
SQL.
SQL is set logic, not loops. Joins combine rows, window functions compute across a frame without collapsing it, and CTEs let you name a query so the next one reads cleanly.
The reference
JOINS
INNER JOIN- Rows in both
LEFT JOIN- All from left, matches from right
FULL OUTER JOIN- All from both
CROSS JOIN- Cartesian product
LATERAL- Right side may reference left's row
WINDOW FUNCTIONS
ROW_NUMBER() OVER (ORDER BY x)- Sequential per partition
RANK() OVER (...)- Rank with gaps
DENSE_RANK()- Rank without gaps
LAG(col, 1) OVER (...)- Previous row's value
LEAD(col, 1) OVER (...)- Next row's value
SUM(x) OVER (PARTITION BY g ORDER BY t)- Running total per group
CTE & RECURSION
WITH RECURSIVE tree AS ( SELECT id, parent, 0 AS depth FROM nodes WHERE parent IS NULL UNION ALL SELECT n.id, n.parent, t.depth+1 FROM nodes n JOIN tree t ON n.parent = t.id ) SELECT * FROM tree;
SETS
UNION- Combine, dedup
UNION ALL- Combine, keep dups (faster)
INTERSECT- Rows in both
EXCEPT- In first, not in second
Field notes
The LEFT JOIN trap
Filtering the right-hand table in WHERE silently turns a LEFT JOIN into an INNER JOIN. Put that condition in the ON clause instead.
Windows keep every row
ROW_NUMBER, RANK and SUM OVER add a computed column without reducing rows — exactly what "top N per group" needs.
UNION vs UNION ALL
UNION removes duplicates (and sorts to do it); UNION ALL keeps them and is much faster. Use ALL when you know rows are distinct.
Recursion walks trees
A recursive CTE is a base case UNION ALL a step that references itself — perfect for hierarchies and graphs. Always bound the depth.