Predict the result first

Suppose three orders contain amounts of $100, NULL and $0. There are three rows, two known amounts and one missing amount. The zero is a known value; the NULL is not. Whether the missing value should block a report depends on the reporting contract.

WITH orders(order_id, amount_usd) AS (
  VALUES (1, 100), (2, NULL), (3, 0)
)
SELECT COUNT(*) AS rows_seen,
       COUNT(amount_usd) AS known_amounts,
       SUM(amount_usd) AS known_total,
       AVG(amount_usd) AS known_average,
       AVG(COALESCE(amount_usd, 0)) AS zero_filled_average
FROM orders;

Expected: 3 rows, 2 known amounts, known total 100, known average 50 and zero-filled average approximately 33.33. Replacing the missing value with zero changed the denominator of the average. This may be appropriate only when your business definition establishes that the absence really means zero.

Use the correct predicate

Use amount_usd IS NULL to find unknown amounts. A comparison such as amount_usd = NULL does not evaluate to true for missing values. A filter keeps true conditions; unknown comparisons do not qualify. Similarly, amount_usd > 0 excludes both zero and missing values, but for different reasons.

The NOT IN trap

If the exclusion list contains a NULL, NOT IN can produce unknown results for otherwise unmatched values. When you mean “there is no matching record,” a correlated NOT EXISTS often expresses the intent more clearly. Still define how a missing key on the left should be handled; an anti-join does not invent that business rule.

WITH customers(id) AS (VALUES (1), (2), (3)),
blocked(id) AS (VALUES (2), (NULL))
SELECT c.id
FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM blocked b WHERE b.id = c.id
)
ORDER BY c.id;

Expected customer IDs: 1 and 3. Test the equivalent NOT IN query and explain why it differs on this fixture. PostgreSQL and DuckDB support the constructs used here; the examples were executed with DuckDB.

Explain the reporting consequence

Do not publish $100 as a complete total without noting that one order lacks an amount. Depending on the contract, show an incomplete-data warning, quarantine the record or halt the calculation. COALESCE is a useful transformation, but it cannot establish why a source value is missing.

Practice task

Add a fourth order for $50. Calculate the known average and the zero-filled average by hand. Then write a query that reports the missing-amount count beside the total. Explain how a dashboard could show that warning without misleading a reader into assuming the report is complete.

Documentation and further reading

• PostgreSQL comparison predicates