01 — SQL Patterns

SQL — Structured Query Language — is how you ask a database a question. You describe the rows you want. The database engine — the program that stores the data and answers questions about it — decides how to get them.

That division of labor cuts both ways: the engine fetches an answer to the question you actually wrote, not the one you meant. Roughly twenty query shapes are worth knowing from memory, and each has a specific way of returning a plausible but wrong number.

When you finish, you should be able to look at a query and do three things:

• say out loud how many rows it will produce, and why
• name the assumption about the data that must hold for its answer to be right
• rewrite it when that assumption does not hold

What goes in, what comes out. The input to every query here is a set of tables — grids of rows and columns, like spreadsheets whose columns have fixed names and types. The output is always another table, called the result set.

That is the whole contract: rows in, rows out. A query cannot hand back “a warning” or “this data looked wrong”. It hands back rows, and it is on you to know whether those rows answer the question you asked.

That contract is why SQL has one property no other language in your stack has: a wrong query is syntactically indistinguishable from a right one, and the engine will run it, and it will return a number. There is no type error, no exception, no red squiggle.

So the defense is not more careful syntax — it is knowing, for each of those ~20 shapes, what the engine actually does with it and the specific way it returns a plausible wrong answer. Nearly every one of those failures reduces to one of three mechanisms:

• Three-valued logic. A comparison against a missing value is neither true nor false. It is unknown, and rows whose filter comes out unknown are silently discarded. That is Three valued logic null is not a value it is an absence of knowledge.
• Join multiplicity. When you combine two tables, how many times each row of one gets repeated by the other. That is Joins five types and the row count formula that predicts fan out.
• Logical processing order. The order in which the engine evaluates the parts of a query, which is not the order you wrote them. That is Logical processing order the fact that explains four error messages.

Once you can derive those three, the rest of the failures become predictable rather than memorized.

Every term is defined where it first appears, and every example runs against one five-table dataset small enough to check by hand. A handful of words recur often enough to be worth defining up front:

The agent-facing version of this material (how to stop a model from writing these queries) is Case Study 07 — SQL / Analytics Agent. This chapter is the mechanism underneath it.

0. The schema every example runs against

Every query in this chapter reads the same five tables, and they are small enough that any result can be checked with a pencil. Here is the whole dataset, once, in full.

A schema is the declaration of what tables exist and what columns they hold. The syntax below is PostgreSQL’s, the most standards-faithful of the common engines. Dialect differences for MySQL and SQLite are flagged as they come up.

Three words in the declarations carry weight:

• A primary key is the column whose value identifies a row uniquely. There is exactly one customers row per customer_id, and the database enforces that.
• A foreign key, written REFERENCES, is a column that points at another table’s primary key. The database refuses to store a value that does not exist over there.
• NOT NULL means the column is guaranteed to have a value in every row. A column without NOT NULL may be empty, and every one of those is a place where Three valued logic null is not a value it is an absence of knowledge’s three-valued logic can bite.

Two column types also need a word. timestamptz is “timestamp with time zone” — an absolute instant, which PostgreSQL stores in UTC and converts on the way in and out (Dates the half open interval rule is entirely about what that does to date filters). char(1) is a one-character string, used here for a status code.

CREATE TABLE customers (
    customer_id  int PRIMARY KEY,
    name         text NOT NULL,
    region       text NOT NULL,
    signup_date  date NOT NULL,
    referred_by  int REFERENCES customers(customer_id)   -- NULLABLE
);

CREATE TABLE orders (
    order_id     int PRIMARY KEY,
    customer_id  int NOT NULL REFERENCES customers(customer_id),
    order_ts     timestamptz NOT NULL,
    status       char(1),           -- 'A' active, 'C' cancelled, NULLABLE
    amount_cents int NOT NULL       -- denormalized order total
);

CREATE TABLE order_items (
    item_id           int PRIMARY KEY,
    order_id          int NOT NULL REFERENCES orders(order_id),
    sku               text NOT NULL,
    qty               int NOT NULL,
    unit_price_cents  int NOT NULL
);

CREATE TABLE employees (
    emp_id      int PRIMARY KEY,
    name        text NOT NULL,
    manager_id  int REFERENCES employees(emp_id)          -- NULLABLE
);

CREATE TABLE events (
    event_id  bigint PRIMARY KEY,
    user_id   int NOT NULL,
    event_ts  timestamptz NOT NULL,
    kind      text NOT NULL
);

Those declarations say what may exist. Here is what actually does — every row of every table, laid out two tables per block so you can see both sides of a join at once. st is status, abbreviated to fit.

customers                                    orders
id  name  region  signup      referred_by    id     cust  order_ts             st  amount
 1  Ada   US      2025-01-05  NULL           1001    1    2025-06-01 09:00Z    A    50000
 2  Bo    US      2025-02-11  1              1002    1    2025-06-15 12:00Z    C    20000
 3  Cy    EU      2025-02-20  1              1003    2    2025-06-30 22:00Z    A    30000
 4  Di    EU      2025-03-02  NULL           1004    3    2025-07-02 08:00Z    A    15000
 5  Ed    APAC    2025-03-15  2              1005    3    2025-06-20 08:00Z  NULL   12000

order_items                                  employees
item  order  sku   qty  unit_price           id  name  manager_id
   1   1001  W-1     2      15000             1  Root  NULL
   2   1001  W-2     1      20000             2  Ann   1
   3   1002  G-9     1      20000             3  Ben   1
   4   1003  W-1     1      15000             4  Cara  2
   5   1003  W-3     3       5000             5  Dan   2
   6   1004  G-9     1      15000             6  Eve   4
   7   1005  W-2     1      12000

Read the shape of that data, because most of the chapter is a consequence of it. Three features matter.

Orders have more line items than there are orders. There are five customers and five orders, but seven order_items rows: orders 1001 and 1003 have two line items each, the other three have one. That is a one-to-many relationship — one parent row on the orders side, many child rows on the order_items side — and it is the single fact that makes Joins five types and the row count formula that predicts fan out’s fan-out possible.

Three columns are allowed to be empty. They are customers.referred_by (nobody referred Ada or Di), orders.status (order 1005 has no status recorded), and employees.manager_id (Root reports to nobody). Those three empty cells generate most of Three valued logic null is not a value it is an absence of knowledge.

The events table is empty. No example below reads it. It is declared because chapter 02 builds on the same schema.

One more property of the data is worth stating, because later sections lean on it. amount_cents on orders is denormalized: it stores a total that could instead be recomputed from the line items, kept alongside them for speed. The line items reconcile with orders.amount_cents exactly — order 1001 is 2×15000 + 1×20000 = 50000, and the other four work out the same way. So any discrepancy you see later is the query’s fault, not the data’s.

How to read the rest of this chapter

The format of each pattern. Each one opens with the plain-English question it answers, then the query, then what came back, then what the engine actually did. Each closes with the assumption the pattern makes about the data and the specific wrong answer you get when that assumption is false.

Those assumptions are always about one of three things: whether a key is unique, whether a column can be empty, or how many rows one key matches. Uniqueness, nullability, cardinality.

Reading the output blocks. Every result block below is checkable against the rows above. The values and the row count are what the query guarantees.

The order of rows is not guaranteed, unless the query has an ORDER BY. Without one, a result set is a bag of rows and the engine may hand them back in any order it finds convenient. Two engines can differ, and so can the same engine after an index is added.

The blocks below are printed in a readable order. Where there is no ORDER BY, read the printed order as this page’s choice, not as the query’s promise. This is the one thing in the chapter you cannot check with a pencil, and it is the reason the last line of a report query is almost always an ORDER BY.

1. Logical processing order — the fact that explains four error messages

You write clauses in one order. The engine evaluates them in another. The written order is SELECT ... FROM ... WHERE ...; the logical order starts at FROM and reaches SELECT second-to-last. Every “column does not exist” error you have ever gotten from a query that obviously has that column is this one fact.

flowchart TD
    F["1 · FROM / JOIN<br/>build the row source<br/>(cross product, then ON filters)"] --> W
    W["2 · WHERE<br/>filter individual rows<br/>no aggregates exist yet"] --> G
    G["3 · GROUP BY<br/>collapse rows into groups"] --> H
    H["4 · HAVING<br/>filter groups<br/>aggregates now exist"] --> WIN
    WIN["5 · window functions<br/>compute across related rows<br/>without collapsing them"] --> S
    S["6 · SELECT<br/>evaluate expressions<br/>ALIASES ARE BORN HERE"] --> D
    D["7 · DISTINCT<br/>dedup the projected rows"] --> O
    O["8 · ORDER BY<br/>aliases are visible"] --> L
    L["9 · LIMIT / OFFSET<br/>discard the rest"]

    style W fill:#1d3557,color:#fff
    style S fill:#2d6a4f,color:#fff
    style WIN fill:#bc6c25,color:#fff

Read that picture as a pipeline. Each stage receives the rows the previous stage produced. Here is the same thing in words, with the one fact per stage that the rest of the section uses:

Two words in that table are worth pinning down now. An alias is the name you attach to a computed column with AS, as in amount_cents / 100.0 AS revenue; the name revenue does not exist anywhere before step 6. LIMIT n means “return at most n rows”, and OFFSET m means “skip the first m of them” — Pagination why offset degrades and the fix is about what that costs.

Four consequences, all derivable from the picture:

Why you cannot use a SELECT alias in WHERE. WHERE runs at step 2; the alias is created at step 6. It does not exist yet.

-- ERROR (PostgreSQL):  column "revenue" does not exist
SELECT amount_cents / 100.0 AS revenue FROM orders WHERE revenue > 200;

-- works: repeat the expression, or wrap in a subquery / CTE
SELECT amount_cents / 100.0 AS revenue FROM orders WHERE amount_cents > 20000;

The second query returns two rows, 500.0 and 300.0 — orders 1001 and 1003. It works because WHERE now names a real stored column instead of a name that step 6 has not yet created.

The comment in that fix names two escapes worth defining now, because they recur throughout the chapter.

A subquery is a complete query nested inside another one, written in parentheses. The inner query runs and its result is used by the outer query.

A CTE, or common table expression, is the same idea given a name up front with the WITH keyword — WITH recent AS (SELECT ...) SELECT ... FROM recent — so the outer query reads as if it were selecting from an ordinary table.

Both work here for the same reason: the inner query finishes step 6 and produces a real named column before the outer query’s WHERE ever runs. Subqueries derived tables ctes and lateral covers how the engine executes each of them.

Why the same alias works fine in ORDER BY. Step 8 is after step 6. ORDER BY revenue DESC is legal in every dialect.

Why WHERE COUNT(*) > 1 is an error and HAVING COUNT(*) > 1 is not. At step 2 no groups exist, so there is nothing for COUNT to count. HAVING is step 4, after grouping.

Why WHERE and HAVING are not interchangeable even when both parse. GROUP BY c.region says “fold all the rows that share a region into one output row per region, and let me aggregate over each fold”.

WHERE and HAVING both look like filters and sit next to each other in the text. But they filter different things at different times. WHERE sees one raw row at a time, before any folding. HAVING sees one whole group at a time, after.

Two queries below make the difference visible. Both start from the same line, FROM customers c JOIN orders o ON o.customer_id = c.customer_id, which pairs every order with the customer who placed it so that a customer’s region is available alongside an order’s amount. That pairing is a join, and Joins five types and the row count formula that predicts fan out derives exactly how many rows it produces. The letters c and o are table aliases, short local names for the tables.

The queries differ in one line each, and those two lines are where the whole lesson lives: Q1 filters with WHERE o.amount_cents >= 20000, Q2 filters with HAVING SUM(o.amount_cents) >= 20000.

-- Q1: revenue per region, counting only large orders
SELECT c.region, SUM(o.amount_cents) AS rev, COUNT(*) AS n
FROM customers c JOIN orders o ON o.customer_id = c.customer_id
WHERE o.amount_cents >= 20000
GROUP BY c.region
ORDER BY rev DESC;

