SQL in an Afternoon

You can learn SQL's working syntax in an afternoon. This post is that afternoon: every construct you need for years of production work, each with the judgment that should travel with it. The hard part of the language lives elsewhere, in knowing what a query did to your data without telling you, and whether the number it returned deserves to go in front of anyone. That part took me a decade in operations and finance plus two years of writing queries, and it's what the other nine posts in this series are for. Syntax is a day's work. The rest of the series is why a day's work won't save you.

Everything below runs against CellarDB, the winery database the whole series shares. Set it up once; it takes fifteen minutes.

SELECT, FROM, WHERE

A query names columns, a table, and a condition:

SELECT BlockCode, Variety, Acres
FROM VineyardBlocks
WHERE Region = N'Russian River Valley'
ORDER BY Acres DESC;

The judgment: SELECT * is for exploring a table you've never met. In anything that feeds a report or another query, name the columns. A query that names its columns fails loudly when the schema changes; a SELECT * rearranges itself and keeps running.

For dates, my default is the half-open range:

SELECT FruitLotID, PickDate, Tons
FROM FruitLots
WHERE PickDate >= '2024-09-01'
  AND PickDate <  '2024-10-01';

BETWEEN '2024-09-01' AND '2024-09-30' returns the same rows today. Then someone alters the column to datetime, the 30th's afternoon picks fall outside the range, and nobody notices for a quarter. Half-open ranges survive type changes and never argue about month lengths. A default, not a law.

ORDER BY and TOP

Tables have no inherent order. A result set without ORDER BY arrives in whatever sequence was cheapest for the engine, which changes with indexes, parallelism, and the weather. Two consequences: never write TOP without ORDER BY, because "first ten rows" of an unordered set means "any ten rows"; and when the sort key has ties, add a tiebreaker if anything downstream cares about position.

SELECT TOP (10) LotCode, VolumeGal
FROM WineLots
ORDER BY VolumeGal DESC, LotCode;

GROUP BY and aggregates

GROUP BY collapses rows into groups and lets you compute one value per group:

SELECT b.BlockCode, f.Vintage, SUM(f.Tons) AS TotalTons, COUNT(*) AS Deliveries
FROM FruitLots f
JOIN VineyardBlocks b ON b.BlockID = f.BlockID
GROUP BY b.BlockCode, f.Vintage;

Every column in the SELECT must be either in the GROUP BY or inside an aggregate. The error message about that rule will be the most familiar text on your screen for a month, and it is the engine doing you a favor: it is refusing to guess which of several values you meant.

The judgment: before writing a GROUP BY, say out loud what one row of the output means. Here, "one row per block per vintage." If you can't finish that sentence, you aren't ready to aggregate. This habit matters more than any of the syntax on this page, and a later post is built entirely on it.

HAVING

WHERE filters rows. HAVING filters groups, which is why it can see aggregates:

SELECT wl.LotCode, COUNT(*) AS OpenOrders
FROM WorkOrders wo
JOIN WineLots wl ON wl.WineLotID = wo.WineLotID
WHERE wo.CompletedDate IS NULL
GROUP BY wl.LotCode
HAVING COUNT(*) >= 3;

That query reads: keep only open work orders, group them by lot, then keep only the lots with three or more. Put conditions in WHERE whenever they apply to individual rows; it filters earlier and reads honestly. Save HAVING for conditions that only exist after aggregation. The full reason for the split is the next post.

Joins

An inner join keeps the rows that match on both sides:

SELECT f.FruitLotID, f.PickDate, f.Tons, b.BlockCode, b.Variety
FROM FruitLots f
JOIN VineyardBlocks b ON b.BlockID = f.BlockID
WHERE f.Vintage = 2024;

A left join keeps every row from the left table and fills the right side with NULLs where nothing matched. That makes it the tool for "which of these have none":

SELECT wl.LotCode, wl.Vintage, wl.Status
FROM WineLots wl
LEFT JOIN BlendComponents bc ON bc.WineLotID = wl.WineLotID
WHERE bc.WineLotID IS NULL;

Wine lots that have never gone into a blend. The judgment: every join is a decision about which table owns the rows you can't afford to lose. An inner join is also a filter, and it filters silently. A join can drop rows and it can multiply them, and both failure modes are serious enough to get their own posts later in the series. For now, build the cheapest habit in SQL: note your row count before a join, check it after, and investigate any change you can't explain.

CASE

CASE is an expression, not a statement. It produces a value anywhere a value is legal:

SELECT LotCode, VolumeGal,
       CASE WHEN VolumeGal >= 5000 THEN 'Tank program'
            WHEN VolumeGal >= 1000 THEN 'Tank and barrel'
            ELSE 'Barrel program'
       END AS Program
FROM WineLots
WHERE Status = 'Aging';

Because it's an expression, it works inside aggregates, which is the single most useful trick on this page. A CASE with no ELSE returns NULL when nothing matches, and COUNT ignores NULLs, so this counts a subset without a second query:

SELECT b.Region,
       COUNT(*) AS Deliveries,
       COUNT(CASE WHEN f.Tons >= 25 THEN 1 END) AS HeavyDeliveries
FROM FruitLots f
JOIN VineyardBlocks b ON b.BlockID = f.BlockID
GROUP BY b.Region;

Conditional counts, shares, and pivot-shaped summaries all fall out of that one pattern.

CTEs

A common table expression names an intermediate result so the next step can read from it:

WITH BlockTons AS (
    SELECT BlockID, Vintage, SUM(Tons) AS TotalTons
    FROM FruitLots
    GROUP BY BlockID, Vintage
)
SELECT b.BlockCode, bt.Vintage, bt.TotalTons
FROM BlockTons bt
JOIN VineyardBlocks b ON b.BlockID = bt.BlockID
WHERE bt.TotalTons > 40;

The judgment: treat CTEs like paragraphs. Each one should do a single describable thing, named for what it holds rather than how it's built. BlockTons, not cte1. A 200-line query built as five named steps can be read and checked one step at a time; the same logic as one nested statement cannot. A post late in the series covers query style at full depth.

Window functions, briefly

A window function computes an aggregate without collapsing the rows:

SELECT LotCode, Vintage, VolumeGal,
       SUM(VolumeGal) OVER (PARTITION BY Vintage) AS VintageTotalGal,
       ROW_NUMBER() OVER (PARTITION BY Vintage ORDER BY VolumeGal DESC) AS SizeRank
FROM WineLots;

Every lot keeps its own row and also knows its vintage's total and its rank within it. GROUP BY answers questions about groups; window functions answer questions about rows in the context of their group, which is most of what analysis actually is. They deserve and will get a full post.

One thing to notice now: you cannot filter on SizeRank in this query's WHERE clause. Try it. The error you get is explained by the next post in the series, which covers the order SQL actually evaluates a query in. It is not the order you write it in, and that one fact accounts for most of the confusing errors you'll hit this month.

That's the working syntax. If you followed everything above, you can write production SQL today. Whether you can trust what it returns is a separate skill, and it's the one this series is actually about: evaluation order next month, then set-based thinking, NULL, join fan-out, grain, windows, style, validation, and performance. The dataset for all of it is already on your machine.

— Lo