SQL NULL values: why missing is not zero

In SQL, NULL marks an absent or unknown value. Zero is a known numeric value. Treating them as interchangeable changes the meaning of an analysis, even when the query continues to run.

Imagine a discount field. Zero can mean that no discount was applied. NULL can mean that the discount amount was not captured. Replacing both with zero may make a spreadsheet look complete while concealing an instrumentation problem.

The commerce SQL fixture contains eight completed orders: five have a recorded zero discount, one has a 2,000-paise discount, and two have an unknown discount. The final order amounts are separately recorded, so the missing discount fields do not make those known totals disappear.

Ask about missingness explicitly

To find the unknown discounts:

SELECT order_id, discount_paise
FROM orders
WHERE status = 'completed'
  AND discount_paise IS NULL
ORDER BY order_id;

Expected orders: O1003 and O1007. Use IS NULL, not = NULL. An ordinary equality comparison does not establish that an unknown value equals another value; rows for which a WHERE condition is unknown do not qualify.

For the known zero discounts:

SELECT COUNT(*) AS known_no_discount_orders
FROM orders
WHERE status = 'completed'
  AND discount_paise = 0;

Expected count: five. These are different groups, and combining them requires a business assumption that the source data alone does not justify. SQL's treatment of filtering and NULL is explained in the engine documentation. SQLite SELECT.

Report the population and the known portion

SELECT
    COUNT(*) AS eligible_orders,
    COUNT(discount_paise) AS orders_with_known_discount,
    SUM(CASE WHEN discount_paise IS NULL THEN 1 ELSE 0 END)
        AS orders_with_unknown_discount
FROM orders
WHERE status = 'completed';

The result is 8, 6, 2. The three numbers reconcile: six known plus two unknown equals eight eligible orders.

COUNT(*) counts rows. COUNT(discount_paise) counts non-NULL values in that expression. It includes zero because zero is a value. This makes the pair useful for a completeness check. SQLite aggregate functions.

You can now say, “Discount amount is available for six of eight completed orders.” That is more informative than announcing an average without disclosing its denominator.

Averages expose the hidden assumption

The six known discount amounts sum to 2,000 paise. Their average is approximately 333.33 paise. If you replace the two unknown values with zero before averaging, the result becomes 250 paise.

Both calculations are arithmetically consistent with their inputs. They answer different questions:

The first does not automatically estimate the whole population fairly. Perhaps discounts are especially likely to go missing for a particular sales channel. The second adds an assumption. Neither problem is solved by a neater SQL expression.

Before choosing, inspect missingness by source, time, region or process step. If the missing records systematically differ, report that limitation and investigate the collection process.

When COALESCE is justified

COALESCE(value, 0) is useful when a missing result truly represents an absent contribution under the metric contract.

Consider a LEFT JOIN to a refund summary. If the refund system is complete and no refund record means no refund occurred, a missing summary can reasonably contribute zero:

WITH refund_totals AS (
    SELECT order_id, SUM(refund_paise) AS refunded_paise
    FROM refunds
    GROUP BY order_id
)
SELECT
    o.order_id,
    o.order_total_paise - COALESCE(r.refunded_paise, 0)
        AS refund_adjusted_value_paise
FROM orders AS o
LEFT JOIN refund_totals AS r ON r.order_id = o.order_id
WHERE o.status = 'completed';

The assumption matters. If refunds have not yet arrived from the source system, absence may mean “not loaded,” not “none.” A completeness or freshness check is needed before applying the default.

This is different from our discount field, where NULL explicitly means the amount was not recorded. The right treatment depends on meaning and provenance, not merely on the SQL type.

Watch for NULL in comparisons and arithmetic

A condition such as discount_paise > 0 selects known positive discounts; it does not classify the unknown group. Similarly, adding an unknown value can produce an unknown result. A metric built from several nullable columns therefore needs a documented policy for each input.

Avoid a blanket rule that every missing numeric field becomes zero. Inventory, prices, survey scores, timestamps and balances have different missingness semantics. For some, zero is a legitimate observation whose meaning must remain distinct.

Practice: create three categories—positive discount, known zero discount and unknown discount—and verify their counts sum to eight. Then write two sentences for a manager explaining what can be concluded about discount use and what remains uncertain.

The Data Analytics with Generative AI programme includes SQL, wrangling and verification work where these distinctions matter. They also matter when an AI system writes the query: a syntactically correct answer can still hide an unjustified missing-data assumption.

Continue learning

This article is part of the SQL foundations for reliable analysis sequence. Use the neighbouring tasks when you need the prerequisite or the next application.

• Review the prerequisite or neighbouring task in SELECT and WHERE: build a reproducible sales extract.
• Continue with COUNT star versus COUNT column in a customer report.