What came back — one row:

region |    rev | n
US     | 100000 | 3

EU is gone. The two EU orders are 15000 and 12000, both under 20000, so neither row survived WHERE and the EU group never formed.

-- Q2: revenue per region, keeping only regions above a threshold
SELECT c.region, SUM(o.amount_cents) AS rev, COUNT(*) AS n
FROM customers c JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.region
HAVING SUM(o.amount_cents) >= 20000
ORDER BY rev DESC;

What came back — two rows:

region |    rev | n
US     | 100000 | 3
EU     |  27000 | 2

EU survives here. Every EU order entered the group, the group’s total came to 27000, and 27000 clears the threshold.

WHERE decides which rows enter a group; HAVING decides which groups survive. Q1 lost the EU region entirely — not because EU revenue was low, but because no single EU order cleared the row filter. That is a whole segment silently missing from a report.

What both queries assume, and what breaks. Two assumptions are buried here.

The first is cardinality. Each row entering the SUM must be one order and not a fragment of one, so this shape is only correct while orders joins to customers many-to-one. Join one more child table — line items, payments — and each order’s amount_cents is repeated once per child row and the revenue inflates. That is Joins five types and the row count formula that predicts fan out’s fan-out.

The second is coverage. An ordinary JOIN keeps only customers who have orders, so any region whose customers ordered nothing produces no row at all rather than a zero. The reader of the report cannot tell the difference between “sold nothing” and “does not exist”. Aggregation shows APAC vanishing exactly this way.

Dialect note. Engines disagree about where an alias is visible.

PostgreSQL extends the standard to allow output aliases in GROUP BY and ORDER BY, but not in WHERE or HAVING. MySQL additionally allows them in HAVING. SQLite goes furthest and resolves SELECT aliases in WHERE too — the erroring query above runs there without complaint and returns two rows, 500.0 and 300.0.

So if you test this section’s central claim in SQLite, the engine will appear to disprove it. It has not. SQLite is being permissive, and the logical order is unchanged underneath: an alias that refers to an aggregate still fails everywhere, and the same query moved to PostgreSQL or MySQL still errors.

Never rely on that leniency in portable SQL. Do rely on GROUP BY 1, 2 and ORDER BY 1 ordinals — an ordinal is the position of a column in the SELECT list, so 1 means “the first thing I selected” — which work everywhere.

2. Three-valued logic — NULL is not a value, it is an absence of knowledge

What does a database do when a query compares something to an empty cell? The answer is that rows quietly disappear from your results — the first of the chapter’s three mechanisms, and the one that produces the most convincing wrong numbers.

Most languages have two truth values, true and false. SQL has three. A predicate like status <> 'C' returns TRUE, FALSE, or UNKNOWN.

NULL is not an empty string and not zero. It is the marker for “there is no value here”, so NULL means “unknown”. Any comparison with it yields UNKNOWN, including NULL = NULL — two unknown quantities cannot be shown to be equal.

This system of three truth values is called three-valued logic. The block below is all of its rules; the left column is comparisons, the right column is how AND, OR, and NOT combine an UNKNOWN with something known.

NULL = 5     -> UNKNOWN         NOT UNKNOWN        -> UNKNOWN
NULL <> 5    -> UNKNOWN         TRUE  OR  UNKNOWN  -> TRUE
NULL = NULL  -> UNKNOWN         FALSE OR  UNKNOWN  -> UNKNOWN
NULL + 5     -> NULL            TRUE  AND UNKNOWN  -> UNKNOWN
                                FALSE AND UNKNOWN  -> FALSE

WHERE keeps a row only when the predicate is TRUE. UNKNOWN is discarded exactly like FALSE, and that asymmetry is where the rows go.

2a. <> silently drops NULLs

The question this pattern answers: “Total revenue from orders that were not cancelled.” The operator <> is SQL’s “not equal to” — some dialects also spell it !=. Here is the obvious query, and the money it loses.

SELECT COUNT(*) AS n, SUM(amount_cents) AS rev
FROM orders WHERE status <> 'C';

Below is the engine’s verdict on each of the five orders, then what came back.

row 1001  'A' <> 'C' -> TRUE     kept
row 1002  'C' <> 'C' -> FALSE    dropped  (correct)
row 1003  'A' <> 'C' -> TRUE     kept
row 1004  'A' <> 'C' -> TRUE     kept
row 1005  NULL <> 'C' -> UNKNOWN dropped  <- NOT what anyone asked for

result:  n = 3,  rev = 95000        intended:  n = 4,  rev = 107000

11.2% of the revenue disappeared and the query did not fail. Three fixes, in order of preference:

WHERE status IS DISTINCT FROM 'C'        -- null-safe <>, standard, PostgreSQL
WHERE status <> 'C' OR status IS NULL    -- explicit, portable, verbose
WHERE COALESCE(status, 'A') <> 'C'       -- states the default; also non-sargable

All three return n = 4, rev = 107000 on this data. They differ in what they say and what they cost.

IS DISTINCT FROM treats NULL as an ordinary value: NULL IS DISTINCT FROM 'C' is TRUE, and NULL IS NOT DISTINCT FROM NULL is TRUE. That is what null-safe means — the comparison always returns true or false and never unknown, so no row can fall through the crack.

MySQL has no IS DISTINCT FROM; it spells null-safe equality <=>, so the MySQL form is NOT (status <=> 'C'). SQLite has supported IS DISTINCT FROM since 3.39 (2022) and it behaves exactly as above; on older SQLite, IS and IS NOT are the null-safe comparisons, as in status IS NOT 'C'.

COALESCE(a, b) returns the first of its arguments that is not NULL. So COALESCE(status, 'A') reads as “the status, or 'A' if it is missing”. Stating the default out loud is its virtue.

It has a cost the other two do not: wrapping a column in a function makes the predicate non-sargable. “Sargable” is a compression of search-argument-able, and it means the engine can hand the condition to an index and jump straight to the matching rows.

An index is a pre-sorted side structure kept alongside the table. The common kind is a B-tree, a balanced tree that keeps values in sorted order, so a lookup costs a handful of steps instead of a full pass over the table.

The index stores the values of status, not the values of COALESCE(status, 'A'). So once you wrap the column, the engine has no choice but to read every row and compute the function. Same answer, much more work. That trade-off recurs throughout Dates the half open interval rule.

What this pattern assumes, and what breaks. Writing status <> 'C' assumes status is never empty. The column is declared without NOT NULL, so that assumption is false in this schema and false in most real ones.

The wrong answer is not an error but an understatement: every row with an unknown status is dropped from a filter that was supposed to keep everything except cancellations. The size of the loss is exactly the fraction of NULLs in the column — a number nobody watching the dashboard can see.

The habit that survives this: before writing <> on any column, check whether it is declared NOT NULL. If it is not, use IS DISTINCT FROM.

2b. NOT IN with a NULL returns zero rows. Always.

This derivation matters most because the result looks like a legitimate empty answer.

The question this pattern answers: “Which customers have never referred anyone?” Intuitively: 3, 4, 5. The tool that suggests itself is NOT IN, where x IN (a, b, c) asks “is x one of these?” and NOT IN asks “is x none of these?” — with the candidate list supplied by a subquery.

SELECT customer_id, name FROM customers
WHERE customer_id NOT IN (SELECT referred_by FROM customers);

What came back: zero rows. Not 3, 4, 5. Not customer 1. Nothing at all. Here is why.

The subquery SELECT referred_by FROM customers returns the multiset {NULL, 1, 1, NULL, 2}. A multiset is a collection that keeps duplicates, which is what you get by reading a column off five rows. Ada’s and Di’s referred_by are empty, Bo and Cy were both referred by customer 1, and Ed was referred by customer 2.

Now expand NOT IN by its definition. It is NOT (x = a OR x = b OR ...), which is the same as x <> a AND x <> b AND .... Each x <> something in that chain is called a conjunct — one of the terms joined by AND — and the whole chain is true only if every conjunct is true.

Trace two customers through it, one who should be in the answer and one who should not:

customer 3:  3<>NULL AND 3<>1 AND 3<>1 AND 3<>NULL AND 3<>2
          =  UNKNOWN AND TRUE AND TRUE AND UNKNOWN AND TRUE
          =  UNKNOWN                                        -> dropped

customer 1:  1<>NULL AND 1<>1  ...
          =  UNKNOWN AND FALSE ...
          =  FALSE                                          -> dropped

The general shape is x NOT IN (list containing NULL), and it fails the same way for every possible x:

flowchart TD
    A["x NOT IN (list containing NULL)"] --> B["expands to<br/>x &lt;&gt; a AND x &lt;&gt; b AND x &lt;&gt; NULL"]
    B --> C{"does x equal<br/>some non-NULL element?"}
    C -->|yes| D["one conjunct is FALSE<br/>FALSE AND UNKNOWN = FALSE"]
    C -->|no| E["all non-NULL conjuncts TRUE<br/>TRUE AND UNKNOWN = UNKNOWN"]
    D --> F["row dropped"]
    E --> F
    F --> G["the predicate can never be TRUE<br/>ZERO ROWS, ALWAYS"]

    style G fill:#9d0208,color:#fff
    style F fill:#bc6c25,color:#fff

The diagram asks one question of every candidate row: does x equal some non-NULL element? If it does, that comparison makes one conjunct FALSE, and FALSE AND UNKNOWN is FALSE, so the row is dropped. If it does not, the row still fails, because the chain now has all its non-NULL conjuncts TRUE and one UNKNOWN left over, and TRUE AND UNKNOWN is UNKNOWN. Both branches lead to the same place.

Because AND with UNKNOWN can only produce FALSE or UNKNOWN, and never TRUE, the predicate has no satisfying row — the result set is empty by construction, independent of the data. The query returns “0 rows”, which reads exactly like “everyone has referred someone.”

IN does not have this problem in the same way, because OR short-circuits upward: 3 IN (NULL, 3) is UNKNOWN OR TRUE = TRUE. A NULL in an IN list is harmless when there is a match and fatal in NOT IN regardless. That asymmetry is the whole reason NOT EXISTS is the default (Anti joins three ways and why not exists is the default).

What this pattern assumes, and what breaks. NOT IN (subquery) assumes the subquery’s column contains no NULLs. referred_by is nullable by design — most customers arrive without a referrer — so the assumption fails on the very first row.

And it fails whatever the data says. The result is empty because of the shape of the logic, not because of the customers.

That is worse than a wrong number, because “0 rows” is a legitimate-looking answer to a “which X have no Y” question. It reads as “everybody has referred somebody”, and nothing in the output distinguishes that from “the predicate was unsatisfiable”.

2c. NULL has two different equality rules, depending on the clause

Everything above described NULL inside a filter. But SQL uses a second, different rule for NULL when it is deciding whether two rows belong in the same bucket. Under the filter rule, NULL = NULL is unknown. Under the grouping rule — used by GROUP BY, by DISTINCT, by UNION, and by window partitioning — two NULLs are treated as the same value and land together.

The table below lists every place the question comes up and which of the two rules applies there. The first row is the filter rule; everything from GROUP BY down to PARTITION BY is the grouping rule.

Four rows of that table need their terms unpacked.

• DISTINCT is the keyword that removes duplicate rows from a result (Deduplication).
• UNION stacks the results of two queries and removes duplicates from the combination (Set operations).
• PARTITION BY is how a window function splits rows into independent groups without collapsing them (chapter 02). It, too, puts all the NULLs in one partition.
• A unique index is a constraint that forbids two rows from sharing a value. PostgreSQL lets any number of rows be NULL in a unique column, because under the filter rule no two of them are provably equal. Since version 15 you can opt into the other behavior with NULLS NOT DISTINCT.

