NULL propagates, it does not fail

A lesson from Green Pipelines, Wrong Numbers. Play it above, or read it through below.

• Unknown is not false, and a WHERE clause keeps neither

  The common read: A comparison against NULL comes back false, so a row holding a NULL fails the test and every other row behaves normally.

  Name the value 7 = NULL evaluates to, then read on. Not false. The comparison chapter is flat: ordinary comparison operators yield null, signifying unknown, not true or false, when either input is null; 7 = NULL and 7 <> NULL are its own two examples. Three truth values, not two: true, false, unknown. That is three-valued logic, and this lesson is its cost. A WHERE clause keeps rows whose predicate came back true, so unknown and false are discarded together, indistinguishable from a row that was never there.

  It lands hardest on the expression that looks like it finds NULLs. Write where plate = null against the permits table and the count is 0, including the permit that genuinely has no plate, whose predicate is unknown rather than true. The documentation says not to write it, "because NULL is not 'equal to' NULL". IS NULL is the predicate that answers: 1 for the same table. The figure's last two columns escape it, and are the only expressions that committed to an answer; S6 names them.

  The parking-permit office runs a nightly check for permits issued without a plate on file, spelled = null, and it has reported zero every night since the day it was written. The permit sitting in the table with no plate has never appeared on it. That one surfaced as an audit finding about uninspected permits, filed against the clerk who runs the check.

• One NULL in the list and NOT IN stops answering. It does not fail.

  The common read: A NULL in the excluded list is a row that matches nothing, so NOT IN steps over it and returns the rows that do not match the rest.

  NOT IN does not step over the NULL. It expands into a chain of inequalities joined by AND, one per subquery row, and the conjunct from the NULL row is unknown, which drags the whole conjunction to unknown for every value not otherwise matched. The documentation says so in one sentence: if the left-hand expression yields null, or if there are no equal right-hand values and at least one right-hand row yields null, the result of the NOT IN construct will be null, not true.

  Two more facts from the same page make the shape of the failure legible, and both are on the figure. The first is the asymmetry, which is why nobody notices: IN is unaffected for the rows that match, because true OR unknown is true, so the positive half of every report keeps working and only the negative half empties.

  The second looks worse than it is. NOT IN against a subquery returning no rows is documented as true: the result is true "if only unequal subquery rows are found (including the case where the subquery returns no rows)". An empty citations table hands back all six permits; one null plate hands back none. The multi-column form carries the same defect one level up: if all the per-row results are either unequal or null, with at least one null, the result of NOT IN is null.

  The office's compliance report comes back empty. citations.plate is nullable, because a handheld scanner has to file a citation when it cannot read a plate, and one citation in ten carries no plate. The report did not error and the count is a legal count, so the ticket was raised against the enforcement dashboard for showing an empty table. Where that NULL came from is S15's question.

• NOT EXISTS never returns unknown. That is the whole of the difference.

  The common read: I filtered the NULLs out of the subquery, so NOT IN is safe again: the two spellings are one anti-join, and picking between them is style or speed.

  The filter repaired one clause of two, not the construct. They are not two spellings of one thing, and the difference is not performance. NOT IN evaluates a chain of comparisons and reports what that chain came to, which can be unknown. NOT EXISTS evaluates whether a subquery produced at least one row and reports the negation, and a count of rows is always a definite number, so the predicate is true or false and never unknown.

  The comparison inside the correlated subquery can go unknown all it likes: an unknown row qualifies nothing, so an outer row whose every comparison is unknown produces an empty subquery, and NOT EXISTS answers true. That is why permit 6 comes back from the second spelling and not from the first, and why the two differ on both sides of the comparison rather than only on the subquery side.

  The scalar version of the same move is IS DISTINCT FROM, and it is the one to reach for when NULL is a value you want to compare rather than exclude. a IS DISTINCT FROM b is <> for non-null inputs, returns true when exactly one side is null, and returns false when both are, so the docs describe the pair as acting "as though null were a normal data value, rather than 'unknown'".

  A change-detection query written with <> reports nothing for a value that went from NULL to NULL and nothing for a value that went from 40 to NULL, because both comparisons come back unknown. The same query written with IS DISTINCT FROM reports the second and not the first, which is what anyone asking meant. Nightly change detection that ignores every transition into and out of NULL is what reaches a finance reconciliation, filed against the warehouse load.

  NOT IN ask: not equal this, not equal that. One NULL in list. Unknown. Never true. Zero rows. No error. Green. Use NOT EXISTS. NULL cannot lie to NOT EXISTS.

