Write SQL You Can Re-read

The longest query I maintain right now is a bulk contract status report, a little over 200 lines. Nothing in it is clever. It is a stack of small steps, each one named, each one checked when it was added, and that is the whole trick. Anyone on the team can open it, find the stage they care about, and change it without fear of the other 180 lines.

Two years ago I made this argument about Excel in Format Your Excel Formulas Like Code: the logic was fine, the formatting made it untouchable, and the fix was line breaks and indentation that mirror the structure. SQL deserves less sympathy. Excel actively pushes you toward a single horizontal line; SQL has always let you format freely, so a wall of unindented SELECT is a choice. This post is the set of conventions I settled on after years of writing reports that other people had to maintain.

Everything below runs on CellarDB, the series dataset (setup page).

CTEs are paragraphs

A long query should read the way a document reads: top to bottom, one idea per block. CTEs are the blocks. Each one does a single thing you can name, builds only on the blocks above it, and produces a result you could inspect on its own. The chain of names becomes a table of contents. When you come back in three months, you read the names, descend into the one stage you need, and ignore the rest.

The alternatives all read worse. Nested derived tables force you to read inside-out, starting from the deepest subquery and climbing back up while holding everything on a mental stack. A single giant SELECT with six joins and a twelve-condition WHERE clause is the unformatted Excel formula again, only longer.

One habit makes the structure carry real information: the first line inside every CTE is a comment stating its grain. The grain question has done steady work since the modeling post in this series, and this is where it pays off for readers. Someone who knows what one row means in each block can follow every join below it.

WITH amendment_ranks AS (
    -- one row per amendment, newest first within its contract
    SELECT
        ContractID,
        NewVolumeGal,
        NewPricePerGal,
        ROW_NUMBER() OVER (
            PARTITION BY ContractID
            ORDER BY AmendmentDate DESC, AmendmentID DESC
        ) AS rn
    FROM ContractAmendments
),
latest_amendments AS (
    -- one row per amended contract
    SELECT ContractID, NewVolumeGal, NewPricePerGal
    FROM amendment_ranks
    WHERE rn = 1
)

Two CTEs where one would technically do, and that is deliberate. "Rank the amendments" and "keep the latest" are two different ideas, so they get two different names.

Names do real work

latest_amendments is documentation. cte2 is a guess you force on every future reader, including yourself. The same goes for computed columns: remaining_gal tells you what the number is, diff tells you someone was in a hurry.

Table aliases can stay short if they stay consistent. I use the same alias for a table in every query I write: wl is always WineLots, bc is always BulkContracts, bf is always BarrelFills. Short aliases are not the problem. The problem is a, b, and c assigned in join order, different in every query, so that reading the SELECT list requires scrolling back to the FROM clause every few seconds.

Settle the formatting arguments once

Mine, stated as defaults rather than laws: keywords uppercase, one expression per line in the SELECT list, each JOIN on its own line with the ON condition indented beneath it, trailing commas. Yours can differ on every point. The win is not any particular convention; it is never spending attention on the question again. A team that argues about comma placement in code review is spending its disagreement budget on the wrong things.

Build long queries in stages

This is where formatting becomes engineering. A 200-line query is never written as 200 lines and then run. It is grown: add one CTE, check it, add the next. The check is cheap because a well-named CTE is independently runnable; you point a throwaway SELECT at the newest block and confirm it before building on it.

The condensed version of my contract report. The goal: one row per open bulk contract, with effective terms after amendments, volume shipped to date, and the remainder.

Stage one is the amendment pair from above. Before moving on, swap the final SELECT for a check:

-- check: one row per amended contract, no more
SELECT COUNT(*) FROM latest_amendments;
SELECT COUNT(DISTINCT ContractID) FROM ContractAmendments;

The two numbers must agree. If they ever don't, the tiebreaker in the ROW_NUMBER is wrong, and so is everything downstream.

