SQL

Runnable SQL on PostgreSQL 17: step-through animations of clause order, NULLs, joins, windows and isolation, real-output workflows for query life, replication and migrations, and 16 checked problems from top-N to query plans.

Overview

This page is a quick reference you can run. The tables hold the rules, the step-through labs show what PostgreSQL does with each row, and the green labs run real PostgreSQL 17 in your browser with a Check button. Every sql block on the page runs on PostgreSQL 17 against the sample tables below; MySQL and SQL Server versions sit in their own tabs, marked with their dialect.

Most SQL bugs do not raise an error. The query runs and the number is wrong. Three rules explain nearly all of them.

NULL means unknown — x = NULL and NOT IN with a NULL silently drop rows — NULL and three-valued logic
Clauses run in a fixed logical order, not the written order — aliases missing in WHERE, a LEFT JOIN turned into an inner join — Clause order
A join emits one row per matching pair — COUNT and SUM inflate after a one-to-many join — Joins

Step-through animations (computed from your input, checked against PostgreSQL) — 15
Workflow diagrams with real commands and outputs — 4
Problems with Check and a worked solution — 16
Try-it labs — 4
Syntax cards — 26

The sample tables

The four tables below are tiny on purpose. Fay has no department, Legal has no staff, Ben and Cai tie on salary, and order 6 has no employee. Those gaps are what make the traps show up.

departments — 3 — Legal has no employees
employees — 7 — Fay has no dept_id; Ana has no manager; Ben and Cai both earn 120000; Dee earns more than her manager Dan
orders — 6 — order 6 has no employee_id
logins — 6 — user 1 logs in three days in a row, then skips a day

The PostgreSQL labs use three larger datasets of their own; the table chips above each editor list their columns.

hr — departments, employees (9 people, a manager tree, a NULL department, tied salaries) — ranking, recursive CTEs, NULL rules
shop — customers, products, orders, order_items, events, app_activity, daily_revenue, customer_imports, inventory — joins, analytics, time series, upserts
traffic — page_views: 50,000 generated rows — real query plans, indexes, pagination

Clause order

You write SELECT first, but it runs after FROM, WHERE, GROUP BY and HAVING. Step through it below: each clause sees only the rows the one before it kept. Change the inputs and the trace recomputes.

The PostgreSQL manual lists these logical steps. The planner may reorder the physical work (push a filter into a join, use an index to avoid a sort), but the result must match this order.

Can WHERE use a SELECT alias? — no — WHERE runs before SELECT creates it
Can ORDER BY use a SELECT alias? — yes — ORDER BY runs after SELECT
Can WHERE use COUNT(*)? — no — groups do not exist yet; use HAVING
Can WHERE filter on ROW_NUMBER()? — no — window functions run with SELECT, after WHERE; filter in an outer query
Why add a tiebreaker before LIMIT? — rows that tie come back in any order — LIMIT just cuts whatever order ORDER BY gave

NULL and three-valued logic

NULL means unknown, not zero or empty. Any comparison with it gives UNKNOWN (shown as NULL), and WHERE, ON and HAVING keep a row only when the condition is TRUE. The trace evaluates nine predicates on every row; type your own values, NULL included.

NULL = NULL — NULL (unknown) — no
NULL <> 1 — NULL — no
TRUE AND NULL — NULL — no
FALSE AND NULL — FALSE — no
TRUE OR NULL — TRUE — yes
NULL IS NULL — TRUE — yes
NULL IS DISTINCT FROM 1 — TRUE — yes
NOT NULL — NULL — no

COUNT(*) — counts every row
COUNT(col), SUM, AVG, MIN, MAX — skip NULLs; AVG divides by the non-null count
x NOT IN (subquery) — one NULL in the subquery makes it return no rows
NOT EXISTS (subquery) — unaffected by NULLs
GROUP BY, DISTINCT — all NULLs form one group
UNIQUE constraint — PostgreSQL allows many NULLs unless NULLS NOT DISTINCT; SQL Server allows one
CHECK (salary > 0) — passes when the value is NULL; add NOT NULL
ORDER BY in PostgreSQL — NULLs sort as larger than any value: last for ASC, first for DESC

Try the rules on real PostgreSQL. The last query is the classic trap: Legal has no staff, yet it returns nothing.

Joins

A join emits one row for every pair of rows whose ON condition is true. Watch each join kind walk the same two tables, then switch to NOT IN to see one NULL empty the result.

INNER JOIN — matching pairs only — 6: Fay drops out
LEFT JOIN — every employee; department columns NULL when unmatched — 7
RIGHT JOIN — every department — 7: Legal appears, Fay drops
FULL OUTER JOIN — every row of both — 8
CROSS JOIN — every pair, no condition — 21 (7 x 3)
semi join (EXISTS) — left rows with at least one match, once each — 6
anti join (NOT EXISTS) — left rows with no match — 1 (Fay)

