Counts refuse to add up. COUNT(*) says 8 rows, but your FAILED count plus your
not-FAILED count totals 7. Or WHERE status != 'FAILED' returns fewer rows than
everyone expected, or COUNT(status) disagrees with COUNT(*) on the same table.
Nothing errors — the report is confidently short.
Any table where a column has missing values — a status never written, a city never
filled, an optional field left blank. Real payment, delivery, and KYC tables always
have some. Freshers hit it the first time they write a != filter or hand-total a
report during reconciliation. Linked module: 0.6 (SQL for AI people).
NULL is not a value; it is the absence of one. Any comparison against it — equal,
not-equal, greater — evaluates to neither true nor false, and WHERE keeps only rows
that are definitely true. So status != 'FAILED' silently drops every NULL-status
row along with the FAILED ones: those rows are neither counted as FAILED nor as
not-FAILED, which is why the halves stop summing to the whole. The same rule explains
the count gap: COUNT(*) counts rows, COUNT(status) counts only non-NULL statuses.
Confirm with two queries: SELECT COUNT(*) - COUNT(status) FROM orders gives the
NULL count, and SELECT * FROM orders WHERE status IS NULL shows the hidden rows
themselves.
Say what NULL should mean, out loud, then encode the decision. If NULL rows belong in
the "not failed" bucket, write WHERE status != 'FAILED' OR status IS NULL. If they
deserve their own label, normalise first with COALESCE(status, 'UNKNOWN') and filter
on that. Always test for missing values with IS NULL / IS NOT NULL — never
= NULL, which is never true for any row. Re-run the halves-versus-whole check after
the fix: FAILED plus not-FAILED plus (or including) NULL must equal COUNT(*).
Audit NULLs on first contact with any table: SELECT COUNT(*) - COUNT(col) FROM t
per important column takes seconds and removes all surprise. Decide the NULL policy
at the start of a report, write it as a comment above the query, and prefer COALESCE
so the decision is visible in the SQL itself. Treat any != filter on a nullable
column as a code smell to re-check.
Q: "Your query says 5 successful orders, the dashboard says 6. The table has 8 rows
and 2 failures. What happened?" Strong answer: spot that 5 + 2 < 8 immediately, name
NULL's three-valued comparison rule in one sentence, give the confirming query
(COUNT(*) - COUNT(status) or WHERE status IS NULL), and show both fixes — the
OR status IS NULL filter and the COALESCE normalisation — while stating which one
the dashboard evidently used.