In filters NULL is unknown; in grouping it is just another value. Confusing the two produces reports where the “unknown” bucket exists in the GROUP BY output but was silently deleted by the WHERE clause of a sibling query. The assumption to check: any report that pairs a grouped total with a filtered total assumes the two clauses agree about the empty cells, and they do not, so the two numbers disagree by exactly the size of the unknown bucket — which is the bucket nobody thought to look at.

2d. NULLs in aggregates

The last place NULL changes an answer is inside the aggregate functions themselves. Recall that an aggregate folds many rows into one value; the question is what it does with the rows that have nothing in them.

Five aggregates over the same five orders rows, with what each returns in the comment beside it:

SELECT COUNT(*)           AS all_rows,      -- 5
       COUNT(status)      AS non_null,      -- 4
       COUNT(DISTINCT status) AS kinds,     -- 2  ('A','C'); NULL not counted
       AVG(amount_cents)  AS avg_amt,       -- 25400
       SUM(amount_cents)  AS total          -- 127000
FROM orders;

COUNT(*) counts rows and asks nothing about their contents, so it returns 5. COUNT(status) counts rows where status has a value, so it returns 4. COUNT(DISTINCT status) counts how many different values appear, ignoring the empty one, so it returns 2.

Rules worth stating out loud: every aggregate except COUNT(*) skips NULL inputs. So AVG divides by the count of non-NULL values, not by the row count — an AVG over a column that is 40% NULL is an average over 60% of your data with no indication of that. And SUM over an all-NULL (or empty) input is NULL, not 0; wrap it in COALESCE(SUM(x), 0) whenever the result feeds arithmetic.

What this pattern assumes, and what breaks. Every AVG you write assumes the denominator you have in your head is the row count. When the column is nullable, that assumption fails silently and the average is computed over a subset you did not choose. The subset is usually not random either, because the rows missing a value are missing it for a reason.

The SUM rule bites in a different place. A query that returns NULL instead of 0 propagates that NULL through every later calculation, so one empty group can blank out a whole column of derived numbers.

3. Joins — five types, and the row-count formula that predicts fan-out

The second of the chapter’s three mechanisms is a one-line formula that tells you, before you run a join, how many rows it will produce — and it is the source of the chapter’s most expensive silent error.

A join combines two tables into one wider table by matching rows: for each order, go find the customer who placed it and put their columns on the same row. You write which rows match with an ON condition. Mechanically:

A join is a filtered cross product, and its output cardinality is a sum over join keys, not a property of either table.

A cross product means every row of the left table paired with every row of the right — 5 rows and 7 rows would give 35 pairs. The ON condition then filters that set of pairs down to the ones you wanted. Engines never literally build all 35, but the definition tells you the answer, and the answer is:

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

Read that as: for each distinct value of the join key, multiply how many rows on the left carry that value by how many rows on the right carry it, and add up those products. The count of rows carrying a given key is that side’s multiplicity for that key. Everything about fan-out follows from that one line. If either side has multiplicity greater than 1 on a key, that side’s rows are duplicated by the other side’s count. Fan-out is the name for exactly that: one row going in and several rows coming out.

flowchart TD
    C["CROSS JOIN<br/>every L × every R<br/>no ON clause"] --> I
    I["INNER JOIN<br/>keep pairs where ON is TRUE"] --> L["LEFT JOIN<br/>+ unmatched left rows,<br/>right columns NULL"]
    I --> R["RIGHT JOIN<br/>+ unmatched right rows"]
    L --> F["FULL OUTER JOIN<br/>+ unmatched from both sides"]
    R --> F

    style I fill:#1d3557,color:#fff
    style F fill:#2d6a4f,color:#fff
    style C fill:#bc6c25,color:#fff

The five types differ in exactly one thing: what happens to a row that finds no partner.

• CROSS JOIN has no ON clause at all. Every left row paired with every right row — the raw cross product.
• INNER JOIN is the default; plain JOIN means this. It keeps only the pairs where the ON condition is TRUE, so a row with no match on the other side simply disappears.
• LEFT JOIN keeps all of those pairs plus every unmatched left row, filling the right-hand columns with NULL. This is how you say “all customers, with their orders if they have any”.
• RIGHT JOIN is the mirror image, keeping unmatched right rows instead. Rarely used, because you can always swap the two tables and write a LEFT JOIN.
• FULL OUTER JOIN keeps unmatched rows from both sides.

The word outer in those names is exactly the promise to keep the unmatched rows. It is also why outer joins put NULLs into columns that were declared NOT NULL: the NULL is manufactured by the join, not stored in the table.

3a. Worked cardinality

Here the formula gets used on real numbers, and the classic silent error falls out of it. The question: “What is our total order revenue, broken down by the products in each order?”

Join orders (5 rows) to order_items (7 rows) on order_id. The table below has one line per distinct join key: n_left is how many orders rows carry that order_id, n_right is how many order_items rows carry it, and rows out is their product.

So the join produces seven rows. Now sum a column over those seven rows — once the wrong way, once the right way. The difference is entirely in which column the SUM names.

-- WRONG: amount_cents is at ORDER grain, the rows are at LINE grain
SELECT SUM(o.amount_cents) FROM orders o
JOIN order_items i ON i.order_id = o.order_id;
-- 50000·2 + 20000·1 + 30000·2 + 15000·1 + 12000·1 = 207000

-- RIGHT: aggregate a measure that is additive at the row grain you produced
SELECT SUM(i.qty * i.unit_price_cents) FROM orders o
JOIN order_items i ON i.order_id = o.order_id;
-- 127000

What came back: 207000 from the first query and 127000 from the second. 127000 is the true total, the same number SUM(amount_cents) FROM orders gives on its own.

Follow what happened to order 1001. It has one row in orders carrying amount_cents = 50000, and two rows in order_items. The join pairs that one order row with each of its two line rows, so 50000 now appears on two output rows, and SUM adds it twice. The join changed the grain of the data from one row per order to one row per line item, and amount_cents is only additive at the old grain.

207000 vs 127000 — a 63% overstatement, from a query with no error in it. Note the ratio is 1.63, the amount-weighted average line count: not 2, not 3, nothing that trips a magnitude check. Case study 07 walks the same failure from the agent’s side.

What this pattern assumes, and what breaks — the assumption to internalize above all others in this chapter.

Summing a column after a join assumes that column is measured at the same grain as the rows the join produced. In practice that means assuming the join key is unique on the side the column came from and on the other side too.

order_id is unique in orders, but it has multiplicity 2 in order_items for orders 1001 and 1003. So the assumption fails for exactly those two orders, and their amounts are counted twice.

The wrong answer is an overstatement equal to the average number of children per parent, weighted by the measure. That is why it is so hard to catch: a doubled number looks like a bug, but a 1.63× number looks like a good quarter.

The check costs five seconds. After any join, ask “what is one row of this result?” — then only sum columns that are facts about that thing.

Two independent one-to-many children multiply each other. If order 1001 had 2 line items and 3 payment rows, joining both produces 2 × 3 = 6 rows: every line item appears 3 times and every payment appears twice. Neither SUM(line_total) nor SUM(payment) is correct, and no single DISTINCT fixes both. The formula predicted it.

3b. The pre-aggregation fix

The structural cure for fan-out, and the habit that prevents it rather than detecting it. The question: “For each order, show the stored total next to the total computed from its line items” — a query that has to touch both grains at once, which is precisely when fan-out strikes.

The trick is entirely in the WITH block: it shrinks order_items from seven rows to five before the join happens.

WITH line_totals AS (
    SELECT order_id, SUM(qty * unit_price_cents) AS line_total_cents
    FROM order_items GROUP BY order_id
)
SELECT o.order_id, o.amount_cents, t.line_total_cents
FROM orders o
LEFT JOIN line_totals t ON t.order_id = o.order_id
ORDER BY o.order_id;

What came back — five rows, one per order, no duplication anywhere:

order_id | amount_cents | line_total_cents
    1001 |        50000 |            50000
    1002 |        20000 |            20000
    1003 |        30000 |            30000
    1004 |        15000 |            15000
    1005 |        12000 |            12000

The two columns agree on every row, which is the reconciliation the question asked for. Summing either column now gives 127000, because there are exactly five rows to sum.

The WITH line_totals AS (...) block is a CTE, the named subquery from Logical processing order the fact that explains four error messages. It collapses the seven line rows into five rows, one per order, before anything is joined.

Aggregate the many-side down to one row per key before joining. The join is now one-to-one, multiplicity is 1 on both sides, and every measure in the result is additive. This is the single most useful structural habit in analytical SQL, and it generalizes to any number of children — one CTE per child, all joined one-to-one.

What this pattern assumes, and what breaks. It assumes the GROUP BY inside the CTE really does produce one row per key — group by something coarser than the join key and you have reintroduced the multiplicity you were trying to remove. It also assumes you want zero, not “nothing”, for orders with no line items: the LEFT JOIN keeps such an order and leaves line_total_cents as NULL, so wrap it in COALESCE(..., 0) before any arithmetic downstream (2d nulls in aggregates).

3c. LEFT JOIN and the filter that silently converts it to an INNER JOIN

The question: “How many cancelled orders does each customer have — including the customers who have none?” The “including the ones with none” is the entire reason to reach for a LEFT JOIN, and the query below throws it away.

-- Intent: every customer, with their cancelled-order count.
SELECT c.customer_id, COUNT(o.order_id) AS cancelled
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.status = 'C'                       -- <- the bug
GROUP BY c.customer_id;

LEFT JOIN manufactures rows with all-NULL right columns for unmatched customers. Then WHERE o.status = 'C' evaluates NULL = 'C' -> UNKNOWN on exactly those rows and deletes them. A WHERE predicate on the nullable side of an outer join is an inner join with extra steps.

What came back from the buggy query — one row:

customer_id | cancelled
          1 |         1

Customers 2, 3, 4 and 5 are gone. The question asked for all five.

The fix is to move the predicate into the ON clause, where it filters which right rows are eligible to match rather than which output rows survive:

SELECT c.customer_id, COUNT(o.order_id) AS cancelled
FROM customers c
LEFT JOIN orders o
       ON o.customer_id = c.customer_id AND o.status = 'C'
GROUP BY c.customer_id
ORDER BY c.customer_id;

What came back — five rows, four of them zero, which is what “including the customers who have none” meant:

customer_id | cancelled
          1 |         1
          2 |         0
          3 |         0
          4 |         0
          5 |         0

For an INNER JOIN, ON and WHERE are logically equivalent. For an outer join they are completely different operations. Interviewers ask this exact question.

What this pattern assumes, and what breaks. A LEFT JOIN promises to keep unmatched left rows. Any WHERE condition that mentions a right-hand column silently revokes that promise, because the manufactured NULLs cannot satisfy it.

The wrong answer is a shorter list than you asked for. Customers with zero cancellations vanish instead of showing zero, so a “customers at risk” report drops precisely the healthy customers, and a “customers with no activity” report returns nothing at all.

The one exception is a condition written to expect the manufactured rows, such as WHERE o.order_id IS NULL. That is the anti-join of Anti joins three ways and why not exists is the default, and it is deliberate.

Second trap in the same query: COUNT(*) would return 1 for a customer with no orders, because the manufactured NULL row is still a row. COUNT(o.order_id) returns 0, because COUNT(col) skips NULLs (2d nulls in aggregates). Always count a NOT NULL column from the right table.

3d. FULL OUTER JOIN — the reconciliation pattern

The only join type most people never use. It has one killer application: finding rows present on one side and missing on the other, in a single pass.

The question: “Our orders table and the billing system’s copy should agree — where exactly do they differ?”

The query below reads a second table, orders_from_billing: the other system’s version of the same orders, with the same order_id and amount_cents columns. It is not part of the schema in The schema every example runs against, because the whole point is that it comes from somewhere else. Here are its rows, so the result stays checkable by hand:

