Missing values: NULL, empty, N/A, zero and minus one

Five spellings of “we do not know”

The NULL lesson covered how a database compares an unknown. This one is about what to do when a column full of unknowns arrives, and it starts with the fact that most exports do not use NULL for missing at all. They use the empty string, or N/A, or the literal text NULL, or a dash, or a zero, or -1, or 9999, or 1900-01-01. Every one of these is a value that a query will happily compare, sum and average.

Find them by looking at the tail of the value list, as the earlier cleaning lesson showed, and then at the round numbers: a spike of 0 in an income column, a cluster at -1 in an age column, a date of 1 January 1900 in a column of birthdays. Then convert each to a real NULL in the clean view, and count each kind separately:

CASE WHEN income_text IN ('', 'N/A', 'NULL', '-', '0', '-1', '9999') THEN NULL
     ELSE income_text::numeric END AS income

The counts matter because the sentinels often mean different things. In one system -1 means “customer refused to say” and the empty string means “the field did not exist when this account was created”. Those are different facts, and the next section is about why.

Four reasons a value is missing

• Not applicable. A spouse’s name for someone unmarried. There is no value and never will be.
• Not collected. A field added to the form in 2025; every account from before has nothing. Missingness is a function of account age.
• Refused or skipped. The customer left it blank. People who skip an income question are not a random sample of people.
• Lost. A migration dropped it, an integration failed for a week. Missingness is a function of a date range.

The treatment differs by reason. Not-applicable values should stay NULL and be handled with CASE. Not-collected values can often be recovered from another source. Lost values can be identified by date and the affected period excluded or flagged. Refused values are the dangerous ones, because they are missing because of what they would have been, and no arithmetic on the rows that remain can undo that.

What filling in does to the numbers

A model or a chart needs a number in every cell, so the temptation is to fill the gaps. Each fill has a mechanical consequence.

Fill with zero and the average falls, and a histogram grows a spike at zero that describes nobody. Fill with the mean and the average is unchanged, but the spread shrinks, because every filled row sits exactly on the centre, and anything that depends on variability (a confidence interval, a correlation, a model’s certainty) is now overstated. Fill with the median and the same happens, slightly less. Fill by carrying the last value forward in a time series and a sensor that died looks like it is reporting a steady reading.

The fill that loses least is the one that keeps the fact of the missingness:

coalesce(income, (SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY income) FROM customers))
  AS income_filled,
(income IS NULL)::int AS income_missing

Two columns instead of one. The model can learn that the missing flag matters, which it often does, since customers who skip the income question behave differently from those who answer. A report can show the rate of missingness next to the average. The information was never thrown away.

Dropping rows and dropping columns

A column that is 90% missing is usually a column to drop, unless the 10% is a specific, meaningful group. A row with one missing value is usually a row to keep, with the flag above. Dropping every row that has any missing value is the default in many tools and the worst option in most datasets, because missingness is rarely random. If customers who churn stop filling in the survey, dropping the incomplete rows drops the churners, and the retention figure computed on what remains is a figure about the loyal.

The test is simple: compare the rows you would drop with the rows you would keep, on a column you care about. If the two groups differ, dropping is not neutral.

Where each decision lives

The raw table keeps the sentinels. The clean view converts them to NULL and keeps NULL. The feature table, which is two lessons away, is where filling happens, next to a flag, because that is the only place a filled value is needed and the only place the filling rule can be stated once. Reports read the clean view and decide, per query, what an unknown should do, as the NULL lesson said.