One NULL in a NOT IN list returns zero rows

A lesson from Successful 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 evaluates to 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. It is not false. The PostgreSQL comparison chapter says: 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. SQL therefore uses three-valued logic: true, false and unknown. A WHERE clause keeps only rows whose predicate evaluated to true, so it discards rows that evaluated to unknown along with the false ones.

  The worst case is the expression that looks like it finds NULLs. where plate = null against the permits table counts 0, excluding the permit with no plate, because its predicate is unknown rather than true. The documentation says not to write it, "because NULL is not 'equal to' NULL". IS NULL works: it returns 1 for the same table.

  The figure's last two columns use IS DISTINCT FROM, which returns true or false even when an input is null. The section on NOT EXISTS later in this lesson explains it.

  The parking-permit office runs a nightly check for permits issued without a plate on file, written with = null, and the check has reported zero every night since the day it was written. The permit in the table with no plate has never appeared in the check's results. The office learned about that permit from an audit finding about uninspected permits, and the finding blamed the clerk who runs the check.

• One NULL in the list makes NOT IN return unknown, and nothing fails.

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

  NOT IN does not skip 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 makes the whole conjunction 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.

  IN still returns true for the rows that match, because true OR unknown is true, so only queries that list non-matching rows return nothing, and nobody notices. An empty subquery is safe: NOT IN is then true for every row, and the figure returns all six permits, plateless permit 6 included, because a null left-hand value makes the result null only when the subquery returns a row. For the multi-column form: if every per-row result is unequal or null, with at least one null, NOT IN returns null.

  The office's compliance report returns no rows. citations.plate is nullable, because a handheld scanner has to file a citation when it cannot read a plate, and one citation in ten has no plate. The report raised no error and the count is a valid count, so someone filed a ticket against the enforcement dashboard for showing an empty table. A later screen in this lesson covers where that NULL came from.

• NOT EXISTS never returns unknown, and that is how it differs from NOT IN.

  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. NOT IN and NOT EXISTS are not two spellings of one thing, and the difference is not performance. NOT IN evaluates a chain of comparisons and returns the result of that chain, 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 evaluate to unknown on any number of rows: a row where it is unknown does not qualify, so an outer row whose every comparison is unknown produces an empty subquery, and NOT EXISTS returns true. That is why the second spelling returns permit 6 and the first does not: NOT EXISTS handles a NULL on either side of the comparison, and filtering the subquery handles only the subquery side.

  IS DISTINCT FROM does for a comparison of two single values what NOT EXISTS does for a subquery: it never returns unknown. Use it when you want to compare NULL as a value rather than exclude it. 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 IS DISTINCT FROM and IS NOT DISTINCT FROM 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 evaluate to unknown. The same query written with IS DISTINCT FROM reports the second and not the first, which is what the person who wrote the query intended. When nightly change detection ignores every transition into and out of NULL, the missed changes appear in a finance reconciliation, and the team blames the warehouse load.

  NOT IN checks that the value is unequal to every row in the list. One NULL in the list means that check is never true for any value, so the query returns zero rows and raises no error. NOT EXISTS never evaluates to unknown, so use NOT EXISTS.

• PostgreSQL discards the rows with no value 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 lower the number.

  A missing value does not count as zero; it is not counted 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. The key word is "discarded": the row is not set to zero, it is removed before the function reads it, so it is removed from both the total and the divisor.

  An average is not the total over the row count. AVG(fine_amount) divides the non-null fines by their count, a divisor that differs per group and that nobody wrote. Over the whole table, 450 divided by 7 gives 64.29 and 450 divided by 10 gives 45. They answer "what was the average recorded fine" and "what was collected per citation written", and only the second divisor is in the query. A rising average with falling collections gets filed against the reporting team; the cause is three warnings with no fine.

• COUNT(*) counts rows, COUNT(col) counts non-null values, and no output shows 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.

  Predict the number 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 changes what is counted. 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 difference between the first two numbers is the count of rows with no value, the most useful completeness measure available, and no query returns it unless you ask for both. The third form 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 cases, and the problem appears months later as an audit finding.

• Of SUM, AVG, MIN, MAX and COUNT, only COUNT returns a number over nothing

  The common read: An aggregate over an empty group returns zero, so a group with nothing to add up appears 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. It is not zero. The manual states the exception in one sentence: except for count, these functions return a null value when no rows are selected. The next clause adds that sum of no rows returns null, not zero as one might expect, and offers coalesce as the substitution. COALESCE returns the first of its arguments that is not null, so wrapping the sum turns the blank into a 0.

  Because aggregates discard null inputs, this happens 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 bigger problem is what later arithmetic does with the blank. NULL propagates through arithmetic in the same way the first screen showed for comparison: + with a null operand returns null, so a desk total of one officer's sum plus another's is null as soon as either sum is null.

  The report shows no number rather than a wrong number. Of the common aggregates, only the count still returns a number: three citations is three citations whether or not any carried a fine, and over an empty selection it is 0, not null. A correct count beside the blank total makes the blank look like a rendering problem. The empty monthly revenue line is reported to the BI team as a display bug, and nobody mentions okafor, whose three citations were all warnings.

• A correct cleanup is enough to cause every failure in this lesson.

  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. No bug created it. The scanners wrote an empty string into plate when they could not read one. In PostgreSQL an empty string is a value (Oracle stores it as NULL): it is not null, and every NOT IN was correct. Then a cleanup commit titled "normalise blank plates" wrapped plate in NULLIF(plate, ''), because a blank plate is not a plate. NULLIF returns null if its two arguments are equal, otherwise the first.

  With one NULL in the subquery, NOT IN returns

  The compliance report went from three permits to zero on the deploy, and the team blamed the deploy, although nothing in that diff had changed the report itself. Every failure in this lesson has the same pattern: no exception, no warning, no exit code, no row count that looks wrong, and a change that a reviewer was right to approve.

  COALESCE does the reverse of NULLIF, and you have already met it, so remember the two as a pair: COALESCE turns a null into a value, NULLIF turns a value into a null, and adding either one is a single function call that can change the result of every construct in this lesson. The two questions to ask of your own database are which columns in your schema became nullable in the last year, and which of your reports asks a negative question about them.