Stage two overlays the amendments onto the original terms. Amendments in this schema change volume or price independently and leave the other NULL, so each column gets its own COALESCE — the amendment pattern from the grain post:

effective_terms AS (
    -- one row per contract: original terms overlaid with latest amendment
    SELECT
        bc.ContractID,
        bc.ContractNumber,
        bc.CustomerID,
        bc.Status,
        COALESCE(la.NewVolumeGal, bc.VolumeGal) AS effective_volume_gal,
        COALESCE(la.NewPricePerGal, bc.PricePerGal) AS effective_price_per_gal
    FROM BulkContracts AS bc
    LEFT JOIN latest_amendments AS la
        ON la.ContractID = bc.ContractID
)

The check: row count equals BulkContracts exactly, and the grain holds.

-- check: every contract exactly once
SELECT ContractID
FROM effective_terms
GROUP BY ContractID
HAVING COUNT(*) > 1;   -- must return nothing

Stage three aggregates shipments to the contract grain before any join touches them, which is the fan-out discipline from earlier in the series applied as a matter of routine rather than as a rescue:

shipped AS (
    -- one row per contract that has shipped anything
    SELECT
        ContractID,
        SUM(VolumeGal) AS shipped_gal
    FROM Shipments
    GROUP BY ContractID
)

The check here is conservation. Aggregation moves volume to a coarser grain; it cannot create or destroy any:

-- check: total volume survives the regrouping
SELECT SUM(shipped_gal) FROM shipped;
SELECT SUM(VolumeGal) FROM Shipments;

Only now does the final SELECT exist, and it is short, because the CTEs did the thinking:

SELECT
    et.ContractNumber,
    c.CustomerName,
    et.effective_volume_gal,
    et.effective_price_per_gal,
    COALESCE(s.shipped_gal, 0) AS shipped_gal,
    et.effective_volume_gal - COALESCE(s.shipped_gal, 0) AS remaining_gal
FROM effective_terms AS et
JOIN Customers AS c
    ON c.CustomerID = et.CustomerID
LEFT JOIN shipped AS s
    ON s.ContractID = et.ContractID
WHERE et.Status = 'Open'
ORDER BY et.ContractNumber;

One note on that COALESCE(s.shipped_gal, 0): the NULL post warned that COALESCE defaults can turn "unknown" into a confident wrong number. This one is safe because a contract absent from Shipments has shipped exactly zero gallons. The default is true, not convenient. Say so in a comment when it isn't obvious.

The production version of this report is the same shape with about nine CTEs. Each was validated the day it was added, so by the time the final SELECT runs, every block beneath it has already been trusted once. I leave the check queries in a comment block at the bottom of the script. The next person to touch it inherits them for free.

Name the window

The window functions post introduced the WINDOW clause; here is what it does for readability. When several columns share the same OVER clause, you define the window once, between HAVING and ORDER BY, and refer to it by name:

SELECT
    WorkOrderID,
    WineLotID,
    ScheduledDate,
    LaborHours,
    LAG(ScheduledDate) OVER lot_history AS prev_op_date,
    SUM(LaborHours) OVER (
        lot_history
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS lot_hours_to_date
FROM WorkOrders
WINDOW lot_history AS (PARTITION BY WineLotID ORDER BY ScheduledDate);

The running total still spells out its ROWS frame — the default-frame trap doesn't go away just because the window has a name — but the partition and ordering live in exactly one place. Change the tiebreaker once and every column that uses the window follows; below compatibility level 160 the clause won't parse, so copy the OVER clause the old way. Before this existed, I watched the same three-line OVER clause drift out of sync across five columns of a report, and the resulting bug looked like a data problem for half a day.

Every other post in this series has been about being right; this one is about staying right after the query outlives your memory of writing it. The reader you are formatting for is you, three months from now, with the context gone and a change due by Friday. Spend the extra minutes. That reader has no one else.

— Lo