SQL Quick Reference

Joins, aggregation, window functions and the query-shape patterns interviews and dashboards both live on.

SQL is declarative: you describe the rows you want and the planner decides how to fetch them. Two ideas make the rows below click. A query is evaluated in a fixed logical order (FROM and JOIN, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT) whatever order you write it in. And NULL means unknown, so any comparison with it is unknown too, which is where most surprising results come from.

Syntax is standard SQL with PostgreSQL where dialects differ. MySQL, SQLite and SQL Server mostly agree, and the differences are marked.

Query shape

Order of evaluation: FROM/JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT — why a SELECT alias fails in WHERE
SELECT DISTINCT: dedupe whole result rows; Postgres DISTINCT ON (k) keeps the first row per key by ORDER BY
CASE WHEN … END: if/else inside an expression; the first true branch wins, and no ELSE means NULL
NULLS LAST: Postgres puts NULLs first under DESC; ORDER BY x DESC NULLS LAST moves them
LIMIT 10 OFFSET 20: the third page of 10; without ORDER BY the rows returned are arbitrary
IN (…) · BETWEEN: membership · a range that includes both ends: BETWEEN 1 AND 10
LIKE · ILIKE: % any run, _ one char; ILIKE ignores case (Postgres); a leading % defeats a B-tree index

Joins

INNER JOIN: only rows with a match on both sides
LEFT JOIN: all left rows, NULLs where the right is missing; a WHERE on right columns undoes it — filter in ON
Anti-join: LEFT JOIN … WHERE right.id IS NULL — "customers with no orders"
Self join: table joined to itself (employees → managers)
FULL OUTER JOIN: every row from both sides, NULLs where either is missing — reconciling two sources
CROSS JOIN: every pairing of both tables (rows × rows) — calendars and combinations; easy to blow up
JOIN … USING (id): shorthand when both columns share a name; the output keeps a single id
JOIN LATERAL: a subquery that sees the outer row: JOIN LATERAL (… LIMIT 3) ON true = top-N per row
Join fan-out: joining two one-to-many tables multiplies rows; aggregate each side before joining

Aggregation

GROUP BY + HAVING: HAVING filters groups; WHERE filters rows (before grouping)
COUNT(DISTINCT x): unique values; COUNT(*) counts rows, NULLs included
COUNT(col): non-NULL values only, so it differs from COUNT(*) when the column has NULLs
Conditional agg: SUM(CASE WHEN status=1 THEN 1 ELSE 0 END) — pivot in one pass
FILTER (WHERE …): cleaner conditional aggregate: COUNT(*) FILTER (WHERE paid) (Postgres, SQLite)
STRING_AGG(x, ', '): join a group’s values into one string; MySQL calls it GROUP_CONCAT
GROUP BY ROLLUP (a): per-group rows plus a grand total in one query; NULL in a marks the total row
NULL traps: NULL ≠ NULL; use IS NULL; aggregates skip NULLs; COALESCE defaults
5 / 2 = 2: integer division truncates (Postgres, SQLite, SQL Server); cast first: 5::numeric / 2

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 d); tied d values share one total unless you add ROWS UNBOUNDED PRECEDING
Moving average: AVG(x) OVER (ORDER BY d ROWS 6 PRECEDING) — 7-day window
NTILE(4): split ordered rows into 4 near-equal buckets — quartiles
FIRST_VALUE · LAST_VALUE: LAST_VALUE returns the current row unless the frame runs to UNBOUNDED FOLLOWING
SUM(x) OVER (): grand total on every row — share of total = x / SUM(x) OVER ()
Filter on a window: window results can’t appear in WHERE; compute them in a CTE and filter outside

CTEs & subqueries

WITH x AS (…): name a subquery and read top-down; Postgres 12+ inlines it unless marked MATERIALIZED
WITH RECURSIVE: hierarchies and series: an anchor query UNION ALL a step that references itself
NOT IN with NULLs: one NULL from the subquery makes NOT IN match nothing — use NOT EXISTS
Correlated subquery: runs once per outer row; often rewritable as a JOIN or LATERAL and much faster
UNION vs UNION ALL: UNION removes duplicates (a sort or hash); UNION ALL just appends — faster

Changing data

INSERT … RETURNING id: get generated values back in the same statement (Postgres; SQLite 3.35+)
ON CONFLICT … DO UPDATE: upsert: SET v = EXCLUDED.v takes the new value; needs a unique index on the key
UPDATE … FROM: update using a join (Postgres); forget the join condition and every row is updated
DELETE … USING: delete with a join condition (Postgres); RETURNING * shows what went
BEGIN · COMMIT · ROLLBACK: wrap risky changes and check the row count before committing
TRUNCATE: empty a table at once — no per-row deletes, no row triggers fired

NULLs, types & dates