orders_from_billing
order_id  amount_cents
    1001         50000     agrees with ours
    1002         20000     agrees with ours
    1003         30500     ours says 30000
    1004         15000     agrees with ours
    1006          9000     we have no order 1006
                           (and our order 1005 is absent here)

Two lines carry the work. FULL OUTER JOIN makes sure an order missing from either side still produces a row, and the closing WHERE throws away the rows where the two sides agree.

SELECT COALESCE(a.order_id, b.order_id) AS order_id,
       a.amount_cents AS ours, b.amount_cents AS theirs,
       CASE WHEN a.order_id IS NULL THEN 'missing_in_ours'
            WHEN b.order_id IS NULL THEN 'missing_in_theirs'
            WHEN a.amount_cents IS DISTINCT FROM b.amount_cents THEN 'mismatch'
            ELSE 'ok' END AS verdict
FROM orders a
FULL OUTER JOIN orders_from_billing b ON b.order_id = a.order_id
WHERE a.order_id IS NULL OR b.order_id IS NULL
   OR a.amount_cents IS DISTINCT FROM b.amount_cents
ORDER BY order_id;

What came back — three rows:

order_id | ours  | theirs | verdict
    1003 | 30000 |  30500 | mismatch
    1005 | 12000 |   NULL | missing_in_theirs
    1006 |  NULL |   9000 | missing_in_ours

Ten input rows across the two tables. The full outer join pairs them into six rows — 1001 through 1006 — and the closing WHERE drops the three that agree (1001, 1002, 1004). That is the point: a healthy reconciliation returns zero rows, so anything at all in this result is a thing to go and fix.

In the output, COALESCE(a.order_id, b.order_id) takes whichever side actually has an id, since one of them is NULL for an unmatched row. The CASE expression — SQL’s if/else, tested top to bottom, first match wins — labels each difference.

Note IS DISTINCT FROM in the mismatch test. With <>, a row where one side is NULL and the other is 5000 would evaluate UNKNOWN, fail all three WHEN arms, and fall through to ELSE 'ok'. A reconciliation query written with <> reports agreement precisely on the rows where one system has no data — the case you built the query to find.

What this pattern assumes, and what breaks. It assumes order_id is unique in both tables. If the billing extract contains an order twice, the full outer join multiplies those rows together exactly as 3a worked cardinality described, and a reconciliation report that is supposed to shrink to zero grows instead — with duplicate “mismatch” rows that no amount of re-checking the amounts will explain. Deduplicate each side to one row per key before comparing.

MySQL has no FULL OUTER JOIN; emulate with LEFT JOIN ... UNION ... RIGHT JOIN. SQLite added it in 3.39.

3e. CROSS JOIN on purpose

The one join with no ON clause. It is a bug by accident and a tool on purpose.

Accidental cross joins come from a missing ON clause, or from the old comma syntax FROM a, b. Both produce every pairing of the two tables, so row counts multiply rather than add: 5 orders and 7 line items become 35 rows instead of 7.

Deliberate cross joins generate a dense grid — every region crossed with every month, say — to left-join real data onto. Recursive ctes builds such a grid recursively and Bucketing and the missing days bug builds one from a series of dates.

4. Self-joins

Every join so far paired two different tables. Some questions relate two rows of the same table — an employee and their manager, two customers in the same region — and for those you join the table to itself.

A self-join is a join whose two sides are the same table under different aliases. Nothing special happens in the engine; giving the table two names, e and m, simply lets you talk about two different rows of it in one query, and everything from Joins five types and the row count formula that predicts fan out applies unchanged. Two distinct uses.

Hierarchy — one level up. The question: “Who is each employee’s manager?” The manager’s name lives in the same employees table as the employee’s, one manager_id hop away.

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.emp_id = e.manager_id
ORDER BY e.emp_id;

What came back — six rows, one per employee:

Root  | NULL      <- INNER JOIN would delete this row
Ann   | Root
Ben   | Root
Cara  | Ann
Dan   | Ann
Eve   | Cara

The root always has a NULL parent, so an inner self-join on a hierarchy silently drops the root — and with it, everything a downstream aggregation would have attributed to the top of the tree. The assumption in one line: this shape assumes every row has a parent, which is false for exactly one row per tree, and that row is the most important one. Arbitrary depth — grandmanagers, whole reporting chains — needs a recursive CTE (Recursive ctes).

Pairs — the inequality predicate that halves the output. The question: “Which pairs of customers share a region?” — the shape behind co-occurrence, duplicate detection, and “people who also bought” analyses.

The second half of the ON clause, b.customer_id > a.customer_id, is the whole trick. Without it the query returns each pair twice plus every row paired with itself.

-- pairs of customers in the same region
SELECT a.name, b.name, a.region
FROM customers a
JOIN customers b ON b.region = a.region AND b.customer_id > a.customer_id;

What came back — two rows:

name | name | region
Ada  | Bo   | US
Cy   | Di   | EU

APAC has one member, Ed, so it contributes no pair at all.

Now count what the > saved you. With b.region = a.region alone, region US (2 members) yields 2 × 2 = 4 rows: (Ada, Ada), (Bo, Bo), (Ada, Bo) and (Bo, Ada). Adding b.customer_id > a.customer_id leaves n(n-1)/2 = 1.

Use > rather than <>. <> removes the self-pairs but keeps both orderings, so you get n(n-1) — exactly double what you want.

What this pattern assumes, and what breaks. The > comparison assumes the id is unique and totally ordered, which a primary key is. On a non-unique column it would keep both orderings of any tied pair and drop the self-pairs unevenly.

The bigger assumption is about cardinality. The output grows with the square of the group size, so self-joins on a large table are the classic accidental quadratic — “quadratic” meaning the work grows as n², so ten times the rows is a hundred times the work.

Put 100k customers in one region and n(n-1)/2 is about 5 × 10^9 output rows. The wrong answer here is not a number; it is a query that never returns.

5. Anti-joins, three ways — and why NOT EXISTS is the default

“Which X have no Y” — customers with no orders, products never sold, users who never came back — is one of the most common questions in analytics, and SQL offers three reasonable-looking ways to write it that are not equally correct.

An anti-join keeps the rows of one table that have no match in another. It is not a keyword; it is a shape, and SQL gives you three ways to express it. Its mirror is the semi-join, which keeps the rows that have at least one match — with the emphasis on at least one, because unlike an ordinary join it returns each left row once no matter how many matches it has.

The question: “Which customers have never referred anyone?” Expected: 3, 4, 5. Customers 1 and 2 each appear in someone’s referred_by; nobody points at 3, 4, or 5.

Four spellings of that one question follow. They differ in one construct each, and one of them is silently broken.

-- (A) NOT EXISTS  -- correct
SELECT c.customer_id FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM customers r WHERE r.referred_by = c.customer_id);

-- (B) NOT IN  -- returns ZERO ROWS (see 2b)
SELECT c.customer_id FROM customers c
WHERE c.customer_id NOT IN (SELECT referred_by FROM customers);

-- (C) LEFT JOIN ... IS NULL  -- correct
SELECT c.customer_id FROM customers c
LEFT JOIN customers r ON r.referred_by = c.customer_id
WHERE r.customer_id IS NULL;

-- (B') NOT IN, repaired
SELECT c.customer_id FROM customers c
WHERE c.customer_id NOT IN (SELECT referred_by FROM customers
                            WHERE referred_by IS NOT NULL);

What came back from each, and why you should care beyond the result column:

Four words in that table need unpacking.

• NOT EXISTS takes a subquery and is true when the subquery returns no rows at all. Because the inner query mentions the outer row’s c.customer_id, it is re-asked for each customer.
• NULL-safe means the form gives the right answer even when the compared column contains empty cells. NOT IN is the one that does not.
• Duplicate-safe means the form returns each left row once even when the right side has several matches, so it cannot fan out.
• The planner is the part of the engine that chooses how to execute your query: which table to read first, whether to build a hash table in memory or sort both sides and merge them. You describe the result; the planner picks the algorithm. Different phrasings of the same result give it different room to work.

Why NOT EXISTS is also the fastest. It states a semantics the planner can implement as a true anti-join: hash the inner side once, stream the outer side, emit the rows that miss.

NOT IN cannot be transformed that way when the inner column is nullable, because the correct answer genuinely depends on whether a NULL is present. So PostgreSQL falls back to a hashed subplan — a little lookup table built once and then consulted separately for every outer row, which is what SubPlan means in a PostgreSQL query plan. That shape also parallelizes badly across CPU cores.

The performance difference and the correctness difference have the same root cause: three-valued semantics block the optimization that two-valued semantics would allow.

Why (C) costs more even though it is correct. Its output cannot duplicate, because WHERE r.customer_id IS NULL discards every row that matched. What survives is exactly one row per unmatched left row, no matter what you project.

The cost is upstream of that filter. Where the planner does not recognize the anti-join — MySQL, SQLite, or PostgreSQL when the shape is obscured — the join materializes all 50 matches for a key, meaning it actually builds them in memory, and then throws away all 50. That is memory and time a true anti-join never spends.

The trap is one keystroke away: drop the IS NULL and the same query is a semi-join written badly, and now it does fan out, 50 rows for that one customer. NOT EXISTS cannot be broken this way, which is the better reason to prefer it than any of the performance arguments.

And the repaired NOT IN (B’) is correct but still not fast. PostgreSQL has no NOT IN -> anti-join transformation; adding WHERE referred_by IS NOT NULL inside the subquery fixes the semantics without telling the planner anything it can use, so it stays a hashed SubPlan. Only NOT EXISTS gets the anti-join.

What these patterns assume, and what breaks. NOT EXISTS and the LEFT JOIN ... IS NULL form assume nothing about the data. That is the point of preferring them.

NOT IN assumes the subquery column has no NULLs, and returns zero rows when it does (2b not in with a null returns zero rows always).

The LEFT JOIN form assumes you remember the IS NULL, and its failure is the opposite of empty. Forget it and you get one row per match, so a customer with 50 matching rows appears 50 times and any count above it is inflated.

The mirror image is the semi-join, which answers “which customers have ordered at all?”:

-- customers who HAVE ordered
SELECT c.* FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);

What came back — three rows, customers 1, 2 and 3. Customer 1 has two orders and customer 3 has two orders, yet each appears once. Customers 4 and 5 have never ordered and are absent.

Do not write this as JOIN orders o ON ... plus SELECT DISTINCT c.*. That form fans out to one row per order — five rows here rather than three — and then pays a dedup to undo it. EXISTS stops at the first match per outer row.

EXISTS answers “is there at least one” and stops; a join answers “which ones” and enumerates them. SELECT 1 versus SELECT * inside EXISTS makes no difference at all, because the projection is never evaluated.

6. Aggregation

Folding many rows into one number per group is the backbone of every report — and a grouped report lies in two ways: by leaving out a group entirely, and by averaging averages.

GROUP BY x collapses every set of rows that share the same x into a single output row, and each aggregate in the SELECT list is computed over the rows in that fold.

6a. The grouped report, and the group that is not in it

The question: “Per region, how many orders, how many distinct buyers, how much revenue, what average order size, and when was the last order?” Five aggregates over one grouping.

SELECT c.region,
       COUNT(*)                    AS orders,
       COUNT(DISTINCT c.customer_id) AS buyers,
       SUM(o.amount_cents)         AS rev_cents,
       ROUND(AVG(o.amount_cents))  AS avg_cents,
       MAX(o.order_ts)             AS last_order
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.region
ORDER BY rev_cents DESC;

What came back — two rows, for three regions of customers:

region | orders | buyers | rev_cents | avg_cents | last_order
US     |   3    |   2    |  100000   |   33333   | 2025-06-30 22:00Z
EU     |   2    |   1    |   27000   |   13500   | 2025-07-02 08:00Z