A condition on the right table of a LEFT JOIN goes in ON, because in WHERE it throws away the NULL rows the join just kept. And joining a one-to-many table before COUNT or SUM repeats each parent row once per child, so aggregate the child table first. Both mistakes run without error and return wrong numbers.

Practice: an anti-join with a guest order in the data, a report whose counts are inflated by a fan-out, and an org chart walked with a recursive CTE.

Which tool for which question

one row per group (totals, counts) — GROUP BY + aggregates — 06
every row plus a group value (rank, running total, share) — window function — 11, 12
top N per group — ROW_NUMBER / DENSE_RANK in a subquery, or LATERAL ... LIMIT — 09, 11, Top 2 per group
keep one row per key (latest wins) — DISTINCT ON or ROW_NUMBER() ... = 1 — Dedupe latest
compare with the previous or next row — LAG / LEAD — 12
consecutive runs, streaks — gaps and islands — 13, Gaps & islands
group events by idle time — LAG + running SUM of a new-session flag — Sessions
rows with / without a match — EXISTS / NOT EXISTS — 08, Anti-join
several steps, or a hierarchy — CTE, WITH RECURSIVE — 10, Recursive CTE
turn rows into columns — FILTER or CASE inside aggregates — 06, 15, Funnel
median, p90 — percentile_cont / percentile_disc — Median
insert or update — INSERT ... ON CONFLICT — 19, Upsert

Window functions

A window function computes over related rows but, unlike GROUP BY, keeps every row. The parts are fn() OVER (PARTITION BY ... ORDER BY ... frame). The frame is the part people get wrong: step through a running sum where two rows share a day.

ROW_NUMBER() — 1, 2, 3, 4 — exactly one row per group, deduplication
RANK() — 1, 2, 2, 4 — competition ranking, ties included, gaps after
DENSE_RANK() — 1, 2, 2, 3 — the Nth distinct value
NTILE(n) — buckets 1..n — quartiles, deciles
LAG(x) / LEAD(x) — previous / next row's x — day-over-day change
FIRST_VALUE / LAST_VALUE — first / last row in the frame — needs a full frame for LAST_VALUE
SUM, AVG, COUNT ... OVER — running or group values — totals without collapsing rows

none, no ORDER BY — the whole partition
none, with ORDER BY — RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: start of partition to the current row and all its ties
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW — the last three rows, counted as rows
RANGE BETWEEN INTERVAL '2 days' PRECEDING AND CURRENT ROW — rows whose sort value is within 2 days (one ORDER BY column only)
GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW — this peer group and the one before

Practice ranking and deduplication: ties kept, exactly N per group, and the latest row of an at-least-once feed.

Time series: moving averages, streaks, sessions

Three patterns cover most event data. Each one is a window function feeding an outer query.

running total, moving average — SUM / AVG OVER (ORDER BY day ROWS ...) — ROWS counts rows, not days: a missing day widens the window. Build a calendar with generate_series, or use RANGE ... INTERVAL
gaps and islands (streaks) — day - ROW_NUMBER() is constant inside a run — duplicates on one day break it: deduplicate first
sessionization — flag ts - LAG(ts) > interval '30 minutes', then a running SUM of the flag — the first event's LAG is NULL; make sure it starts a session

Product analytics: funnels, cohorts, percentiles

Analytics questions are mostly about the grain: one row per customer, per customer-month, or per order before you aggregate again.

funnel conversion — one row per customer with a flag per step — count(*) FILTER (WHERE ...) per step, LAG for step-to-step %
monthly retention — distinct (customer, active month) and each customer's first month — count per (cohort, month number), divide by month 0
median order value — one row per order (sum its items) — percentile_cont(0.5) WITHIN GROUP (ORDER BY total)

percentile_cont interpolates between the two middle values; percentile_disc returns an actual value from the data. Use disc when the answer must be a real row's value, such as a real latency sample.

Writing data

INSERT ... VALUES / SELECT — add rows — RETURNING gives back generated ids (PostgreSQL, SQLite)
UPDATE ... SET ... WHERE — change rows — run the WHERE as a SELECT first
UPDATE ... FROM — change rows from another table — syntax differs in every dialect (card 18)
DELETE ... WHERE — remove some rows — no WHERE removes all rows
TRUNCATE — empty a table fast — transactional in PostgreSQL; other engines differ, so check before relying on a rollback
INSERT ... ON CONFLICT — upsert — needs a unique index or constraint on the conflict target
MERGE — many-branch sync from a source — PostgreSQL 15+; the manual points to ON CONFLICT for concurrent upserts

A query's life inside PostgreSQL

Before tuning anything, know where the time can go: the pool, the planner, the buffer cache, the disk, and the WAL flush at commit. Play the flow; each step shows the real command and what PostgreSQL printed.

