SQL logic has three values, not two, and NULL is the third one leaking into a query written as if there were only TRUE and FALSE. Every trap here has shipped a wrong number that looked clean: a result grid with a plausible total in it, formatted and sent upward with confidence, because nothing errors and nothing warns. The rules are short. The consequences are everywhere. All examples below run on CellarDB, the winery dataset for this series; the schema and load script are on the setup page.
Three values
A comparison in SQL returns TRUE, FALSE, or UNKNOWN. Any comparison involving NULL returns UNKNOWN: Brix = NULL is UNKNOWN, Brix <> NULL is UNKNOWN, and NULL = NULL is UNKNOWN, because NULL is a claim of ignorance and you cannot say whether one unknown thing equals another. IS NULL and IS NOT NULL exist because equality can't do the job.
The part that does the damage: WHERE keeps a row only when its condition evaluates to TRUE. UNKNOWN is not TRUE, so rows with UNKNOWN conditions are dropped, silently, exactly like rows that are FALSE. Most of this post is that one sentence wearing different disguises.
The filter that excludes rows it never mentions
WorkOrders.CellarHand is nullable. Some cellar operations get logged without a name on them. Now count the work done by anyone other than Ramos:
SELECT WorkOrderID, WorkType, ScheduledDate, CellarHand
FROM WorkOrders
WHERE CellarHand <> 'Ramos';The query reads as "everyone except Ramos." It means "every row whose cellar hand is known and isn't Ramos." For a NULL row, CellarHand <> 'Ramos' is UNKNOWN, and the row is gone. A labor report built on this undercounts by every unsigned work order, and the people reading it have no way to know.
If unsigned work should count, say so:
WHERE CellarHand <> 'Ramos' OR CellarHand IS NULLThe fix is one clause. The discipline is deciding, before you filter, what NULL means in this column. "Nobody logged it" is not the same fact as "someone other than Ramos did it," and the query has to pick one.
NOT IN against a nullable column returns nothing
A reasonable question after harvest: which fruit deliveries never became a wine lot?
SELECT FruitLotID, PickDate, Tons
FROM FruitLots
WHERE FruitLotID NOT IN (SELECT FruitLotID FROM WineLots);Zero rows. Which looks like good news: every delivery accounted for. It isn't. About fifteen rows in WineLots are purchased bulk wine with no fruit lot behind them, so WineLots.FruitLotID contains NULLs, and NOT IN expands to a chain of inequalities:
FruitLotID <> 101 AND FruitLotID <> 102 AND FruitLotID <> NULL AND ...That third comparison is UNKNOWN for every row, and an AND chain containing UNKNOWN can never be TRUE. The whole filter fails for the whole table. No warning, no hint. Worse, the query worked perfectly in every database that happened to have no NULLs in the subquery, then went dark the day the first purchased lot was entered.
The fix is to ask for absence directly:
SELECT f.FruitLotID, f.PickDate, f.Tons
FROM FruitLots f
WHERE NOT EXISTS (
SELECT 1
FROM WineLots w
WHERE w.FruitLotID = f.FruitLotID
);NOT EXISTS asks "is there a matching row," and a NULL key never matches, which is what you meant. I write NOT EXISTS for every anti-join and have stopped negotiating with NOT IN. The positive form, IN, doesn't have this failure; NULLs in the list can't produce false matches. Only the negation breaks.
Joins don't match NULL, and INNER JOIN won't tell you
The same fifteen purchased lots break a different report. Cellar volume by vineyard block:
SELECT vb.BlockCode, vb.VineyardName, SUM(wl.VolumeGal) AS CellarGal
FROM WineLots wl
JOIN FruitLots fl ON fl.FruitLotID = wl.FruitLotID
JOIN VineyardBlocks vb ON vb.BlockID = fl.BlockID
WHERE wl.Status IN ('Aging', 'Blended')
GROUP BY vb.BlockCode, vb.VineyardName;A NULL join key matches nothing, so every purchased lot falls out at the first join and the report understates the cellar by every purchased gallon. Each block's number is correct. The total is wrong, and nothing in the grid says so.
If the report is "estate production by block," fine; put that in the title and move on. If it claims to be the cellar, keep the lots and label them:
SELECT COALESCE(vb.BlockCode, 'Purchased bulk') AS Source,
SUM(wl.VolumeGal) AS CellarGal
FROM WineLots wl
LEFT JOIN FruitLots fl ON fl.FruitLotID = wl.FruitLotID
LEFT JOIN VineyardBlocks vb ON vb.BlockID = fl.BlockID
WHERE wl.Status IN ('Aging', 'Blended')
GROUP BY COALESCE(vb.BlockCode, 'Purchased bulk');Note what COALESCE is doing here: labeling. It changes how a row is described, not what gets counted. That distinction matters in a minute.
Aggregates skip NULLs and say so where nobody looks
FruitLots holds about 320 deliveries. Roughly 25 of them are night machine picks that arrived unsampled, because the crew was picking at 2 a.m. and the lab opens at 7. Their Brix is NULL.
SELECT Vintage,
COUNT(*) AS Deliveries,
COUNT(Brix) AS BrixSamples,
AVG(Brix) AS AvgBrix
FROM FruitLots
GROUP BY Vintage;COUNT(*) counts rows. COUNT(Brix) counts rows where Brix is not NULL, so the two columns disagree, and AVG(Brix) divides by the smaller number. That is usually what you want: the average of the measured deliveries. But it answers "average Brix of sampled fruit," not "average Brix of the vintage," and the person reading the report deserves to know which question got answered. COUNT(DISTINCT col) and the denominator of every other aggregate behave the same way.
The same mechanism produces a classic false alarm. Sum the component percentages of each blend in BlendComponents and two blends come back around 92 instead of 100. It looks like the blend sheet has a math error. It doesn't; those blends carry trial components whose ComponentPct was never assigned. The data is fine. The sum just doesn't mean "the whole blend" once NULLs are in the column.
SQL Server does disclose all of this, once, in a place almost nobody reads: the Messages tab, "Warning: Null value is eliminated by an aggregate or other SET operation." The grid looks finished. The warning is the only witness, and it only appears when ANSI_WARNINGS is on, which it is by default. If you see that message and can't say which column triggered it, stop.
COALESCE defaults that lie
The instinct, on meeting NULLs, is to clean them up: COALESCE(col, 0) and the query runs quiet. This is the politest way to ship a wrong number.
AVG(COALESCE(Brix, 0)) tells the engine those 25 night picks came in at zero Brix. There is no such grape. The vintage average drops by a couple of points, the winemaker wonders why the fruit looks underripe on paper, and the report has manufactured a fact. The unmodified AVG(Brix) was more honest: average of what we measured, sample size disclosed in the next column.
The labor version is more expensive. Cost out lots using COALESCE(LaborHours, 0) and every unlogged work order becomes free labor. The lots with the sloppiest record-keeping become your cheapest lots, and someone allocates overhead off that.
My rule: replace NULL with a value only when that value is the truth, not a placeholder. A contract with no shipments has shipped zero gallons; zero is the fact, and COALESCE is earned. An unsampled delivery does not have zero Brix; zero is a lie with good posture. Keep COALESCE in the final SELECT, where it labels and formats, and out of the arithmetic. If removing a COALESCE changes an aggregate, you were not formatting. You were inventing data.
IS NOT DISTINCT FROM
Occasionally you need the opposite behavior: NULL treated as equal to NULL. The usual case is comparing two copies of the same data, say a re-import from the lab system sitting in a staging table, looking for rows that actually changed:
SELECT s.FruitLotID
FROM FruitLots_Staging s
JOIN FruitLots f ON f.FruitLotID = s.FruitLotID
WHERE s.Brix IS DISTINCT FROM f.Brix
OR s.pH IS DISTINCT FROM f.pH;IS DISTINCT FROM is null-safe comparison: two NULLs count as the same, NULL against a value counts as different, which is exactly what "did it change?" means. It arrived in SQL Server 2022, so check your version. On older servers the workhorse trick is that INTERSECT and EXCEPT also treat NULLs as equal, which is why you'll see veterans diff tables with EXCEPT instead of WHERE clauses full of ISNULL.
The question to ask
For every column you filter on, join on, or aggregate: can it be NULL, and what does NULL mean in this table? Unknown? Not applicable? Not yet? The setup page documents which CellarDB columns are nullable and why. Production schemas owe you the same documentation and almost never provide it, so you check: SELECT COUNT(*) - COUNT(col) FROM table is the fastest census of what you're dealing with.
Every query in this post runs without error and returns a grid that looks finished. One of them admits something is off, in a Messages tab nobody reads. The rest keep it to themselves. Ask the question yourself, because the engine mostly won't.
— Lo