COUNT(*) counts rows in the group — orders, here, because the join put one row per order. COUNT(DISTINCT c.customer_id) counts how many different customers those rows came from, which is why “3 orders, 2 buyers” is not a contradiction. MAX(o.order_ts) picks the latest timestamp in the group.

APAC is missing. Customer 5 has no orders, so the inner join produced no APAC rows and GROUP BY cannot invent a group for rows that do not exist. A dashboard reading this table concludes APAC does not exist, rather than that APAC sold nothing. Fix with LEFT JOIN from customers — and then COUNT(o.order_id), not COUNT(*) (3c left join and the filter that silently converts it to an inner join).

What this pattern assumes, and what breaks. Two assumptions.

The first is coverage: every grouped report assumes that the groups you want to see are present in the rows you fed it. That is the coverage assumption from Logical processing order the fact that explains four error messages, and an inner join breaks it for any category with zero activity.

The second is the same uniqueness assumption as 3a worked cardinality: SUM(o.amount_cents) here is only right because each order contributes exactly one row. Add a join to order_items for a product breakdown and every regional revenue figure inflates, while COUNT(*) silently stops meaning “orders” and starts meaning “line items”.

6b. Average of averages

Take the two per-region averages from the report above and average them. AVG(region_avg) over those two rows is (33333 + 13500) / 2 = 23417. The true overall average is 127000 / 5 = 25400.

The gap exists because US has three orders and EU has two. Averaging the two regional figures gives each region equal weight regardless of how many orders it stands for.

Means of means are unweighted; the only correct re-aggregation is SUM(sum) / SUM(count). This is the same weighting error as macro- versus micro-averaging in ML 06 — Metrics, where averaging per-class scores gives a 20-row class the same weight as a 900-row one.

6c. Subtotals in one pass: ROLLUP and GROUPING SETS

These answer “give me the per-region-per-month totals and the regional subtotals and the grand total, in one query”.

One helper appears in the query below. DATE_TRUNC('month', ts) snaps a timestamp down to the first instant of its month, so every June order groups together under 2025-06-01 00:00.

SELECT c.region, DATE_TRUNC('month', o.order_ts) AS mon, SUM(o.amount_cents)
FROM customers c JOIN orders o ON o.customer_id = c.customer_id
GROUP BY ROLLUP (c.region, DATE_TRUNC('month', o.order_ts));

ROLLUP (a, b) walks a hierarchy. It returns the per-(a, b) totals, then the subtotal per a, then the grand total. GROUPING SETS is the general form: you list the exact combinations you want instead of getting a nested sequence.

Subtotal rows carry NULL in the rolled-up column, which is indistinguishable from a genuine NULL group. Use GROUPING(c.region), which returns 1 for a subtotal row and 0 for a detail row, to tell them apart.

What came back — 6 rows: three detail rows, then one subtotal per region, then one grand total. (This assumes a UTC session, since DATE_TRUNC on a timestamptz truncates in the session’s timezone.)

region | mon              | sum      <- what the NULLs mean
US     | 2025-06-01 00:00Z| 100000      detail: orders 1001, 1002, 1003
EU     | 2025-06-01 00:00Z|  12000      detail: order 1005
EU     | 2025-07-01 00:00Z|  15000      detail: order 1004
US     | NULL             | 100000      subtotal: all of US, every month
EU     | NULL             |  27000      subtotal: all of EU, every month
NULL   | NULL             | 127000      grand total

Read the NULLs as “rolled up here”, not as “missing”. Three levels of detail arrive interleaved in one result set, and the only thing distinguishing a subtotal row from a detail row is a NULL in the column that was collapsed — which is why GROUPING() exists and why any consumer of this shape has to filter on it rather than eyeballing the nulls. Row order is not guaranteed either; add an explicit ORDER BY if a report depends on subtotals landing beneath their details.

Dialect note. ROLLUP, GROUPING SETS, CUBE and GROUPING() are in PostgreSQL 9.5+, MySQL 8 (as WITH ROLLUP, a weaker spelling), and SQL Server. SQLite has none of them. Emulate with a UNION ALL of one GROUP BY per level, which is literally what ROLLUP expands to.

DATE_TRUNC is likewise PostgreSQL-only. MySQL wants DATE_FORMAT(ts, '%Y-%m-01'); SQLite wants strftime('%Y-%m', ts).

One SQLite gotcha while you are there: its date functions want a UTC offset spelled +00:00 or Z. A bare +00 parses as nothing, and every date function then silently returns NULL. Between that and the missing ROLLUP, this is the one query in the chapter you cannot run in SQLite to check.

7. Conditional aggregation — pivoting without a pivot operator

The grouped reports of Aggregation put each category on its own row. Dashboards usually want categories as columns instead — one for active revenue, one for cancelled — computed without reading the table more than once, and there is a one-word mistake that makes the whole thing return the wrong count.

To pivot is to rotate values into columns. Instead of one row per (region, status), you want one row per region with a column per status.

PostgreSQL has no dedicated pivot operator and does not need one. A CASE WHEN condition THEN a ELSE b END expression, evaluated per row and placed inside an aggregate, turns rows into columns in a single pass.

The question: “Per region, how much revenue is active, how much is cancelled, how many active orders are there, and how many have no status at all?” Four measures, one scan.

SELECT c.region,
       SUM(CASE WHEN o.status = 'A' THEN o.amount_cents ELSE 0 END) AS active_cents,
       SUM(CASE WHEN o.status = 'C' THEN o.amount_cents ELSE 0 END) AS cancelled_cents,
       COUNT(*) FILTER (WHERE o.status = 'A')                       AS n_active,
       COUNT(*) FILTER (WHERE o.status IS NULL)                     AS n_unknown
FROM customers c JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.region
ORDER BY active_cents DESC;

What came back — two rows, four measures wide:

region | active_cents | cancelled_cents | n_active | n_unknown
US     |    80000     |     20000       |    2     |     0
EU     |    15000     |         0       |    1     |     1

Check the EU row against the data. EU has two orders: 1004 with status A and amount 15000, and 1005 with no status at all and amount 12000. So active_cents is 15000, cancelled_cents is 0, n_active is 1, and n_unknown is 1. The 12000 appears in no revenue column.

COUNT(*) FILTER (WHERE ...) is the same idea with dedicated syntax: it counts only the rows in the group that satisfy the condition. Note that n_unknown uses IS NULL rather than = NULL, for the reason Three valued logic null is not a value it is an absence of knowledge gave — = NULL is never true.

The mechanism: one table scan, N conditional accumulators. The engine walks the rows once and keeps N running totals side by side, adding to whichever ones each row qualifies for. The alternative — N separate queries stacked with UNION, or N subqueries re-asked per row — reads the table N times for the same answer.

The failure mode, and it is very common. Four ways to count the active orders, over all five orders rows. Three are right and the first is not:

COUNT(CASE WHEN status = 'A' THEN 1 ELSE 0 END)   -- returns 5, counts EVERYTHING
COUNT(CASE WHEN status = 'A' THEN 1 END)          -- returns 3, correct
SUM (CASE WHEN status = 'A' THEN 1 ELSE 0 END)    -- returns 3, correct
COUNT(*) FILTER (WHERE status = 'A')              -- returns 3, correct and readable

COUNT(x) counts non-NULL values, and 0 is not NULL. So with ELSE 0, every row hands COUNT either a 1 or a 0, and both are things to count.

ELSE 0 inside COUNT makes the condition irrelevant. The query returns the row count of the group — a plausible-looking number that happens to answer a different question. Use ELSE 0 with SUM, omit ELSE with COUNT, or use FILTER.

What this pattern assumes, and what breaks. The CASE arms assume they cover every value the column can take, and the value they always forget is NULL: WHEN status = 'A' and WHEN status = 'C' between them account for four of the five orders, so a report that adds active_cents + cancelled_cents and calls it total revenue is short by the unknown bucket. Write an explicit IS NULL arm, as n_unknown does, and the missing rows become visible instead of merely absent.

FILTER (WHERE ...) is SQL:2003, in PostgreSQL 9.4+ and SQLite 3.30+. MySQL has no FILTER; use the CASE forms.

8. Subqueries, derived tables, CTEs, and LATERAL

SQL has several ways to put a query inside another query. They look interchangeable in the text and are not interchangeable in the engine — the difference that matters is whether the inner query runs once or once per row.

Four ways to nest a query, with materially different execution:

• A scalar subquery returns a single value, and can therefore stand anywhere a value is expected.
• A derived table is a subquery in the FROM clause, so it stands where a table is expected.
• A CTE is the same thing hoisted out and named with WITH.
• LATERAL marks a subquery in the FROM clause that is allowed to refer to columns of the rows already produced to its left. That is normally forbidden, because items in a FROM clause cannot see each other.

The column that matters in the table below is the last one — how many times the inner query actually runs:

8a. Correlated versus uncorrelated

Correlated means the inner query references a column from the outer query, which makes it a function of the outer row rather than a constant.

An uncorrelated subquery mentions nothing from outside. Its answer is the same for every row, so the engine computes it once and reuses it.

A correlated one changes with each row, so conceptually it is re-asked per row. “Conceptually” is doing real work in that sentence: a good planner will often rewrite it into something better.

The question: “What is each customer’s total revenue?” Written twice — once with a correlated scalar subquery, once as a single grouped join.

-- correlated: O(n · m) without an index on orders(customer_id)
SELECT c.customer_id,
       (SELECT SUM(o.amount_cents) FROM orders o
        WHERE o.customer_id = c.customer_id) AS rev
FROM customers c;

-- rewritten as one aggregate + one join: two scans total
SELECT c.customer_id, t.rev
FROM customers c
LEFT JOIN (SELECT customer_id, SUM(amount_cents) AS rev
           FROM orders GROUP BY customer_id) t
       ON t.customer_id = c.customer_id;

Both return the same five rows:

customer_id |   rev
          1 | 70000
          2 | 30000
          3 | 27000
          4 |  NULL
          5 |  NULL

The first query scans orders once per customer; the second scans it once. O(n · m) in the comment is the usual shorthand for “work proportional to the number of customers times the number of orders”. With 100k customers and 5M orders and no index, that is 100k scans versus 1.

Modern planners de-correlate many of these automatically. They recognize the per-row subquery and rewrite it as a single grouped join — exactly the second form above.

But they cannot do it when the subquery contains LIMIT, a volatile function, or a non-trivial ORDER BY. A volatile function is one that may return a different answer on each call, like random() or now(), so it is not safe to evaluate a different number of times. Those three cases are exactly what people write by hand.

Note the last two rows of the output. Customers 4 and 5 have no orders, and both forms give them NULL, not 0. Anything arithmetic downstream needs COALESCE(rev, 0).

8b. The CTE materialization fence

An interview favorite, and one where the correct answer changed in 2019.

Through PostgreSQL 11, WITH was an optimization fence. The CTE was always materialized, meaning computed in full into a temporary result before the outer query ran. So a filter in the outer query could not be pushed down into the CTE to make it cheaper: the CTE would compute a million rows and the outer WHERE would then discard all but ten.

From PostgreSQL 12 a CTE is inlined — spliced into the outer query so the planner optimizes them together — provided it is not recursive, has no side effects, and is referenced exactly once. You can force either behavior:

WITH big AS MATERIALIZED     (SELECT ...)   -- compute once, reuse; blocks pushdown
WITH big AS NOT MATERIALIZED (SELECT ...)   -- inline even if referenced many times

Reach for MATERIALIZED when a CTE is expensive and referenced several times; reach for NOT MATERIALIZED when a CTE referenced twice would benefit from having the outer WHERE pushed into it. Old advice that “CTEs are always slower in Postgres” is a PG-11-and-earlier fact.

8c. LATERAL

LATERAL is the form that unlocks per-row subqueries with LIMIT. The question: “What are the two most recent orders for each customer?”

