Why don't the counts from status = 'active' and status <> 'active' add up to the table total?

## The symptom A dashboard shows an "active" tile and a "not active" tile, and their sum is smaller than the row count of the table. Nobody deleted anything; the two queries look like exact opposites: ```sql SELECT COUNT(*) FROM subscriptions WHERE status = 'active'; -- 6 SELECT COUNT(*) FROM subscriptions WHERE status <> 'active'; -- 3 SELECT COUNT(*) FROM subscriptions; -- 10 ``` One row is missing from the breakdown. It is the row whose `status` is NULL. ## Why both filters reject the same row On a row where `status` is NULL, `status = 'active'` evaluates to UNKNOWN — SQL cannot decide whether an absent value equals `'active'`. Crucially, `status <> 'active'` is *also* UNKNOWN, for the same reason: not knowing what the value is means not knowing that it differs either. `WHERE` retains a row only when the condition is TRUE, so both queries discard it. The usual instinct — write the second filter as a negation — changes nothing: ```sql SELECT COUNT(*) FROM subscriptions WHERE NOT (status = 'active'); -- still 3 ``` because `NOT UNKNOWN` is UNKNOWN. In three-valued logic a predicate and its negation are not complementary: they partition only the rows where the predicate is *decidable*. ## Diagnosing it The check is one query: ```sql SELECT COUNT(*) AS total, COUNT(*) FILTER (WHERE status IS NULL) AS unknown_status FROM subscriptions; ``` (or a plain `WHERE status IS NULL` count on engines without `FILTER`). If `unknown_status` equals the gap between the total and the sum of the buckets, the diagnosis is confirmed. This should be an instinct: whenever category counts fail to reconcile to a total, suspect a nullable categorising column before suspecting the data. The same failure shows up in subtler places — a job that processes `WHERE processed_at IS NULL` and a monitor that counts `WHERE processed_at IS NOT NULL` reconcile fine, because `IS NULL` predicates are two-valued; but a job split on `WHERE priority > 5` and `WHERE priority <= 5` silently leaves NULL-priority rows unprocessed by *either* branch. That class of bug — work that no worker claims — is much more expensive than a wrong dashboard number. ## Making the buckets exhaustive Three correct shapes, in increasing order of elegance: ```sql -- 1. explicit extra branch, portable everywhere WHERE status <> 'active' OR status IS NULL -- 2. NULL-safe comparison (standard; check engine support) WHERE status IS DISTINCT FROM 'active' -- 3. three explicit buckets rather than two SELECT COALESCE(status, '(unknown)') AS bucket, COUNT(*) FROM subscriptions GROUP BY COALESCE(status, '(unknown)'); ``` Option 1 works because `IS NULL` is two-valued: it contributes a decisive TRUE that the surrounding `OR` absorbs. Option 2 says the same thing in one predicate — `IS DISTINCT FROM` is TRUE when the values differ *or* when exactly one side is NULL, and never returns UNKNOWN. Option 3 is often the best answer for a report: instead of pretending the world is binary, it surfaces the missing-value population as its own visible category, so the next person to read the dashboard sees the gap rather than losing it. ## The judgment beyond the fix The interesting part of this question at senior level is not the rewrite; it is what you do next. - **Decide what the NULL means.** "Not yet set", "not applicable" and "we lost the value in a migration" call for different treatments. Bucketing them all as `(unknown)` is honest; folding them into `'inactive'` is a claim about the domain that may be false. - **Ask whether the column should be nullable.** A status column that always has a real value in the domain is better expressed as `NOT NULL` with an explicit `'unknown'` member, which removes three-valued reasoning from every query that will ever touch it. That trade — an extra enum member versus a nullable column — is the durable fix. - **Fix every consumer, not just the failing one.** If one dashboard tile was wrong, every other query filtering that column with `=` or `<>` is suspect. Grep for them. - **Add a reconciliation check.** A test or monitor asserting that the buckets sum to the total catches the regression the next time someone adds a category. ## How to answer Lead with the mechanism in one sentence — UNKNOWN is discarded by `WHERE`, so predicate and negation are not exhaustive — then the diagnostic count, then the rewrite, then the modelling question about whether the column should be nullable at all.