SQL null semantics: count records without inventing outcomes

Last updated: 5 Oct 20265 min read

tutorial

SQL null is an unknown value marker, not a zero, an empty string or a failed outcome.

Separate rows from observed values

A support export has 47 eligible case rows, but only 39 have a reviewer outcome. COUNT(*) returns 47 while COUNT(review_outcome) returns 39. Both numbers are useful and answer different questions. A resolved-rate denominator cannot be inferred from one count without a written cohort policy. Missing-data rules must identify unknown, not applicable and not yet observed outcomes before SQL turns them into metrics.

Use three-valued predicates deliberately

A predicate such as review_outcome = 1 does not evaluate to false when the outcome is null; it evaluates to unknown, and WHERE removes that row. Likewise, review_outcome != 1 does not select null rows. Write IS NULL for unknowns and define whether the metric uses all eligible rows or only observed rows. In conditional aggregation, use ELSE 0 for a known false condition but preserve an explicit unknown count alongside it.

Watch NOT IN with nulls

If an exclusion subquery returns even one null ID, case_id NOT IN (subquery) can yield unknown for every candidate and remove the whole cohort. Use NOT EXISTS with a correlated, non-null identity comparison, and validate null IDs in the exclusion source. This is a query-semantics failure, not a rare numerical corner case. A zero-row result should trigger an anomaly check before publication.

Keep missingness visible

For each segment, report eligible cases, reviewed cases, positive reviewed cases and unknown outcomes. A segment with five positives from 37 reviewed cases out of 47 eligible cases has an observed-case positive rate of 5/37, but its eligible-case positive fraction is not known without assumptions about the ten missing outcomes. Nonresponse bounds give one way to show that gap.

Test against a tiny fixture

Include one row with review_outcome = 1, one with 0 and one with NULL. Assert the three counts separately. Add a null to the exclusion table and confirm the NOT EXISTS query still returns eligible cases. Run the fixture in the same SQL dialect as production; subtle casts and boolean behavior vary by engine.

Implementation

WITH review_cases(case_id, review_outcome) AS (
    VALUES (201, 1), (202, 0), (203, NULL), (204, 1)
)
SELECT COUNT(*) AS eligible_cases,
       COUNT(review_outcome) AS reviewed_cases,
       SUM(CASE WHEN review_outcome = 1 THEN 1 ELSE 0 END) AS positive_reviews,
       SUM(CASE WHEN review_outcome IS NULL THEN 1 ELSE 0 END) AS unknown_reviews
FROM review_cases;

Performance and operating cost

The aggregation scans N rows in O(N) time and O(1) memory for one global group. Grouped counts require O(G) aggregate state for G segments. A NOT EXISTS anti-join can use an indexed case ID; without an index, a nested plan may repeatedly scan the exclusion table.

Common Mistakes

Do not treat COUNT(column) as a count of all cohort rows.
Do not expect a comparison with NULL to be true or false.
Do not use NOT IN when the subquery can contain null identities.