That question is impossible with a scalar subquery, which by definition returns one value, and awkward with a plain join, which cannot say “only the top two per customer”.

The line that makes it work is WHERE o.customer_id = c.customer_id inside the subquery: LATERAL is what permits the inner query to see c at all.

-- the two most recent orders per customer
SELECT c.customer_id, l.order_id, l.order_ts
FROM customers c
LEFT JOIN LATERAL (
    SELECT o.order_id, o.order_ts FROM orders o
    WHERE o.customer_id = c.customer_id
    ORDER BY o.order_ts DESC LIMIT 2
) l ON true
ORDER BY c.customer_id, l.order_ts DESC;

What came back — seven rows, because customers 1 and 3 have two orders each, customer 2 has one, and customers 4 and 5 have none but are kept by the LEFT:

customer_id | order_id | order_ts
          1 |     1002 | 2025-06-15 12:00Z
          1 |     1001 | 2025-06-01 09:00Z
          2 |     1003 | 2025-06-30 22:00Z
          3 |     1004 | 2025-07-02 08:00Z
          3 |     1005 | 2025-06-20 08:00Z
          4 |     NULL | NULL
          5 |     NULL | NULL

ON true is there because the syntax demands a join condition and the correlation already lives inside the subquery. There is nothing left to say.

With an index on orders(customer_id, order_ts DESC), this is k index lookups per customer and touches nothing else — dramatically better than ranking the entire orders table, when customers are few and orders are many (Top n per group three ways the fourth is chapter 02).

What these patterns assume, and what breaks.

A scalar subquery assumes it returns at most one row. Return two and PostgreSQL raises an error rather than guessing — the rare case in this chapter where SQL protects you. Do not count on that everywhere: SQLite accepts the same subquery and silently keeps one row.

LEFT JOIN LATERAL assumes you want customers with no orders kept. Write a plain JOIN LATERAL and rows 4 and 5 above disappear — the 3c left join and the filter that silently converts it to an inner join failure again.

And every one of these forms returns NULL, not 0, for a customer with no orders, so anything arithmetic downstream needs COALESCE.

Dialect note. LATERAL is PostgreSQL 9.3+, MySQL 8.0.14+, and Oracle. SQL Server spells the same idea CROSS APPLY / OUTER APPLY. SQLite has no LATERAL; use the ROW_NUMBER form from chapter 02 instead.

9. Recursive CTEs

One construct in standard SQL can follow a chain of unknown length — a reporting line, a category tree, a graph of referrals — and it has two ways of failing to stop. The question: “Print the whole org chart, with each person’s depth and the path from the root down to them.” The self-join of Self joins could only go one level up; this goes all the way.

A recursive CTE is a fixed-point iteration with SQL syntax. It applies the same step over and over, feeding each round’s output back in, until a round produces nothing new.

It has two halves, separated by UNION ALL:

• The anchor term is the starting set, evaluated once. Here it is the employees whose manager_id is empty — the root of the tree.
• The recursive term is the step, evaluated repeatedly. Here it is “join employees to the rows I just produced, to find their direct reports”.

The recursive term does not see the whole accumulated result. It sees only the rows produced by the immediately preceding iteration. That set is called the working table, and the fact that it is small and fresh each round is what makes the evaluation traceable by hand.

WITH RECURSIVE org AS (
    SELECT emp_id, name, manager_id, 0 AS depth,
           ARRAY[emp_id] AS path                 -- anchor
    FROM employees WHERE manager_id IS NULL
  UNION ALL
    SELECT e.emp_id, e.name, e.manager_id, o.depth + 1,
           o.path || e.emp_id                    -- recursive term
    FROM employees e
    JOIN org o ON e.manager_id = o.emp_id
    WHERE NOT e.emp_id = ANY(o.path)             -- cycle guard
)
SELECT repeat('  ', depth) || name AS tree, depth, path FROM org ORDER BY path;

The final SELECT is cosmetic: repeat(' ', depth) makes a string of depth double-spaces and || glues it to the name, so the output prints as an indented tree.

The diagram below traces the iterations against the six employees. Read it top to bottom; each box is one round.

flowchart TD
    A["anchor: manager_id IS NULL<br/>-> {Root d0}"] --> I1
    I1["iter 1: join employees to<br/>working table {Root}<br/>-> {Ann d1, Ben d1}"] --> I2
    I2["iter 2: working table {Ann, Ben}<br/>-> {Cara d2, Dan d2}"] --> I3
    I3["iter 3: working table {Cara, Dan}<br/>-> {Eve d3}"] --> I4
    I4["iter 4: working table {Eve}<br/>-> empty"] --> S["stop · result = union of<br/>all 6 rows emitted"]

    style A fill:#1d3557,color:#fff
    style I4 fill:#bc6c25,color:#fff
    style S fill:#2d6a4f,color:#fff

What came back — six rows, one per employee:

tree        depth  path
Root          0    {1}
  Ann         1    {1,2}
    Cara      2    {1,2,4}
      Eve     3    {1,2,4,6}
    Dan       2    {1,2,5}
  Ben         1    {1,3}

Trace the diagram against the data, one round at a time.

The anchor selects the one employee with manager_id IS NULL — Root, at depth 0. That single row becomes the working table.

Iteration 1 joins employees to the working table {Root} and finds everyone whose manager is Root: Ann and Ben, at depth 1. Those two rows replace the working table.

Iteration 2 works from {Ann, Ben} and emits Cara and Dan at depth 2. Iteration 3 works from {Cara, Dan} and emits Eve at depth 3.

Iteration 4 works from {Eve}, finds nobody reporting to Eve, and produces nothing. An empty round is the stop condition. The result is the union of all 6 rows emitted along the way.

Three pieces of syntax carry the bookkeeping:

• depth + 1 counts levels.
• ARRAY[emp_id] starts a list of ancestors, and o.path || e.emp_id appends to it. So path ends up holding the chain of ids from the root down to that person.
• NOT e.emp_id = ANY(o.path) is the cycle guard. = ANY(array) asks “is this id already somewhere in the ancestry?”, and refusing such rows stops the query from walking in a circle forever.

Dialect note. WITH RECURSIVE itself is portable (PostgreSQL, MySQL 8+, SQLite 3.8.3+, SQL Server), but the array machinery in the query above is PostgreSQL-only: ARRAY[...], || as array append, = ANY(array), and repeat() all fail elsewhere. The portable rewrite carries the path as a delimited string and tests membership with instr, which every engine has. Wrap each id in the delimiter on both sides so that searching for /6/ cannot match inside /16/:

--  anchor:     '/' || emp_id || '/'              instead of  ARRAY[emp_id]
--  recursive:  o.path || e.emp_id || '/'         instead of  o.path || e.emp_id
--  guard:      instr(o.path, '/'||e.emp_id||'/') = 0   instead of  NOT e.emp_id = ANY(o.path)

path then reads /1/2/4/6/ instead of {1,2,4,6}, and ORDER BY path still gives the same depth-first order for the same reason. (MySQL spells instr the same way; SQL Server spells it CHARINDEX, with the arguments reversed. For the indenting, repeat(' ', depth) is REPEAT in MySQL and has no SQLite equivalent — use replace(substr(' ', 1, depth), ' ', ' ') or indent in the application.)

Three mechanisms worth naming:

ORDER BY path gives depth-first order for free. Sorting by the ancestry array sorts children under their parent, which is depth-first order: follow one branch all the way down before starting the next. Sorting by depth instead gives breadth-first order: everyone at level 1, then everyone at level 2. Neither requires a second pass.

UNION ALL vs UNION decides whether cycles terminate. UNION deduplicates each new iteration against everything produced so far. A cycle eventually produces only rows already seen, the working table empties, and the query stops.

UNION ALL does not deduplicate. A single bad row — say employees row 1 given manager_id = 6, making Root report to Eve — makes the query loop forever, generating rows until the disk fills.

The path guard above is the explicit fix and works with UNION ALL. PostgreSQL 14+ also offers CYCLE emp_id SET is_cycle USING path as first-class syntax.

A depth cap is cheap insurance. Add AND o.depth < 20 to the recursive term. Real org charts and category trees are shallow; a query that reaches depth 20 has found a data problem, and you would rather learn that in 40 ms than in an incident.

What this pattern assumes, and what breaks. It assumes the data is a tree: exactly one row with no parent, every other row pointing at a row that exists, and no cycles. Two roots and you silently get two trees interleaved in one result. A row whose manager_id points at a deleted employee is unreachable and simply never appears — an employee missing from the org chart with no error anywhere. And a cycle, with UNION ALL and no guard, is not a wrong answer but a query that runs until the disk fills.

Recursive CTEs also generate sequences (a spine of dates, 1..n, a bill-of-materials explosion) and are how you write generate_series in MySQL, which lacks it. A spine is a complete axis of values generated on purpose so real data can be left-joined onto it — the fix for missing rows in Bucketing and the missing days bug.

10. Set operations

Joins put two tables side by side; sometimes you instead want to stack the results of two queries into one. The trap here is an invisible deduplication that one spelling performs and the other does not — and it costs a great deal at scale.

Set operations combine whole result sets rather than joining rows side by side. There are four:

• UNION ALL stacks two results.
• UNION stacks them and removes duplicates.
• INTERSECT keeps only rows appearing in both.
• EXCEPT keeps rows from the first that do not appear in the second.

The question below is “which products appear on order 1001 or on order 1005?”, asked two ways. Order 1001 has line items W-1 and W-2; order 1005 has one line item, W-2. So W-2 is a genuine duplicate across the two branches.

SELECT sku FROM order_items WHERE order_id = 1001
UNION ALL
SELECT sku FROM order_items WHERE order_id = 1005;   -- W-1, W-2, W-2  (3 rows)

SELECT sku FROM order_items WHERE order_id = 1001
UNION
SELECT sku FROM order_items WHERE order_id = 1005;   -- W-1, W-2       (2 rows)

The table’s set semantics versus multiset semantics distinction is just this: a set has no duplicates, a multiset keeps them. INTERSECT and EXCEPT deduplicate by default; the ALL variants keep multiplicities.

UNION is UNION ALL plus a DISTINCT you did not ask for, and the dedup runs over the sum of both inputs.

Scale that up. Two 10-million-row branches: UNION ALL emits 20M rows and allocates nothing. UNION must hash or sort all 20M first — hundreds of megabytes of work space, likely spilling to disk, which means running out of the memory budget and writing intermediate data to temporary files, orders of magnitude slower.

Worse, the whole UNION blocks: no row can be returned to the caller until the last input row has been read, so nothing streams.

And if the branches are provably disjoint — different date ranges, different statuses, different shards, where a shard is one slice of a table split across several machines — all that work removes zero rows.

Write UNION ALL by default. Add the DISTINCT deliberately, when you can name the duplicates it removes.

Three more rules govern every set operation.

Branches match positionally, not by name. They must agree on column count and have compatible types, and the first branch’s column names win. This is the assumption most likely to fail: swap two columns of the same type in one branch and the query still runs, silently interleaving amounts with ids under the first branch’s column names.

ORDER BY and LIMIT at the end apply to the whole result, not to the last branch. Parenthesize a branch if you need to limit just that one.

Dedup uses grouping equality, so NULL matches NULL here (2c null has two different equality rules depending on the clause).

EXCEPT as a snapshot diff

EXCEPT is the clean “what changed” diff. The question: “which (order, amount) pairs are in today’s snapshot and were not in yesterday’s?” — that is, the orders that are new or whose amount moved.

The two snapshot tables are copies of orders taken on different days. They are not part of the schema in The schema every example runs against, so here are their rows, small enough to diff by hand:

orders_snapshot_yesterday        orders_snapshot_today
1001  50000                      1001  50000      unchanged
1002  20000                      1002  25000      amount moved
1003  30000                      1003  30000      unchanged
                                 1004  15000      new order

