Stop Thinking in Rows

For most of my career the unit of thought was the cell. Excel makes every row visible and every calculation a formula you drag down the column, and after enough years that becomes how you think: find the row, compute the thing, move to the next row. Fill-down is a loop you run by hand. It works because you can watch every row obey.

SQL asks for the opposite. A query describes the result you want, as one set, and the engine decides how to produce it. There is no "next row." Carrying the loop instinct into SQL doesn't usually produce wrong answers; it produces a particular style of query that works, then gets slow, then breaks in a specific and instructive way. This post is about recognizing that style in your own writing and what to replace it with. Queries run against CellarDB.

The loop you bring with you

The question: total labor hours per wine lot. The loop instinct, transcribed into T-SQL, is a cursor:

DECLARE @LotID INT, @Total DECIMAL(10,1);
DECLARE lot_cursor CURSOR FOR SELECT WineLotID FROM WineLots;
OPEN lot_cursor;
FETCH NEXT FROM lot_cursor INTO @LotID;
WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT @Total = SUM(LaborHours)
    FROM WorkOrders
    WHERE WineLotID = @LotID;
    -- ... store @Total somewhere ...
    FETCH NEXT FROM lot_cursor INTO @LotID;
END;
CLOSE lot_cursor;
DEALLOCATE lot_cursor;

The set version:

SELECT WineLotID, SUM(LaborHours) AS TotalHours
FROM WorkOrders
GROUP BY WineLotID;

Both produce the same numbers. The cursor visits a few hundred lots one at a time and runs a separate aggregation for each; the single statement hands the entire question to the engine, which is free to scan once, aggregate in one pass, and parallelize if it wants to. On CellarDB the difference is milliseconds. On a production table it's the difference between a report and a support ticket. Nobody writes many cursors after their first month, but the instinct that produces them survives and reappears in subtler clothing, which is the rest of this post.

The same loop, dressed as a subquery

Here's loop-thinking that looks like set code. For each lot, the date of its most recent completed work order:

SELECT wl.LotCode,
       (SELECT MAX(wo.CompletedDate)
        FROM WorkOrders wo
        WHERE wo.WineLotID = wl.WineLotID) AS LastWorkDate
FROM WineLots wl;

That's a correlated subquery: the inner query references the outer row, so logically it runs once per lot. It is a for each loop written in SELECT. And to be clear, this query is correct. It returns the right date for every lot, today and forever.

The case against it rests on two other things.

It's slow, conditionally. The optimizer can often rewrite a correlated subquery into a join and make the cost disappear, and sometimes it can't, and you get one probe of WorkOrders per lot. Which one you got is not visible in the query text. A query whose performance depends on an invisible rewrite is a query you can't make promises about.

It's fragile, structurally. Extend the question to "the work type of the most recent completed order" and the natural correlated version is:

SELECT wl.LotCode,
       (SELECT wo.WorkType
        FROM WorkOrders wo
        WHERE wo.WineLotID = wl.WineLotID
          AND wo.CompletedDate = (SELECT MAX(wo2.CompletedDate)
                                  FROM WorkOrders wo2
                                  WHERE wo2.WineLotID = wl.WineLotID)) AS LastWorkType
FROM WineLots wl;

A scalar subquery is allowed to return at most one row. The day two work orders on the same lot complete on the same date, this query stops returning anything and starts returning an error: Subquery returned more than 1 value. It ran clean for six months because the data happened to cooperate, and then the data stopped cooperating. That failure mode, correct until the data shifts under a single-value assumption, is the signature of row-thinking, and it's why I treat correlated scalar subqueries as a last resort rather than a wrong answer.

The set version states the question at the level of the whole table: rank each lot's completed orders, keep rank one.

WITH LastWork AS (
    SELECT WineLotID, WorkType, CompletedDate,
           ROW_NUMBER() OVER (PARTITION BY WineLotID
                              ORDER BY CompletedDate DESC, WorkOrderID DESC) AS rn
    FROM WorkOrders
    WHERE CompletedDate IS NOT NULL
)
SELECT wl.LotCode, lw.WorkType, lw.CompletedDate
FROM WineLots wl
LEFT JOIN LastWork lw
  ON lw.WineLotID = wl.WineLotID AND lw.rn = 1;

Notice the tiebreaker. ORDER BY CompletedDate DESC alone leaves the same-day case to chance; adding WorkOrderID DESC makes the answer deterministic. Ties don't break this version. They were considered at writing time, which is the whole point: set-thinking forces you to decide what the answer means for every row at once, including the awkward ones, instead of assuming each row is as tidy as the first one you pictured.

Correlation isn't the crime

Now the nuance that the "never write correlated subqueries" advice gets wrong. This is correlated, and it's excellent:

SELECT b.BlockCode, b.Variety
FROM VineyardBlocks b
WHERE EXISTS (SELECT 1
              FROM FruitLots f
              WHERE f.BlockID = b.BlockID
                AND f.Vintage = 2024);

Blocks that delivered fruit in 2024. EXISTS asks a yes-or-no question per block, which is exactly what the question is, and SQL Server compiles it to a semi-join rather than anything row-at-a-time. No single-value assumption exists to be violated, so the fragility from the previous section can't occur.

"Correlated" describes syntax, not a verdict. The thing to watch for is row-at-a-time thinking: queries that fetch a value per row when the question was really about groups, that assume one match where the data permits several, that bolt a join on to "grab one more column" without asking what the join did to the row population. That last habit causes wrong totals rather than errors, and it's serious enough that it gets its own post later in the series.

So the working defaults, stated as defaults: joins and GROUP BY for combining and summarizing, window functions for per-row answers that need group context, EXISTS for existence, correlated scalar subqueries last, and when one does earn its place, write it so a second matching row produces a planned outcome instead of a surprise.

Sets don't mean one giant statement

The misreading to avoid on the way out: set-based thinking doesn't require cramming everything into a single statement. A CTE pipeline is a sequence of sets, each one a complete, checkable intermediate result. Aggregate work orders to lots in one step, attach lot attributes in the next, summarize by vintage in the third. That's still set logic, applied in readable layers, and it's how anything over fifty lines should be built. The same applies to writes: one UPDATE with a join beats a loop of single-row updates by the same margin and for the same reasons as everything above.

The instinct you imported from spreadsheets took years to build and it won't dissolve in a month. The test I use on my own queries: describe the result in one sentence that starts with "one row per." If the sentence comes out starting with "for each... go find...", the query is probably a loop wearing SQL syntax, and there's a set formulation waiting underneath it.

Next post, in January: NULL, three-valued logic, and the ways unknown values silently reshape counts, averages, and NOT IN.

— Lo