📋 SQL Reference · Free · No paywall

SQL Cheat Sheet — every clause, with a runnable example

Most cheat sheets list syntax. Yours forgets it by next Tuesday. This one links every clause to a real challenge — so you don't just read SQL, you run it. SELECT through window functions, all in one page.

Open the App → Jump to the cheat sheet ↓

Print-friendly · Bookmark the section anchors · Cmd+F to search

📑 Table of contents

Query order of ops SELECT & FROM WHERE / ORDER BY / LIMIT Aggregation & GROUP BY JOINs (all 6 types) Subqueries & CTEs Window functions NULL handling CASE WHEN Date & string functions What NOT to do FAQ

Query order of operations

Read first

SQL is written in one order and executed in another. Every "unknown column" error and every "why doesn't my window function work in WHERE" question traces back to this:

Logical execution FROM + JOINs → WHERE → GROUP BY → HAVING → window functions → SELECT (column projection & aliases) → DISTINCT → ORDER BY → LIMIT / OFFSET Aliases declared in SELECT are not visible in WHERE, GROUP BY, or HAVING — they're computed too late. They are visible in ORDER BY. You cannot reference a window function in WHERE either; wrap the query in a CTE.

SELECT & FROM — the basic shape

Beginner
SELECT columns SELECT name, age FROM passengers; Pick which columns to return. SELECT * returns every column — fine for exploration, avoid in production.
SELECT DISTINCT SELECT DISTINCT class FROM passengers; Deduplicate. Operates on the entire row of selected columns, not on individual columns.
Column aliases SELECT name AS passenger_name FROM passengers; Rename a column in the output. AS is optional in most dialects — but it makes the intent clear.
Try it on the Titanic challenge

WHERE / ORDER BY / LIMIT — filtering & shaping output

Beginner
WHERE — basic predicates WHERE age > 30 AND class = '1st' WHERE name LIKE 'Smith%' WHERE age BETWEEN 18 AND 35 WHERE class IN ('1st', '2nd') LIKE uses % for any-string and _ for single char. BETWEEN is inclusive on both sides. IN is shorthand for a chain of OR.
ORDER BY — sort ORDER BY age DESC, name ASC; Multiple columns tie-break in order. ASC is the default. NULLs sort first or last depending on dialect — use NULLS LAST if it matters.
LIMIT & OFFSET LIMIT 10 OFFSET 20; -- Pagination: skip 20, take 10 Always pair LIMIT with ORDER BY if you want deterministic results — without ordering, "top 10" is undefined.
Practice filtering on real data

Aggregation & GROUP BY — counting things

Intermediate
Aggregate functions COUNT(*) -- all rows including NULLs COUNT(col) -- non-NULL values only COUNT(DISTINCT col) SUM(col), AVG(col), MIN(col), MAX(col) All aggregates ignore NULL except COUNT(*). AVG on an all-NULL column returns NULL, not zero.
GROUP BY SELECT class, COUNT(*) AS n FROM passengers GROUP BY class; Every column in SELECT must either be in GROUP BY or wrapped in an aggregate. SQLite is permissive about this; Postgres/MySQL strict mode will reject the query.
HAVING — filter groups GROUP BY class HAVING COUNT(*) > 100; HAVING runs after GROUP BY, so it can reference aggregates. WHERE cannot. Common gotcha: putting an aggregate filter in WHERE → "aggregate functions not allowed."
Conditional aggregation SUM(CASE WHEN survived = 1 THEN 1 ELSE 0 END) AS survivors The single most-asked technique in interview SQL. CASE WHEN inside SUM/COUNT/AVG counts only the rows that match — like an if-statement aggregate.
Drill GROUP BY challenges

JOINs — combining tables

Intermediate

JOINs are where most beginners freeze on the job. Learn all six types and the difference between filtering in WHERE versus ON.