(SELECT order_id, amount_cents FROM orders_snapshot_today
 EXCEPT
 SELECT order_id, amount_cents FROM orders_snapshot_yesterday)
ORDER BY order_id;

What came back — two rows:

order_id | amount_cents
    1002 |        25000
    1004 |        15000

Now note two things the diff cannot tell you.

It cannot distinguish an edit from an insert. (1002, 25000) and (1004, 15000) come back looking identical, but 1002 is an amount that changed and 1004 is a brand-new order. EXCEPT compares whole rows and reports the ones that are new as rows. To tell “changed” from “added”, you need the key comparison of the FULL OUTER JOIN in 3d full outer join the reconciliation pattern.

It is one-directional. 1002’s old value (1002, 20000) appears nowhere in the output. Running the diff the other way round is a second query.

11. Dates: the half-open interval rule

Nearly every date-filter bug traces back to ignoring one rule: include the start instant, exclude the end instant. Timezones complicate the rule, and the days on which nothing occurred hide a second trap — but the rule comes first.

A half-open interval is a range that contains its lower bound and excludes its upper, written [start, end). Expressed as order_ts >= start AND order_ts < end, the filter keeps the column bare and usable by an index; wrapping the column in a function to hit an exact bucket makes the predicate non-sargable, the cost 2a described.

The two correct spellings differ only in that cost:

WHERE DATE_TRUNC('month', order_ts) = DATE '2025-06-01'   -- correct, NOT sargable
WHERE order_ts >= ... AND order_ts < ...                  -- correct AND sargable

Wrapping the column in a function means the index on order_ts is unusable — the engine must compute DATE_TRUNC for every row. Same answer, sequential scan. (An expression index on DATE_TRUNC('month', order_ts) restores it, if you truly need that shape.)

What this pattern assumes, and what breaks. BETWEEN on a timestamp assumes the column has no time-of-day component — it is correct on a date column and wrong on a timestamptz one, which is why the mistake survives code review by people who once used it correctly. The wrong answer is a steady, proportional undercount, one day in thirty, appearing every month with the same sign, which is exactly the profile of an error that never gets found.

Timezones. Being timestamptz, order_ts holds an absolute instant, stored in UTC (Coordinated Universal Time, the zero-offset reference clock) and converted on the way in and out.

“June in New York” is not “June in UTC”. It starts at 2025-06-01 04:00Z, where the trailing Z means UTC. So an order stamped 2025-07-01 02:00Z is still June 30 in New York, and a UTC filter files it under July.

The fix is to convert the boundaries, not the column:

WHERE order_ts >= (DATE '2025-06-01' AT TIME ZONE 'America/New_York')
  AND order_ts <  (DATE '2025-07-01' AT TIME ZONE 'America/New_York')

This keeps the column bare and therefore indexable, and it is correct across DST transitions — daylight saving time, when the local clock shifts by an hour — where a naive - INTERVAL '4 hours' is wrong for part of the year. The assumption to state out loud in any report: a “day” or a “month” is only well defined once you name the timezone, and if the query and the reader disagree about which one, revenue moves between periods with nothing to show for it.

Dialect note. AT TIME ZONE exists in PostgreSQL and SQL Server, with different semantics in each.

PostgreSQL’s version is a type-changing operator, not a conversion. Applied to a timestamp it attaches the named zone and yields a timestamptz. Applied to a timestamptz it strips the zone and yields a local-clock timestamp. Both directions are spelled the same way, which is why it reads as a no-op the first few times you meet it.

MySQL’s equivalent is the three-argument CONVERT_TZ(dt, from_tz, to_tz), which needs the zone tables loaded or it returns NULL.

SQLite has no named-zone support at all. strftime('%s', ts, 'utc') and 'localtime' are the only modifiers, and “localtime” means the server’s, so a zone-correct boundary has to be computed in the application and passed in as a literal.

MySQL has no timestamptz — TIMESTAMP converts to UTC on write and back to the session timezone on read, while DATETIME stores no zone at all. Two sessions with different time_zone settings see different values from the same TIMESTAMP column. SQLite has no date type at all; dates are text, integers, or reals by convention.

Bucketing, and the missing-days bug

The question: “What is the daily revenue for June?” — and this is the case where a query with nothing whatsoever wrong with it produces a number off by a factor of seven.

SELECT DATE_TRUNC('day', order_ts) AS day, SUM(amount_cents) AS rev
FROM orders
WHERE order_ts >= TIMESTAMPTZ '2025-06-01+00' AND order_ts < TIMESTAMPTZ '2025-07-01+00'
GROUP BY 1 ORDER BY 1;

What came back — 4 rows, one per day that had an order:

day        |   rev
2025-06-01 | 50000
2025-06-15 | 20000
2025-06-20 | 12000
2025-06-30 | 30000

June has 30 days. The query cannot return June 2, because there is no row to group. Now compute the daily average from that result and compare it with the truth:

AVG over the returned rows:  112000 / 4  = 28000 per day
true average daily revenue:  112000 / 30 =  3733 per day

A 7.5x error produced by a query with no bug in it. The rows that should have been zero are absent instead, and every downstream AVG, moving average, and sparkline — a sparkline being the tiny inline trend chart on a dashboard — inherits the error.

The fix is a spine: generate the dense axis of every day in the range, then left-join the sparse data onto it. generate_series(start, stop, step) is PostgreSQL’s row generator, and it produces exactly that axis — here, 30 timestamps, one per day of June.

SELECT d::date AS day, COALESCE(SUM(o.amount_cents), 0) AS rev
FROM generate_series(TIMESTAMPTZ '2025-06-01 00:00+00',
                     TIMESTAMPTZ '2025-06-30 00:00+00',
                     INTERVAL '1 day') AS d
LEFT JOIN orders o ON o.order_ts >= d AND o.order_ts < d + INTERVAL '1 day'
GROUP BY 1 ORDER BY 1;

What came back: 30 rows, 26 of them zero. Now AVG(rev) over the result is 112000 / 30 = 3733, which is the number the question meant.

Two bits of PostgreSQL syntax in there. d::date is a cast — expr::type converts a value to another type, and here it drops the time-of-day from the generated timestamp so the label reads 2025-06-02. INTERVAL '1 day' is a duration literal, addable to a timestamp.

What this pattern assumes, and what breaks. The naive daily query assumes every day in the range produced at least one row. How badly that fails is decided by the density of your data: at one order per day the error is invisible, at four orders per month it is 7.5×.

The rule generalizes to any grouping key with an absent category — regions with no sales, statuses with no rows, cohorts with no retention. GROUP BY can only produce groups that exist in the data. If zero is a meaningful answer, you must supply the axis yourself.

The spine query has an assumption of its own: the join condition o.order_ts >= d AND o.order_ts < d + INTERVAL '1 day' must tile the axis exactly. Overlap the buckets and an order lands in two of them and is counted twice.

12. Deduplication

SQL offers several tools for removing duplicate rows. Knowing them is less important than knowing when not to reach for them: deduplicating to fix a join is the most common way people convert a visible problem into an invisible one.

Four tools, different semantics:

• DISTINCT removes rows that are identical across every selected column.
• GROUP BY with no aggregate does the same thing.
• DISTINCT ON (key) is a PostgreSQL extension that keeps one row per key.
• GROUP BY ... HAVING COUNT(*) > 1, at the end of this section, finds duplicates rather than hiding them.

They answer different questions. The first two answer “what regions exist?”; the third answers “what is each customer’s latest order?”

SELECT DISTINCT region FROM customers;               -- 3 rows: US, EU, APAC
SELECT region FROM customers GROUP BY region;        -- same 3 rows, identical plan in PostgreSQL
SELECT DISTINCT ON (customer_id) customer_id, order_id, order_ts
FROM orders
ORDER BY customer_id, order_ts DESC, order_id DESC;  -- PostgreSQL: one row per key

12a. DISTINCT is not a function

DISTINCT is not a function. SELECT DISTINCT(region), name FROM customers looks like it deduplicates region — the parentheses are decorative, and it actually deduplicates the pair (region, name). This is a real, frequent, silent bug. Run the two side by side on the five customers and the parentheses do nothing at all:

SELECT DISTINCT region FROM customers;          -- 3 rows: US, EU, APAC
SELECT DISTINCT(region), name FROM customers;   -- 5 rows: (US,Ada) (US,Bo)
                                                --         (EU,Cy) (EU,Di) (APAC,Ed)

DISTINCT is a modifier on the whole SELECT list. So the second query is SELECT DISTINCT region, name with decorative parentheses around the first column.

It returns 5 rows because no two customers share both a region and a name. Nothing collapses, and the answer to “what regions exist?” comes back with two extra rows and a column nobody asked for.

That is the shape of the bug. At this size you notice. On a table where a few pairs happen to coincide, you get a count that is neither the number of regions nor the number of customers, with no clue which.

12b. DISTINCT ON, and the tiebreaker it needs

DISTINCT ON (expr) keeps the first row per expr in ORDER BY order. It is PostgreSQL-only and is the most concise top-1-per-group in existence.

The ORDER BY must lead with exactly the DISTINCT ON expressions, then the tiebreaker that defines “first”. That is why the query above ends order_ts DESC, order_id DESC and not just order_ts DESC. order_ts alone says “the latest”; order_id DESC says which one wins when two orders share a timestamp. Without it, the engine is free to return either, and to choose differently on different runs.

What came back from the DISTINCT ON query above — three rows:

customer_id | order_id | order_ts
          1 |     1002 | 2025-06-15 12:00Z
          2 |     1003 | 2025-06-30 22:00Z
          3 |     1004 | 2025-07-02 08:00Z

Only three rows, because only three customers have ordered at all. DISTINCT ON reads orders, so customers 4 and 5 are simply not in its input. Left-joining from customers is form (3) in Top n per group three ways the fourth is chapter 02.

12c. DISTINCT as a fan-out band-aid

This is the anti-pattern to name in an interview. Someone sees duplicate rows from a join and adds DISTINCT. Two things happen, and only the first is visible.

(a) The duplicates from the join collapse, so the row list looks right. Any SUM is still computed over the fanned-out rows, though, unless the DISTINCT is inside the aggregate. The list is fixed; the numbers are not.

(b) Rows that were genuinely distinct in the source, but identical in the projection, also collapse. If an order has two line items with the same sku, qty, and unit_price — two of the same widget, entered as two lines — then SELECT DISTINCT sku, qty, unit_price returns one. The count is now wrong in the other direction, and nothing indicates it.

The assumption in one line: DISTINCT assumes any two identical rows are the same fact recorded twice, and real tables are full of identical rows that are two genuinely different events. Fix the grain (3b the pre aggregation fix); do not paper over it.

12d. Counting distinct values

COUNT(DISTINCT a, b) is not PostgreSQL syntax. Use a row constructor, (a, b), which packages several values into one composite value the engine can compare as a unit: COUNT(DISTINCT (a, b)). (MySQL does accept the two-argument spelling.)

COUNT(DISTINCT x) is also expensive. It must retain every distinct value it has seen so it can recognize repeats, where COUNT(*) is a single counter.

For approximate work at scale, use HyperLogLog: a sketch that estimates how many distinct values a stream contained while storing only a small fixed-size summary rather than the values themselves. It trades roughly 2% error for constant memory. PostgreSQL gets it through the postgresql-hll extension; BigQuery exposes it as APPROX_COUNT_DISTINCT.

12e. Finding the duplicates instead of hiding them

The question: “which (customer, timestamp) pairs appear more than once, and which row should I keep?” HAVING COUNT(*) > 1 is the line that keeps only the groups with a duplicate in them.

SELECT customer_id, order_ts, COUNT(*) AS n, MIN(order_id) AS keep_id
FROM orders GROUP BY customer_id, order_ts HAVING COUNT(*) > 1;