connection — too many connections, each a process — SELECT count(*) FROM pg_stat_activity; put a pooler in front
planning — bad row estimates from stale statistics — EXPLAIN ANALYZE: estimated rows= far from actual rows=; run ANALYZE
execution — reading pages that are not in memory — EXPLAIN (ANALYZE, BUFFERS): read= instead of hit=
commit — WAL flush on a slow disk — commit latency; synchronous_commit trades durability of the last moments for speed
waiting — locks held by another session — pg_stat_activity.wait_event_type = 'Lock', pg_blocking_pids()

Transactions and isolation

Two sessions withdraw from one account at the same time. Step through the lost update, then the tabs: the atomic UPDATE fix, REPEATABLE READ failing the second writer, and write skew, which only SERIALIZABLE stops. The outcomes were recorded with two real sessions.

The SQL standard defines four isolation levels by which read anomalies they allow. PostgreSQL implements them more strictly than the minimum.

Read uncommitted — allowed — possible — possible — behaves as Read committed
Read committed — no — possible — possible — the default
Repeatable read — no — no — allowed by the standard — no phantoms (snapshot)
Serializable — no — no — no — also blocks serialization anomalies

PostgreSQL — Read committed
MySQL (InnoDB) — Repeatable read
SQL Server — Read committed
SQLite — Serializable (one writer at a time)

atomic statement — SET balance = balance - 100 instead of read, compute, write
pessimistic lock — SELECT ... FOR UPDATE holds the row until commit
optimistic lock — UPDATE ... WHERE id = ? AND version = ?; zero rows updated means retry
stricter isolation — REPEATABLE READ or SERIALIZABLE; retry the whole transaction on SQLSTATE 40001

Indexes and query plans

A B-tree keeps keys sorted in pages and walks from the root to one leaf, reading one page per level. Compare it with the sequential scan in the next tab, which reads every page. This picture holds 3 keys per page; a real 8 kB page holds hundreds, so on a test server a 10-million-row integer primary key was only three levels deep.

A B-tree index keeps keys sorted, so it helps most when the query constrains its leading columns. For an index on (a, b, c):

a = 1 — seeks on a
a = 1 AND b = 2 — seeks on a, b
a = 1 AND b > 2 — seeks on a, range on b
a = 1 AND c = 3 — seeks on a; c is only checked inside that range
b = 2 — no leading column: at best a full index scan, usually a table scan instead

created_at >= '2024-01-01' — yes
EXTRACT(YEAR FROM created_at) = 2024 — no — a half-open date range, or an expression index
created_at::date = '2024-01-02' — no — created_at >= '2024-01-02' AND created_at 100000 — no — salary > 100000 / 12.0
name LIKE 'Da%' — yes, with a text_pattern_ops index or C collation
name LIKE '%an' — no — a trigram (pg_trgm) GIN index
lower(email) = 'x' — no — an index on lower(email)

B-tree (the default) — =, , BETWEEN, IN, sorting, prefix LIKE
Hash — = only
GIN — arrays, jsonb, full-text search
GiST / SP-GiST — geometry, ranges, nearest neighbor
BRIN — very large tables whose order follows a column, such as time

Seq Scan — reads the whole table; fine for small tables or when most rows match
Index Scan — walks the index, then fetches each row from the table
Index Only Scan — answers from the index alone (the index covers every column used)
Bitmap Index Scan + Bitmap Heap Scan — collects matching row locations first, then reads each table page once, in page order
Nested Loop / Hash Join / Merge Join — small outer side with an indexed inner / large unsorted inputs / inputs already sorted

rows=10 (estimate) vs actual rows=10000 — the planner guessed wrong; stale statistics or correlated columns
loops=500 — the node ran 500 times; multiply its time and rows by loops
Rows Removed by Filter: 49990 — rows read and thrown away: a missing or unusable index
Heap Blocks: exact=10 — table pages a bitmap scan visited
Buffers: shared hit=12 read=3 — pages found in shared_buffers vs fetched from the OS

Practice: read a plan, add the index, read it again; then page through history with a keyset cursor, and find the foreign keys that have no index.

Replication and change data capture

Most production databases have copies: a streaming standby for reads and failover, and a change stream into the warehouse. Both are built on the WAL, and both can surprise you: the standby lags, and a stalled consumer makes the primary keep WAL until its disk fills.

a user saves, reloads and sees the old value — the read went to a standby that has not replayed the write — read from the primary after a write, or wait until pg_last_wal_replay_lsn() passes the write's LSN
cannot execute INSERT in a read-only transaction — a write was routed to a standby — separate read and write connection targets
primary disk filling with WAL — an inactive replication slot — drop unused slots; alert on retained WAL; set max_slot_wal_keep_size

Production: what breaks and how to diagnose

