NULL vs Blank vs Zero: How to Report Missing Data in SQL

The three states and what they mean

The distinction that matters most in reporting is between NULL and 0: one says "we don't have this number," the other says "we have this number, and it's zero." Those are different facts, even though a dashboard cell might render them identically.

How each one changes AVG()

-- Orders: [10, 20, NULL, 30]  (one order's discount was never recorded)

SELECT AVG(discount_amount) AS avg_ignoring_null FROM orders;
-- Result: 20.0  -- (10+20+30)/3 — the NULL row is excluded entirely

SELECT AVG(COALESCE(discount_amount, 0)) AS avg_treating_null_as_zero FROM orders;
-- Result: 15.0  -- (10+20+0+30)/4 — the NULL row now counts as a real zero

Neither result is universally "correct" — it depends entirely on what that NULL means for this column. If it means "we don't yet know this order's discount," excluding it is right. If it means "no discount logic applies to this order type, which is functionally a $0 discount," including it as zero is right. The bug isn't the math — it's applying COALESCE(x, 0) as a reflex without answering that question first.

How each one changes COUNT()

SELECT
  COUNT(*)              AS total_rows,          -- counts every row
  COUNT(middle_name)    AS non_null_names,     -- excludes NULL, but counts '' as present
  COUNT(NULLIF(middle_name, '')) AS meaningfully_filled -- excludes both NULL and ''
FROM customers;

If a text column mixes NULL and '' to mean the same thing — "no middle name given" — a plain COUNT(middle_name) will overcount how many customers actually have one, because it only excludes the NULLs.

Deciding what a column should use

A simple rule of thumb: use NULL when a value genuinely wasn't captured or doesn't apply; use 0 only when zero is a real, confirmed measurement; avoid empty strings in numeric-adjacent columns entirely, and in text columns, decide once whether "nothing entered" is represented as NULL or '' — then apply it consistently, since mixing both for the same meaning is what breaks COUNT() above.

Converting between them intentionally

-- NULL -> a chosen default, only where 0 is genuinely the right meaning
SELECT COALESCE(discount_amount, 0) AS discount_for_display FROM orders;

-- '' -> NULL, to normalize blank text so COUNT() and IS NULL behave consistently
UPDATE customers SET middle_name = NULLIF(TRIM(middle_name), '');

A worked example: the missing-day problem

A daily revenue report has no row at all for March 15th. Two very different explanations produce the exact same symptom:

-- Option A: the store was genuinely closed — 0 is the accurate value
SELECT d.report_date, COALESCE(SUM(o.amount), 0) AS daily_revenue
FROM calendar_dates d
LEFT JOIN orders o ON o.order_date = d.report_date
GROUP BY d.report_date;

-- Option B: the ETL job failed to load that day — 0 would hide a real outage
SELECT d.report_date,
  CASE WHEN NOT EXISTS (SELECT 1 FROM etl_load_log l WHERE l.load_date = d.report_date AND l.status = 'success')
       THEN NULL  -- surfaced as missing/unknown, not silently zero
       ELSE COALESCE(SUM(o.amount), 0) END AS daily_revenue
FROM calendar_dates d
LEFT JOIN orders o ON o.order_date = d.report_date
LEFT JOIN etl_load_log l ON l.load_date = d.report_date
GROUP BY d.report_date;

Option A's zero is a fact. Option B's zero would be a guess dressed up as a fact — checking a load log before deciding what "missing" means is the difference between a report that's honestly incomplete and one that's confidently wrong.

Common mistakes

• Wrapping every nullable column in COALESCE(x, 0) as a habit, without checking whether NULL means "zero" or "unknown" for that specific column.
• Mixing NULL and empty string for the same meaning in one text column, which breaks the assumption that COUNT(column) reflects "how many have a value."
• Zero-filling missing dates in a report without checking why they're missing — a pipeline failure can look identical to a genuinely quiet day.
• Comparing = '' instead of using NULLIF/COALESCE when the goal is really to detect "no meaningful value," conflating two different underlying representations.