Cheatsheet
SQL Cheatsheet
Every SQL query you type in real data work. Basic SELECT through window functions and CTEs.
SQL syntax is stable across decades; the trick is remembering the patterns for aggregations, joins, and window functions. This cheatsheet covers standard SQL used across PostgreSQL, MySQL, SQLite, and SQL Server (with dialect notes where they differ).
SELECT basics
`SELECT col1, col2 FROM table WHERE cond ORDER BY col LIMIT n`.
`SELECT DISTINCT col FROM table` — deduplicate.
`SELECT col AS alias FROM table t` — column and table aliases.
JOINs
The five most-used join types.
- INNER JOIN — rows matching in both
- LEFT JOIN — all from left, matched from right (NULL if unmatched)
- RIGHT JOIN — mirror of LEFT
- FULL OUTER JOIN — all from both (NULL where no match)
- CROSS JOIN — Cartesian product; almost never intentional
Aggregation and GROUP BY
`SELECT dept, COUNT(*), AVG(salary) FROM emp GROUP BY dept`.
`HAVING` filters after aggregation (unlike `WHERE` which filters before).
Common aggregations: `COUNT`, `SUM`, `AVG`, `MIN`, `MAX`, `STRING_AGG` (Postgres) or `GROUP_CONCAT` (MySQL).
Window functions
Aggregate without collapsing rows.
`SUM(x) OVER (PARTITION BY dept ORDER BY date)` — running total per department.
Ranking: `ROW_NUMBER()`, `RANK()`, `DENSE_RANK()`, `NTILE(4)`.
Offset: `LAG(col, 1)`, `LEAD(col, 1)`.
CTEs and subqueries
`WITH sales AS (SELECT ...) SELECT * FROM sales WHERE ...`.
Recursive: `WITH RECURSIVE tree AS (base UNION ALL recursive step)`.
Frequently asked questions
INNER JOIN or WHERE with two tables?
INNER JOIN is clearer and standard. WHERE-join syntax works but is older style.
When to use CTE vs subquery?
CTEs for readability with multiple references or recursion. Subqueries for one-off inline filters.
PARTITION BY vs GROUP BY?
GROUP BY collapses rows. PARTITION BY (in a window function) computes per-group aggregates without collapsing.
How do I paginate?
`ORDER BY id LIMIT 20 OFFSET 40` (Postgres/MySQL). For large tables, use keyset pagination: `WHERE id > last_seen_id ORDER BY id LIMIT 20`.
Should I EXPLAIN queries?
Yes. `EXPLAIN ANALYZE` (Postgres) or `EXPLAIN` (MySQL) shows the query plan. Essential for optimization.
Keep exploring
Made for exam season
Pass that exam.
Turn your notes into flashcards and quizzes in seconds. Study smarter — start free today.