Why SQL comparisons with NULL do not behave like zero

A small dataset that makes the difference visible

Imagine three recorded balances: 100, 0 and NULL. There are three accounts, but only two known balances. The third account might have a balance of zero, a debt or a positive amount; the data does not answer that question.

The query below uses VALUES, so it needs no existing table. It produces total_rows = 3, known_balances = 2 and mean_known = 50. Replacing the missing value with zero changes the denominator: mean_with_zero is about 33.33. That is a change in the meaning of the report, not merely a formatting choice.

WITH balances(balance) AS (
  VALUES (100::numeric), (0::numeric), (NULL::numeric)
)
SELECT COUNT(*) AS total_rows,
       COUNT(balance) AS known_balances,
       AVG(balance) AS mean_known,
       AVG(COALESCE(balance, 0)) AS mean_with_zero
FROM balances;

Find missing values

A missing balance is not automatically a zero balance. Keep that distinction explicit. For a comparison that treats two missing values as equal, PostgreSQL provides IS NOT DISTINCT FROM.

SELECT * FROM accounts WHERE balance IS NULL;
SELECT a IS NOT DISTINCT FROM b;

Count rows deliberately

COUNT(*) counts rows. COUNT(balance) counts non-null balances. COALESCE(balance, 0) substitutes zero when balance is missing; apply it only when that replacement matches the meaning of your data.

Why a filter unexpectedly loses rows

For the same dataset, WHERE balance <> 0 keeps only 100. Zero makes the condition false, while NULL makes it unknown. If the report should include both nonzero and missing balances, write balance <> 0 OR balance IS NULL.

Negating the comparison does not recover missing rows: NOT(balance = 0) is still unknown when balance is NULL. Write the missing-value rule explicitly instead of assuming that every condition has only two possible outcomes.

The NULL trap in NOT IN

Suppose you need identifiers other than 2, and the exclusion list accidentally contains NULL. Even 1 NOT IN (2, NULL) is unknown: SQL cannot establish that 1 differs from every item. A WHERE condition based on this expression drops the row.

For an exclusion subquery, consider NOT EXISTS with a clearly defined matching condition. It expresses the absence of a matching row without inheriting a NULL from the returned list. Still decide how nullable identifiers should behave; replacing syntax does not decide your business rule.

SELECT 1 NOT IN (2, NULL) AS result;

Missing data after a LEFT JOIN

A LEFT JOIN keeps rows from the left table and supplies NULL for unmatched right-side columns. Filtering a right-side value in WHERE can then remove the unmatched rows. If you want all accounts but only matching active records on the right, place that right-side restriction in ON.

Before using COALESCE, ask why the value is missing: not collected, not applicable or absent because of the join? Those cases may need different labels. First compare total rows, known values and missing values; only then choose a replacement for the report.