What came back on this data: zero rows. No customer placed two orders at the same instant, so there is nothing to clean up — which is exactly the answer you want from an audit query, and the same “zero rows” that 2b not in with a null returns zero rows always warned you not to trust blindly. The difference is that here you can point at the reason: MIN(order_id) would have named the survivor if a group had more than one row.

The ROW_NUMBER form — the one that generalizes to “keep the newest per key” — is De duplication with row_number.

13. Top-N per group, three ways (the fourth is chapter 02)

The latest row per key is the most common interview question in analytical SQL. It comes in three forms here, with a fourth waiting in chapter 02 — and which one to reach for is decided by the shape of the data, not by taste.

The question: “The most recent order per customer.”

Three forms follow. Read the FROM clause of each: forms (1) and (2) start from orders, so they can only produce customers who ordered. Form (3) starts from customers, so it produces all five.

-- (1) correlated subquery on the ordering column
SELECT o.* FROM orders o
WHERE o.order_ts = (SELECT MAX(o2.order_ts) FROM orders o2
                    WHERE o2.customer_id = o.customer_id);

-- (2) DISTINCT ON  (PostgreSQL)
SELECT DISTINCT ON (customer_id) * FROM orders
ORDER BY customer_id, order_ts DESC, order_id DESC;   -- order_id DESC = the tiebreaker

-- (3) LATERAL  (generalizes to N > 1)
SELECT c.customer_id, l.* FROM customers c
LEFT JOIN LATERAL (SELECT * FROM orders o WHERE o.customer_id = c.customer_id
                   ORDER BY o.order_ts DESC LIMIT 1) l ON true;

What came back. Forms (1) and (2) each returned three rows — orders 1002, 1003 and 1004, the latest for customers 1, 2 and 3. Both select *, so the real output is every orders column; three of them are shown here.

customer_id | order_id | order_ts
          1 |     1002 | 2025-06-15 12:00Z
          2 |     1003 | 2025-06-30 22:00Z
          3 |     1004 | 2025-07-02 08:00Z

Form (3) returned five rows — the same three, plus customers 4 and 5 with every orders column NULL. Pick the form whose row count answers your question.

The table below compares all three, plus a fourth from the next chapter:

The fourth form is ROW_NUMBER, a window function that numbers the rows within each group in an order you specify. “Keep the row numbered 1 per customer” gives the same answer. It is De duplication with row_number.

What these patterns assume, and what breaks. All four assume the ordering column determines a unique winner — that no customer has two orders at the identical timestamp.

Form (1) silently returns more rows than groups when the ordering column has ties. Two orders at the same timestamp for one customer produce two rows, and a downstream join then fans out into the 3a worked cardinality failure.

Forms (2), (3), and (4) break ties by whatever you put after the leading ORDER BY keys. Add a unique tiebreaker such as order_id DESC and the result becomes deterministic.

Without one, those three still return one row per customer — but which row can change between two runs of the identical query. That is the kind of bug that reproduces only in production.

The performance split is worth stating: LATERAL is groups × index-lookup; ROW_NUMBER is one sort over the whole table. With 1,000 customers and 50M orders and an index on (customer_id, order_ts DESC), LATERAL touches ~1,000 index entries and ROW_NUMBER sorts 50M rows. With 50M customers and 2 orders each, the window function wins.

14. Pagination — why OFFSET degrades, and the fix

Handing a large result set back to a caller twenty rows at a time looks like the easiest job in this chapter. The obvious way gets slower with every page — and can hand back the same row twice.

To paginate is to return one page of results at a time. LIMIT 20 says “at most twenty rows”; OFFSET 100000 says “skip the first hundred thousand of them”. The question: “Give me page 5,001 of the orders, newest first.”

SELECT * FROM orders ORDER BY order_ts DESC, order_id DESC LIMIT 20 OFFSET 100000;

OFFSET n does not skip work; it does the work and throws it away. The engine must produce the first n rows in sorted order to know which row is number n+1. Fetching page k costs proportional to 20k, so paging through the whole table costs:

rows produced to deliver K pages = sum over k=1..K of 20k = 20 · K(K+1)/2 ≈ 10 K^2

250,000 rows at 20 per page is K = 12,500 pages:

OFFSET pagination:  20 · 12500 · 12501 / 2  = 1,562,625,000 rows produced
keyset pagination:                             250,000 rows produced
                                               ------------------------
                                               6,250x

1.56 billion rows produced to deliver 250 thousand. And the last pages are the slowest, so the experience degrades exactly as the user gets more committed.

flowchart LR
    subgraph OFF["OFFSET 100000 LIMIT 20"]
        O1["sort / scan in order"] --> O2["produce 100,020 rows"] --> O3["discard 100,000"] --> O4["return 20"]
    end
    subgraph KEY["keyset: WHERE (ts,id) < (last_ts,last_id)"]
        K1["index seek to the cursor"] --> K2["read 20 rows"] --> K3["return 20"]
    end

    style O3 fill:#9d0208,color:#fff
    style K1 fill:#2d6a4f,color:#fff

The two halves of that diagram are the whole argument. On the OFFSET 100000 LIMIT 20 side the engine must do the expensive thing the first box names — sort / scan in order — then produce 100,020 rows to discover where row 100,001 begins, discard 100,000 of them, and return 20. On the keyset: WHERE (ts,id) < (last_ts,last_id) side the entire plan is three steps — index seek to the cursor, read 20 rows, return 20 — and no row is produced that the caller does not see.

Keyset pagination — also called cursor or seek pagination — remembers the last row instead of counting rows. A cursor here is simply the sort-key values of the last row you showed, handed back to you on the next request:

-- page 1
SELECT order_id, order_ts, amount_cents FROM orders
ORDER BY order_ts DESC, order_id DESC LIMIT 20;

-- page n+1: pass the last row of page n as the cursor
SELECT order_id, order_ts, amount_cents FROM orders
WHERE (order_ts, order_id) < (TIMESTAMPTZ '2025-06-15 12:00+00', 1002)
ORDER BY order_ts DESC, order_id DESC LIMIT 20;

The row-constructor comparison (a, b) < (x, y) is lexicographic — compared the way words are ordered in a dictionary, first by a, and only when the as are equal by b — and it maps directly onto a composite index, an index built on several columns in a stated order, here (order_ts DESC, order_id DESC): one index seek, 20 rows read, constant cost regardless of page number. (Writing it as ts < x OR (ts = x AND id < y) is logically the same but many planners handle it worse.)

The second, less-discussed argument for keyset is correctness. OFFSET addresses rows by position in a result set that other transactions — other users’ units of work, running against the same table at the same time — are mutating between your requests.

Walk through what that does to a reader paging through a live table:

you read page 5 (rows 81-100)
someone inserts a row that sorts at position 40
you read page 6 (rows 101-120)
   -> the old row 100 has shifted to 101 and you see it TWICE
   (a delete above your position makes you SKIP a row instead)

A cursor addresses a row, not a position, so concurrent inserts and deletes above the cursor change nothing about what you see next. OFFSET pagination over a live table can duplicate and drop records with no error anywhere — which matters enormously when the “pagination” is an export job or a backfill loop.

What this pattern assumes, and what breaks. OFFSET assumes the underlying result is stable for the whole time the client is paging through it. That holds in a test with one user and fails on any table receiving writes, producing duplicated and skipped rows with no error to catch.

Keyset assumes the sort key is a total ordering, meaning no two rows tie on it. Both forms need this. Always append a unique column to ORDER BY, or ties make the boundary between pages ambiguous and rows go missing on their own.

The trade-off: keyset cannot jump to page 47, because it has no way of knowing where page 47 begins without counting. If the product requires numbered pages, OFFSET on a bounded, filtered result is acceptable; for infinite scroll, feeds, APIs, and any batch job, keyset is strictly better.

15. Ordered-set aggregates — percentiles

Medians and 95th percentiles are what you report when an average would mislead. SQL computes them with two closely related functions whose one difference matters — and the result, unlike a sum, cannot be re-aggregated.

A percentile (or quantile) is the value below which a given fraction of the data falls. The 50th percentile, written p50, is the median: half the values are below it. p90 is the value 90% of the data falls below, and p99 the value 99% falls below.

In latency work these are the standard summaries, because they describe the experience of the unluckiest users — which an average hides.

The functions that compute them are called ordered-set aggregates, because unlike SUM they need their input in sorted order. The WITHIN GROUP (ORDER BY ...) clause is what supplies that order.

The question: “What is the median order value, and the 90th percentile?”

SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount_cents) AS p50,
       PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY amount_cents) AS p90,
       PERCENTILE_DISC(0.9) WITHIN GROUP (ORDER BY amount_cents) AS p90_disc,
       AVG(amount_cents) AS mean
FROM orders;

The five order amounts, sorted, are 12000, 15000, 20000, 30000, 50000. What came back, with the arithmetic spelled out:

p50       = 20000                                   (exact middle)
p90 cont  : index = 0.9·(5-1) = 3.6
            interpolate between sorted[3]=30000 and sorted[4]=50000
            30000 + 0.6·(50000-30000) = 42000
p90 disc  = 50000        (first actual value whose cumulative fraction >= 0.9)
mean      = 25400

The _CONT calculation treats the five sorted values as points on a continuous line and lands between two of them: position 0.9 × (5-1) = 3.6 is 60% of the way from the fourth value to the fifth, so it interpolates — reads off the value at that fractional position, 42000, which no order actually had. The _DISC calculation instead walks the sorted values until the cumulative fraction reaches 0.9 and returns the real value it stopped on.

PERCENTILE_CONT interpolates and can return a value that appears nowhere in your data; PERCENTILE_DISC returns an actual observed value. Use _DISC when the quantity is discrete or the value must be real (a median order id, a representative row); use _CONT for latency percentiles and anything continuous.

Median (20000) versus mean (25400) on five rows already shows the skew — a distribution with a long tail on one side, so that a few large values drag the average above the typical one — that makes AVG the wrong summary for latency, order value, and session length. It is the same mean-versus-median argument as Every metric names an optimal prediction, where the mean is the prediction that minimizes squared error and the median the one that minimizes absolute error.

Percentiles do not average and do not add. The p95 of the union of two services is not the mean of their p95s, and a “daily p99” cannot be rolled up into a “monthly p99”.

To roll up, you need the raw distribution, or a structure that summarizes it while staying mergeable. Two such structures: a histogram, which is counts per value-bucket and can be added bucket by bucket, and a t-digest, a compact sketch that keeps more resolution in the tails where percentiles are read.

The assumption people make and should not: that a percentile behaves like a sum under re-aggregation. It does not. A monthly p99 computed as the average of thirty daily p99s is simply a different number, with no interpretation.

Dialect note. PERCENTILE_CONT and PERCENTILE_DISC as ordered-set aggregates — the WITHIN GROUP (ORDER BY ...) spelling used above — are in PostgreSQL 9.4+ and Oracle. SQL Server has the same two functions but only as window functions: it requires a trailing OVER (...) clause and returns one value per input row rather than one per group. MySQL and SQLite have neither form. Emulate with ROW_NUMBER (chapter 02): number the sorted rows, then pick the one at the position the percentile names.

16. The 20 patterns

Every pattern in the chapter, in one table: the situation that calls for it, and the wrong answer you get when its assumption about the data does not hold. Each trap is what happens when uniqueness, nullability, or cardinality is not what the query assumed — every entry is one of those three.

The one-line version of the chapter: NULL is unknown in a filter and just-another-value in a group, so <> and NOT IN delete rows without telling you; a join’s cardinality is sum over keys of n_L(k)·n_R(k) so any aggregate above a one-to-many edge is multiplied; and clauses evaluate FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY -> LIMIT, which is why aliases and aggregates are visible exactly where they are and nowhere else.

Next: 02 — Window Functions — the clause that computes over a frame without collapsing rows, and the one fact about when it runs that resolves most of the confusion around it.