COALESCE(a, b, 0): the first non-NULL argument — defaults for missing values
NULLIF(x, 0): NULL when x = 0, so a / NULLIF(b, 0) returns NULL instead of erroring
x::int · CAST(x AS int): type conversion; :: is Postgres shorthand, CAST is standard SQL
date_trunc('day', ts): bucket timestamps by day, week or month for time series (Postgres)
interval '7 days': date arithmetic: ts >= now() - interval '7 days'
IS DISTINCT FROM: NULL-safe comparison: true when values differ, with two NULLs counted as equal

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
Leftmost prefix: an index on (a, b) serves filters on a or on a and b, not b alone
Sargable predicates: WHERE f(col)=x kills 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)
Keyset pagination: WHERE id > :last with ORDER BY id LIMIT 50; OFFSET re-reads every skipped row
Partial index: an index with WHERE status = 'open': small and fast, covering only the rows you query
SELECT *: fetches unneeded columns and blocks index-only scans; name the columns

Recipes

Top 3 products by revenue in each category

WITH ranked AS (
  SELECT category, product, SUM(amount) AS revenue,
         ROW_NUMBER() OVER (PARTITION BY category ORDER BY SUM(amount) DESC) AS rn
  FROM sales
  GROUP BY category, product
)
SELECT category, product, revenue
FROM ranked
WHERE rn <= 3
ORDER BY category, rn;

Window functions run after GROUP BY, so they can rank aggregates; the CTE exists because a window result cannot be filtered in the same query’s WHERE. Use RANK() to keep ties.

Month-over-month revenue growth

WITH monthly AS (
  SELECT date_trunc('month', created_at) AS month, SUM(amount) AS revenue
  FROM orders
  GROUP BY 1
)
SELECT month, revenue,
       ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
             / NULLIF(LAG(revenue) OVER (ORDER BY month), 0), 1) AS growth_pct
FROM monthly
ORDER BY month;

LAG reads the previous month’s row and NULLIF turns a zero baseline into NULL instead of an error; the first month’s growth is NULL by design.

Find duplicate rows and keep one of each

SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) > 1;

DELETE FROM users u
USING users d
WHERE u.email = d.email AND u.id > d.id;

The first query lists each duplicated email; the delete keeps the lowest id per email (Postgres syntax). Run it inside a transaction and check the count before committing.

Customers who never ordered, two safe ways

SELECT c.* FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

SELECT * FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

Both handle NULLs correctly and usually get the same plan. Avoid NOT IN (SELECT customer_id …): a single NULL customer_id makes it return no rows at all.

Upsert a daily counter

INSERT INTO page_views (page, day, views)
VALUES ('/pricing', CURRENT_DATE, 1)
ON CONFLICT (page, day) DO UPDATE
SET views = page_views.views + EXCLUDED.views;

This needs a unique constraint on (page, day). Insert-or-increment happens atomically, so concurrent requests never lose a count.

Questions about SQL Cheat Sheet

What is the difference between WHERE and HAVING in SQL?

WHERE filters individual rows before they are grouped, so it can only use columns from the tables in the FROM clause. HAVING filters whole groups after GROUP BY has run, so it can use aggregates such as COUNT or SUM. Total sales per customer with HAVING SUM(amount) over 1000 keeps only big customers, while WHERE amount over 1000 would keep only big individual orders before summing. Put every condition that does not need an aggregate in WHERE, because filtering early leaves less work for the grouping step.

What is the difference between INNER JOIN and LEFT JOIN?

An INNER JOIN returns only rows that have a match on both sides of the join condition. A LEFT JOIN returns every row from the left table and fills the right-hand columns with NULL where no match exists, which is how you find customers with no orders or report zero counts. A common mistake is a WHERE condition on a right-table column after a LEFT JOIN: rows without a match have NULL there, fail the condition and disappear, silently turning it back into an inner join. Put such conditions in the ON clause instead.

What is the difference between RANK, DENSE_RANK and ROW_NUMBER?

All three number rows within a window in ORDER BY order; they differ only on ties. ROW_NUMBER gives every row a unique number and breaks ties arbitrarily unless the ORDER BY makes rows unique. RANK gives tied rows the same number and then skips, producing 1, 1, 3. DENSE_RANK gives tied rows the same number without gaps, producing 1, 1, 2. Use ROW_NUMBER for exactly N rows per group, as in deduplication, RANK when ties should share a place, and DENSE_RANK for questions such as the second-highest salary.

Why does NOT IN return no rows when the list contains a NULL?

Because x NOT IN (a, b, NULL) means x differs from a, and x differs from b, and x differs from NULL, and a comparison with NULL is unknown rather than true. A chain of ANDs that contains an unknown can never be true, so no row passes the filter. This bites most often with NOT IN followed by a subquery over a nullable column. Use NOT EXISTS with a correlated subquery, or a LEFT JOIN with an IS NULL check; both behave as intended when NULLs are present and usually perform at least as well.

Go deeper

The SQL handbook →PostgreSQL vs MySQL — coming soon →