A field guide to NULL · Edit Distance

Every NULL bug I have shipped had the same shape. The query ran. It returned rows. The number was wrong, and it was wrong in the direction nobody checks. NULL is not a value. It is the absence of one, and every operator in SQL treats an absence differently.

Eight orders, nothing missing

Illustrative rows. Every result is computed on this page with standard three-valued logic.

idregionamountrep_id

reps: r1 Ana · r2 Ben · r3 Cy

SELECT COUNT(*), COUNT(amount), SUM(amount), AVG(amount) FROM orders

COUNT(*)

COUNT(amount)

SUM(amount)

900

AVG(amount)

112.50

Eight rows, nothing missing

A toy orders table. Three columns can be NULL and none of them are yet, so every aggregate agrees: eight rows, eight amounts, average 112.50.

One cell goes NULL

Row 4 loses its amount. COUNT(*) still says eight. COUNT(amount) says seven, and AVG divides by seven, not eight. The average went up because a small value stopped existing.

Equals finds nothing

amount = NULL is not false. It is UNKNOWN, for every row, including the one that is NULL. WHERE only keeps TRUE. IS NULL is the two-valued question, and it works.

The NOT IN trap

Which reps have no orders? Cy, until one order has a NULL rep_id. Then NOT IN returns nobody, silently. NOT EXISTS asks the question row by row and ignores the NULL.

The join that drops rows

An inner join on rep_id is a comparison, and a NULL key compares UNKNOWN to everything, including another NULL. Row 6 disappears from the join. LEFT JOIN keeps it, with a NULL name.

Three truth values

AND, OR and NOT over TRUE, FALSE and UNKNOWN. Click through the table. The one to memorise: NOT UNKNOWN is UNKNOWN, which is why every NOT IN and every != can quietly drop rows.

Your turn

Toggle any cell to NULL and pick any query. Every number is computed on this page by a small evaluator of the standard rules, checked against twenty test cases.

Three truth values, not two

The thing to internalise is that a comparison involving NULL is not false. It is UNKNOWN, a third truth value. WHERE keeps a row only when its predicate is TRUE, so UNKNOWN gets dropped like FALSE. That part feels harmless. The part that bites is NOT: NOT UNKNOWN is still UNKNOWN. You cannot flip your way out.

Everything above is computed on the page. The table is eight made-up orders. The results are not made up: a small evaluator implements the standard rules for comparisons, AND, OR, NOT, IN, aggregates, grouping, ordering and joins, and a test file checks it against twenty cases before I trust it in front of you.

8 to 7

COUNT(amount) after one NULL

COUNT(*) stays at 8

112.50 to 121.43

AVG(amount) after the same NULL

divides by the non-null count

0 rows

from NOT IN once the subquery has a NULL

with the right answer being one rep

The five NULL bugs you will ship this year

The average that drifts. AVG ignores NULL and divides by what is left. Row 4 loses its amount, the average rises, and no one asks why revenue per order improved. If a NULL means zero in your domain, say so with COALESCE(amount, 0). If it means unknown, leave it out and report the count next to the average.

The equals that finds nothing. WHERE status = NULL is syntactically fine and returns nothing, always. Same for != NULL. You need IS NULL and IS NOT NULL, or IS DISTINCT FROM when you want a null-safe comparison of two columns.

The NOT IN that empties the report. “Which reps have no orders” is a NOT IN (SELECT rep_id FROM orders) for most people. The day one order has a NULL rep_id, that list is empty, and it will stay empty until someone notices the report has been blank for a month. Use NOT EXISTS. It asks the question one row at a time and a NULL cannot poison it.

The join that loses rows. Inner joins are comparisons. A NULL key matches nothing, including another NULL, so the row silently drops out of the result. If the key can be NULL, decide whether you want those rows and write LEFT JOIN on purpose.

The count that disagrees with itself. COUNT(*) counts rows. COUNT(col) counts non-null values. COUNT(DISTINCT col) ignores NULL entirely, while SELECT DISTINCT col keeps exactly one NULL, and GROUP BY col gives NULL its own group. Three different answers to “how many regions”, all correct.

Where engines disagree

Almost none of this varies by database. The one place it does is sorting. Postgres, Oracle and Snowflake treat NULL as larger than every value, so it lands last in an ascending sort. MySQL, SQL Server, SQLite and BigQuery treat it as smaller, so it lands first. The same ORDER BY amount puts the missing rows at opposite ends of the page depending on where it runs. Write NULLS FIRST or NULLS LAST and stop depending on the default.

The two habits that prevent all of them

First: decide what NULL means, per column, and write it down in the model. “Unknown” and “not applicable” and “zero” are three different things and SQL will not tell them apart for you. Once the meaning is fixed, the operator choice follows. Zero gets a COALESCE at the staging layer. Unknown stays NULL and gets a count alongside every average.

Second: never write a negative membership test. NOT IN with a subquery and != against a nullable column are the two constructs I refuse in review. NOT EXISTS and IS DISTINCT FROM do the same job and cannot be poisoned. This is not style. It is the difference between a report that is wrong and one that is empty, and empty at least gets noticed.

What to do this week

Grep your models for NOT IN (SELECT and != against nullable columns. Replace with NOT EXISTS and IS DISTINCT FROM.
Add not_null tests to every join key in dbt. A NULL key is a dropped row waiting to happen.
Put COUNT(col) next to every AVG(col) on any dashboard that matters.
Write NULLS LAST or NULLS FIRST in every ORDER BY on a nullable column.
Go back to the table above, toggle a cell, and watch which queries move. That is the whole guide.