• The rows with no value are gone before the arithmetic starts

  The common read: An aggregate reads every row in its group, so a missing value counts as zero and can only drag the number down.

  A missing value does not count as zero. It does not count at all. Most aggregate functions ignore null inputs, in the documentation's words, "so that rows in which one or more of the expression(s) yield null are discarded", and the manual adds that this holds for every built-in aggregate unless otherwise specified. Discarded is the operative word: the row is not zeroed, it is removed before the function sees it, so it leaves both the total and the divisor at once.

  So an average is not the total over the row count. AVG(fine_amount) is the sum of the non-null fines over their count, a divisor that differs per group and appears in no query anyone wrote. Over the whole citations table the two divisors give 64.29 and 45. Neither is wrong: they answer "what was the average fine actually recorded" and "what was collected per citation written", and only one is written down. An average that rises while collections fall gets filed against the reporting layer, traced to three warnings.

• COUNT(*) counts rows. COUNT(col) counts values. Nothing prints the difference.

  The common read: COUNT(col) and COUNT(*) are two ways of writing the same count, so the column name inside the parentheses is documentation for the reader.

  Put a number on it before reading on. The citations table holds ten rows, and count(fine_amount) over that same table returns a different number. The column name inside the parentheses is not documentation. It is the question. count(*) is documented as computing the number of input rows and count("any") as the number of input rows in which the input value is not null, and count(distinct f1) yields the number of distinct non-null values. Over this table the three answers are 10, 7 and 3.

  The gap between the first two numbers is the count of rows with no value there, the most useful completeness measure available, and no query returns it unless you ask for both. The third variant hides the most. count(distinct fine_amount) returns 3, and it would return 3 if nine hundred citations had no fine amount recorded, because the NULLs are discarded before the deduplication. A check reading "three distinct fine bands expected, three observed" passes in both worlds, and is filed months later as an audit finding.

• COUNT is the only aggregate that returns a number over nothing

  The common read: An aggregate over an empty group returns zero, so a group with nothing to add up shows up as a zero and the totals still add up.

  Say what sum(fine_amount) returns for an officer who wrote three citations and no fines, then read on. Not zero. The manual states the exception in one sentence: except for count, these functions return a null value when no rows are selected. It names the trap in the next clause, that sum of no rows returns null, not zero as one might expect, with coalesce offered as the substitution. COALESCE returns the first of its arguments that is not null, so wrapping the sum turns the blank into a 0.

  The discard rule from S9 makes this reachable without an empty table: a group whose every value is null has every row discarded, and is a group of no rows by the time the function runs. The consequence is not the blank cell but what it does next. NULL in an arithmetic expression propagates, a fact held since S1 about comparison and equally true of +, so a desk total of one officer's sum plus another's is null the moment either half had nothing to add up.

  What renders is not a wrong number but no number. The one aggregate that behaves is the count: three citations is three citations whether or not any carried a fine, and over an empty selection it is 0, not null. That exception is why nobody notices the rule: a real count beside the blank total makes the blank look like a rendering problem. The empty monthly revenue line goes to the BI tool as a display bug, and the two officers whose citations were all warnings never come up.

• Nothing in this lesson needs a bug. A cleanup is enough.

  The common read: My columns do not have NULLs in them, so none of this reaches my queries.

  Say where the citations table's NULL came from, then read on. Not from a bug. The handheld scanners wrote an empty string into plate when they could not read one, and that is a value: it compares, it is not null, and every NOT IN was quietly correct. Then a hygiene commit titled normalise blank plates wrapped it in NULLIF(plate, ''): a blank plate is not a plate. NULLIF returns a null value if its two arguments are equal and otherwise returns the first, exactly what the reviewer approved.

  With one NULL in the subquery, NOT IN returns

  The compliance report went from three permits to zero on the deploy, and the deploy took the blame: nothing in that diff had touched the report itself. That is the shape of every failure in this lesson: no exception, no warning, no exit code, no row count that looks wrong, and a change that a reviewer was right to approve.

  COALESCE is the inverse move and the reader has already met it, so hold the two as a pair: COALESCE takes a null and gives you a value, NULLIF takes a value and gives you a null, and both are one function call away from every construct on the last fourteen screens. The two questions that make this actionable are which columns in your schema became nullable in the last year, and which of your reports asks a negative question about them.