SQL Query Patterns — cheat sheet — SQL & Databases

A wrong SQL query is syntactically identical to a right one: the engine runs it and returns a plausible number, so defense means knowing how each shape fails. Nearly every failure reduces to three mechanisms: three-valued logic, join fan-out, and logical processing order.

Logical processing order

Written order is not evaluation order. The engine runs:

FROM/JOIN -> WHERE -> GROUP BY -> HAVING -> window -> SELECT -> DISTINCT -> ORDER BY -> LIMIT

Alias (the AS name) is born in SELECT, so it is invisible to WHERE/GROUP BY/HAVING and visible in ORDER BY.
WHERE COUNT(*)>1 errors (no groups yet); HAVING COUNT(*)>1 works.
WHERE filters rows before grouping; HAVING filters groups after. Not interchangeable: a WHERE on an aggregate’s input can make a whole group vanish from a report.
Portable trick: GROUP BY 1, ORDER BY 1 ordinals (position in SELECT list) work everywhere.

Three-valued logic (NULL)

NULL means “unknown”. Any comparison with it yields UNKNOWN, including NULL = NULL. WHERE keeps a row only when the predicate is TRUE — UNKNOWN is dropped exactly like FALSE.

status <> 'C' silently drops rows where status is NULL. Fix: status IS DISTINCT FROM 'C' (null-safe; MySQL NOT (status <=> 'C')).
x NOT IN (subquery with any NULL) returns zero rows, always, independent of data — because AND UNKNOWN never yields TRUE. IN is safe; NOT EXISTS is the fix.
Two NULL rules: in filters NULL != NULL (unknown); in grouping (GROUP BY, DISTINCT, UNION, PARTITION BY) two NULLs are the same value.
Every aggregate except COUNT(*) skips NULL. AVG divides by non-NULL count; SUM over all-NULL/empty is NULL, not 0 (wrap in COALESCE(SUM(x),0)).
COALESCE(col, ...) fixes NULLs but is non-sargable (wrapping a column blocks index use).

Joins and fan-out

A join is a filtered cross product. Output row count is a property of keys, not tables:

rows_out = sum over each key k of  n_left(k) x n_right(k)

If either side has multiplicity > 1 on a key, its rows are duplicated — fan-out. Summing a parent measure (orders.amount_cents) over child rows (order_items) double-counts: the classic silent overstatement (207000 vs true 127000, a 1.63x inflation that looks like a good quarter, not a bug).

Fix: pre-aggregate the many-side to one row per key in a CTE before joining, making it one-to-one. One CTE per child.
Two one-to-many children multiply each other: 2 items x 3 payments = 6 rows; no single DISTINCT fixes both.
After any join, ask “what is one row here?” and only sum facts about that thing.

Join	Keeps
`INNER` (default)	only matching pairs; unmatched rows vanish
`LEFT`	all left rows + matches; right cols `NULL` if none
`RIGHT`	mirror of LEFT
`FULL OUTER`	unmatched from both sides
`CROSS`	every pairing, no `ON`

ON vs WHERE on an outer join: a WHERE on the nullable side turns LEFT JOIN into an inner join (NULL = 'C' -> UNKNOWN drops the manufactured rows). Put the filter in ON instead.
Count a NOT NULL right column: COUNT(o.order_id) gives 0 for no-match; COUNT(*) gives 1 (the manufactured row).
Self-join on a hierarchy: inner join drops the root (NULL parent) — use LEFT.
Self-join pairs: use b.id > a.id, not <> (which keeps both orderings, doubling rows).

Anti-join / semi-join

“Which X have no Y” — three spellings, not equally correct:

Form	Result	Notes
`NOT EXISTS`	correct	null-safe, dup-safe, becomes a true anti-join (fastest); the default
`NOT IN`	0 rows if column nullable	avoid
`LEFT JOIN ... WHERE r.key IS NULL`	correct	drop the `IS NULL` and it fans out

EXISTS (semi-join) answers “has at least one” and stops at first match. Prefer over JOIN ... DISTINCT, which fans out then pays to dedup. SELECT 1 vs SELECT * inside EXISTS is identical.

Other traps to memorize

Dates: use half-open >= start AND < end, not BETWEEN (drops the last day’s rows after 00:00 on a timestamp) — a steady ~3%/month undercount. Convert the boundaries for timezones, keep the column bare (sargable).
Missing buckets: GROUP BY only makes groups that exist. Days/regions with zero rows are absent, not zero — daily AVG was 7.5x wrong. Build a spine (generate_series) and LEFT JOIN onto it.
Aggregation: means of means are unweighted; the only correct re-aggregation is SUM(sum)/SUM(count). Inner join hides categories with zero activity.
Pivot: COUNT(CASE ... ELSE 0 END) counts every row (0 is non-NULL). Use FILTER (WHERE ...), SUM(CASE...ELSE 0), or COUNT(CASE...) with no ELSE.
UNION vs UNION ALL: plain UNION adds a hidden DISTINCT over the combined input (hashes/sorts, blocks streaming). Default to UNION ALL. Branches match by position, not name.
DISTINCT is not a function: DISTINCT(region), name dedups the pair. And DISTINCT as a fan-out band-aid hides the grain bug while leaving sums wrong.
Pagination: OFFSET n produces then discards n rows (O(K^2) overall) and duplicates/skips under concurrent writes. Use keyset: WHERE (ts,id) < (last_ts,last_id) on a composite index — constant cost. Append a unique column so the sort is total.
Recursive CTE: UNION ALL + a cycle = infinite loop. Carry a path and add a depth cap.
Percentiles: do not average or add. PERCENTILE_CONT interpolates (can return a value in no row); PERCENTILE_DISC returns an observed value.

The core assumption to check every time

Every trap is a broken assumption about one of three things — uniqueness, nullability, or cardinality — a wrong query that returns a number anyway. Before a <>, check NOT NULL. Before a SUM after a join, check the grain.