The same handful of problems cause most database incidents. Each has a query that shows it.

N+1 queries — a page runs one query per row it shows — pg_stat_statements: one statement with a huge calls count and a tiny mean_exec_time — one query with a JOIN or WHERE id = ANY($1)
foreign key without an index — deletes on the parent are slow; joins to the child scan it — the FK-index query in the FK indexes lab — CREATE INDEX CONCURRENTLY on the referencing column
deep OFFSET pagination — later pages get slower — EXPLAIN ANALYZE shows actual rows = offset + limit — keyset pagination on an indexed, unique sort key
implicit cast or function on a column — an indexed lookup does a Seq Scan — the plan's Filter: shows (col)::numeric or (col)::date — match the column type; half-open ranges; expression index
NOT IN with a NULL — a report silently returns no rows — SELECT count(*) ... WHERE col IS NULL on the subquery — NOT EXISTS
COUNT(col) read as COUNT(*) — counts lower than expected — compare both — say which you mean
join fan-out — counts and sums too high — count(*) of the joined rows before aggregating — aggregate the child first
lock queue behind DDL — the site stalls during a deploy — pg_blocking_pids() in pg_stat_activity — lock_timeout on DDL, retries, CONCURRENTLY
deadlock — ERROR: deadlock detected (SQLSTATE 40P01) — the server log lists both statements — lock rows in one consistent order
long or idle transaction — table bloat, VACUUM cannot remove dead rows — oldest xact_start in pg_stat_activity; n_dead_tup — end it; idle_in_transaction_session_timeout

Play the deploy that took the site down: one idle transaction, one ALTER TABLE, and every read queued behind them.

These diagnostic queries run anywhere, including this page's database; on a busy server they are the first thing to look at.

Why a long transaction causes bloat: an UPDATE writes a new row version and leaves the old one dead, and VACUUM can only remove dead versions that no open snapshot might still need. With a REPEATABLE READ transaction left idle, VACUUM (VERBOSE) on a test server reported tuples: 0 removed, 20000 remain, 10000 are dead but not yet removable after 10,000 updates; once that transaction ended, the next run removed all 10,000.

> [!WARNING] lock_timeout, idle_in_transaction_session_timeout and statement_timeout are all 0 (off) by default. Set them per role or per migration, not globally to small values.

Dialect differences

first N rows — LIMIT n OFFSET m — LIMIT n OFFSET m — LIMIT n OFFSET m — TOP n, OFFSET m ROWS FETCH NEXT n ROWS ONLY
quote an identifier — "name" — back-quotes — "name" — [name]
concatenate — , concat() skips NULL — CONCAT() returns NULL if any argument is NULL — +, CONCAT()
upsert — ON CONFLICT — ON DUPLICATE KEY UPDATE — ON CONFLICT — MERGE
return written rows — RETURNING — none — RETURNING (3.35+) — OUTPUT
FULL OUTER JOIN — yes — no: UNION a left and a right join — 3.39+ — yes
window functions — yes — 8.0+ — 3.25+ — yes
FILTER (WHERE ...) on aggregates — yes — no: use CASE — yes — no: use CASE
string aggregate — string_agg — GROUP_CONCAT — group_concat, string_agg (3.44+) — STRING_AGG (2017+)
largest of a list — GREATEST — GREATEST — multi-argument max() — GREATEST (2022+)
3 / 2 — 1 — 1.5 (a decimal) — 1 — 1
regex match — ~, ~* — REGEXP — REGEXP only if the app defines it — REGEXP_LIKE (2025+)
default isolation — Read committed — Repeatable read — Serializable — Read committed

What you will be able to do

Predict which rows a query keeps from the clause order and the NULL rules.
Pick the right join, write anti joins that are safe with NULLs, and spot a fan-out.
Choose between GROUP BY, a window function, a CTE and LATERAL from the wording of a question.
Solve the standard analytics problems: top N, dedupe, moving averages, streaks, sessions, funnels, cohorts, medians.
Read a query plan, say why an index is or is not used, and fix it.
Explain lost updates, serialization failures and write skew, and pick the fix.
Diagnose lock queues, replica lag, slot bloat and long transactions with the system views.
Translate the common statements between PostgreSQL, MySQL and SQL Server.

When to use it

A query has more than one join and you must reason about which rows survive and how many times each is counted
A total is too large or a row count is off: usually a join fan-out or a NULL rule
Choosing between ROW_NUMBER, RANK and DENSE_RANK, or setting a window frame
A query is slow and you need to read the plan and decide whether an index can help
Moving a query between PostgreSQL, MySQL, SQLite and SQL Server
An analytics question: top N, deduplication, moving averages, streaks, sessions, funnels, cohorts, medians
A production incident: a stalled deploy, lock waits, replica lag, table bloat, a slow page

SQL