INNER JOIN — only matched rows FROM orders o INNER JOIN customers c ON c.id = o.customer_id; Returns rows where the join condition is true on both sides. Default if you write just JOIN.
LEFT JOIN — keep all left rows FROM customers c LEFT JOIN orders o ON o.customer_id = c.id; Every customer appears, even if they have no orders (their order columns are NULL). Useful for "find X without a matching Y" via WHERE o.id IS NULL — the anti-join pattern.
RIGHT JOIN — keep all right rows FROM orders o RIGHT JOIN customers c ON c.id = o.customer_id; Mirror of LEFT JOIN. Most teams convert RIGHT to LEFT by reversing table order — easier to read.
FULL OUTER JOIN — keep both sides FROM a FULL OUTER JOIN b ON a.id = b.id; Every row from both tables. Unmatched rows have NULLs on the side that didn't match. SQLite emulates this with two LEFT JOINs UNION'd; Postgres supports it natively.
CROSS JOIN — cartesian product FROM sizes CROSS JOIN colors; Every row of A paired with every row of B. Useful for generating combinations, calendar dimension tables, or filling in missing dates.
SELF JOIN — table joined to itself FROM employees e JOIN employees m ON m.id = e.manager_id; Aliases are mandatory. Used for hierarchies (employees + their managers), comparisons within a table (consecutive logins, prev/next), and graph-shaped data.
⚠️ WHERE vs ON in LEFT JOIN -- Filter in ON: keeps unmatched rows LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'paid' -- Filter in WHERE: silently turns LEFT into INNER LEFT JOIN orders o ON o.customer_id = c.id WHERE o.status = 'paid' The single most expensive bug in real-world SQL. WHERE filters out the NULL-padded unmatched rows, defeating the LEFT JOIN. Filter on the right table inside ON, not in WHERE.
Browse 30+ JOIN challenges

Subqueries & CTEs — multi-step queries

Intermediate
Scalar subquery SELECT name, age, age - (SELECT AVG(age) FROM passengers) AS diff_from_avg FROM passengers; Returns one value, used inline. Works in SELECT, WHERE, or HAVING.
IN / EXISTS subquery WHERE id IN (SELECT customer_id FROM orders); WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id); EXISTS is generally faster than IN for large result sets, and safer against NULLs (NOT IN with a NULL in the list returns no rows — common bug).
CTE — Common Table Expression WITH top_customers AS ( SELECT customer_id, SUM(amount) AS total FROM orders GROUP BY customer_id HAVING SUM(amount) > 10000 ) SELECT c.name, t.total FROM top_customers t JOIN customers c ON c.id = t.customer_id; Reads top-to-bottom like a recipe. Prefer over deeply nested subqueries. You can chain multiple CTEs separated by commas.
Recursive CTE WITH RECURSIVE nums(n) AS ( SELECT 1 UNION ALL SELECT n + 1 FROM nums WHERE n < 10 ) SELECT * FROM nums; Walk hierarchies (org charts, comment trees, graph paths) and generate sequences. Anchor + recursive step + termination condition.
Browse CTE challenges

Window functions — the interview differentiator

Advanced

Asked in roughly 80% of mid-to-senior data interviews. Drill them until you can choose between RANK, DENSE_RANK, and ROW_NUMBER without thinking.

Ranking — three flavors ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) RANK() OVER (PARTITION BY dept ORDER BY salary DESC) DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) ROW_NUMBER: unique 1, 2, 3 (ties broken arbitrarily). RANK: ties get same rank, gaps after — 1, 1, 1, 4. DENSE_RANK: ties get same rank, no gaps — 1, 1, 1, 2.
LAG / LEAD — peek at neighbors LAG(price, 1) OVER (ORDER BY day) AS prev_price, LEAD(price, 1) OVER (ORDER BY day) AS next_price Compare current row to previous/next. Foundation of day-over-day deltas, churn detection, sessionization, and consecutive-event detection.
Running totals SUM(amount) OVER ( ORDER BY day ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) Cumulative sum. Drop the ROWS clause and SQL defaults to RANGE which can behave unexpectedly with ties — explicit ROWS is safer.
Rolling averages (last N rows) AVG(price) OVER ( ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW ) AS ma_7d 7-day moving average. The window frame defines which rows feed the aggregate. Combine with PARTITION BY to compute per-group rolling stats.
Top-N per group WITH ranked AS ( SELECT *, ROW_NUMBER() OVER ( PARTITION BY dept ORDER BY salary DESC ) AS rn FROM employees ) SELECT * FROM ranked WHERE rn <= 3; The single most-asked window-function pattern. Wrap in a CTE because you can't filter on window-function results in the same WHERE.
33 window function challenges

NULL handling — the silent bug factory

Intermediate
Test for NULL WHERE col IS NULL WHERE col IS NOT NULL -- WRONG: silently matches nothing -- WHERE col = NULL col = NULL is always unknown, never true. The WHERE clause requires true, so unknown rows are excluded. This is the most common SQL bug — test on the live data.
COALESCE — first non-NULL COALESCE(nickname, first_name, 'Unknown') Returns the first argument that isn't NULL. Use it to provide defaults, fill gaps in left-joined columns, or avoid NULL contagion in math.
NULLIF — opposite of COALESCE NULLIF(divisor, 0) Returns NULL if the two arguments are equal, else the first. Classic use: avoid divide-by-zero by turning 0 into NULL — division by NULL returns NULL, not an error.
⚠️ NOT IN with NULLs -- BUG: returns nothing if subquery has any NULL WHERE id NOT IN (SELECT nullable_col FROM ...) -- SAFE WHERE NOT EXISTS (SELECT 1 FROM t WHERE t.col = id) Subtle: x NOT IN (..., NULL, ...) is logically x ≠ NULL AND ... — and x ≠ NULL is unknown. Whole clause becomes unknown, every row drops.

CASE WHEN — SQL's if-statement

Intermediate
Simple CASE CASE class WHEN '1st' THEN 'Premium' WHEN '2nd' THEN 'Standard' ELSE 'Economy' END AS tier Equality matching against a single column. Concise but limited.
Searched CASE — full predicates CASE WHEN age < 18 THEN 'Minor' WHEN age BETWEEN 18 AND 64 THEN 'Adult' ELSE 'Senior' END More flexible — each WHEN can have any condition. Returns the first matching THEN. ELSE is optional but recommended (otherwise unmatched rows return NULL).
Inside aggregates (the trick) SUM(CASE WHEN survived = 1 THEN 1 ELSE 0 END) AS survivors, SUM(CASE WHEN survived = 0 THEN 1 ELSE 0 END) AS deaths The pivot pattern. Count rows that match a condition by mapping match→1, mismatch→0, then summing. Foundation of every "X by Y, broken out by Z" analytics query.

Date & string functions — the everyday workhorses

Beginner

Syntax varies by dialect. SQLite uses strftime; Postgres uses EXTRACT and DATE_TRUNC; MySQL uses YEAR(), MONTH(). SQL Quest runs SQLite — challenge hints call out dialect differences.

Date parts (SQLite) strftime('%Y', order_date) -- year strftime('%Y-%m', order_date) -- year-month strftime('%w', order_date) -- day of week 0-6 Cast first if your column is stored as text. Postgres equivalent: EXTRACT(YEAR FROM date) or DATE_TRUNC('month', date).
Date math date(order_date, '+7 days') julianday(end) - julianday(start) -- days between SQLite uses modifiers; Postgres uses date + INTERVAL '7 days'; MySQL uses DATE_ADD(). Pattern is the same.
String operations LOWER(name), UPPER(name) LENGTH(name) SUBSTR(name, 1, 3) -- first 3 chars first_name || ' ' || last_name -- concat REPLACE(phone, '-', '') TRIM(name) String concatenation is || in SQLite/Postgres, CONCAT() in MySQL. SUBSTR is 1-indexed.

What NOT to do — anti-patterns

Senior

Things that work but mark you as a beginner in code review. Avoid them once you can.

SELECT * in production -- Don't: SELECT * FROM orders WHERE ... -- Do: SELECT id, customer_id, amount, created_at FROM orders WHERE ... Naming columns explicitly is a contract — if a column is added or removed upstream, your downstream code breaks visibly instead of silently.
Nested subqueries 4 levels deep -- If you're reading inside-out, rewrite as CTEs: WITH step_1 AS (...), step_2 AS (... step_1 ...), step_3 AS (... step_2 ...) SELECT * FROM step_3; CTEs read top-to-bottom. The parser optimizes both forms identically in modern engines, so prefer the readable one.
DISTINCT to "fix" a bad JOIN -- If your COUNT is too high, the fix isn't DISTINCT. -- The fix is finding which JOIN is one-to-many. DISTINCT scans every row to deduplicate — slow on large tables. If your query produces duplicates, the JOIN graph is wrong. Trace the cardinality and fix the join condition.
ORDER BY without LIMIT in subqueries -- The optimizer drops the ORDER BY anyway FROM (SELECT ... FROM t ORDER BY x) sub -- pointless Inner ORDER BYs are not guaranteed to survive the outer query. If you need the order, apply it on the outer query — or use ROW_NUMBER for top-N logic.

Frequently Asked

Reading the cheat sheet is the easy part.
Now write the queries.

200+ challenges, AI Coach by Claude, 7-day Pro trial. First query in under 60 seconds.

Open SQL Quest — Free ⚡

Browser-only · No install · No card