SQL / NULL AND THREE-VALUED LOGIC
Filtering and counting when NULLs are present
Audit missing data with COUNT variants, keep filter buckets summing to the row total, and write conditional counts that handle unknowns on purpose.
What you will learn
- Read COUNT(*) as rows and COUNT(col) as non-null values; the gap is the NULL count.
- Add an IS NULL bucket when filters must add up to the full row count.
- AVG divides by COUNT(col), so missing values are skipped, not treated as zero.
- Count matches with COUNT(CASE WHEN cond THEN 1 END), unknowns with IS NULL.
Understanding Filtering and counting when NULLs are present
WHERE is a gate that opens only for TRUE. A row whose predicate evaluates to unknown is discarded exactly like a row that evaluates to false, and nothing in the result set hints that it happened. That is why celsius >= 15 and celsius < 15 stop adding back up to COUNT(*) once the column holds NULLs: the missing readings fail both tests, and the only predicate that keeps them is celsius IS NULL. Whenever your buckets are supposed to cover the whole table, one of those buckets has to be the IS NULL one.
Aggregates deal with the same NULLs the other way round: they drop them before computing, so unknown never propagates into the answer. COUNT(*) counts rows and takes no expression at all, while COUNT(celsius) counts rows where celsius is not null, which makes COUNT(*) - COUNT(celsius) the exact number of missing values. The same skipping rule fixes AVG's denominator, because AVG(celsius) is SUM(celsius) / COUNT(celsius): a sensor that reported once out of six rows contributes one value, not one value plus five zeros. Picture an aggregate as receiving the column with the holes already cut out.
Conditional counting uses that skipping deliberately. COUNT(CASE WHEN celsius >= 15 THEN 1 END) counts precisely the rows where the condition is TRUE, because both the false rows and the unknown rows fall through to the implicit ELSE NULL and COUNT ignores NULL. SUM(CASE WHEN cond THEN 1 ELSE 0 END) reaches the same number by adding a zero for those rows, which exposes the risk: unknown always lands on the 'did not match' side, so when that distinction matters you must count IS NULL separately. GROUP BY is the one operation that treats NULL as a value of its own and collects all such rows into a single group, so a grouped report has one more row than COUNT(DISTINCT) on the same column.
placeholder
CREATE TABLE reading (
sensor TEXT,
celsius INTEGER
);
INSERT INTO reading (sensor, celsius) VALUES
('s1', 12),
('s1', NULL),
('s2', 18),
('s2', 15),
('s3', 15),
('s3', NULL);
SELECT COUNT(*) AS rows_total,
COUNT(celsius) AS with_value,
COUNT(*) - COUNT(celsius) AS missing,
COUNT(DISTINCT celsius) AS distinct_values,
SUM(celsius) AS total,
AVG(celsius) AS mean
FROM reading;In WHERE an unknown comparison is thrown away like a false one, while aggregates silently skip NULL inputs, so row counts and value counts are two different numbers.
Worked examples
A filter and its negation lose rows
Shows that two opposite comparisons plus the table total do not agree until the unknown rows are counted on their own.
SELECT (SELECT COUNT(*) FROM reading WHERE celsius >= 15) AS at_least_15,
(SELECT COUNT(*) FROM reading WHERE celsius < 15) AS below_15,
(SELECT COUNT(*) FROM reading WHERE celsius IS NULL) AS unknown,
(SELECT COUNT(*) FROM reading) AS rows_total;Example explained
Line 1celsius >= 15 keeps 18, 15 and 15, because those comparisons are TRUE.
Line 2celsius < 15 keeps only 12; the two NULL rows are not below 15 either, they are simply not comparable.
Line 33 + 1 is 4, two short of the 6 rows, and the shortfall is exactly the unknown column.
Line 4celsius IS NULL is a test on the row's state rather than a comparison, so it returns TRUE and recovers those two rows.
Counting per group with a condition
Counts rows, reported values and matching values side by side for each sensor.
SELECT sensor,
COUNT(*) AS rows_seen,
COUNT(celsius) AS reported,
COUNT(CASE WHEN celsius >= 15 THEN 1 END) AS warm,
SUM(CASE WHEN celsius IS NULL THEN 1 ELSE 0 END) AS missing
FROM reading
GROUP BY sensor
ORDER BY sensor;Example explained
Line 1COUNT(*) counts rows per group, so s1 shows 2 even though one reading was never recorded.
Line 2COUNT(celsius) skips the NULL row, which is why reported is 1 for s1 and 2 for s2.
Line 3The CASE has no ELSE, so a false comparison and an unknown comparison both produce NULL and COUNT leaves them out; s1's warm count is 0, not NULL.
Line 4The missing column can use SUM with ELSE 0 safely because IS NULL is always TRUE or FALSE, so every row contributes a number.
COUNT(DISTINCT) versus GROUP BY
Demonstrates that grouping keeps a bucket for the unknown rows while COUNT(DISTINCT) does not.
SELECT (SELECT COUNT(DISTINCT celsius) FROM reading) AS distinct_values,
(SELECT COUNT(*)
FROM (SELECT celsius FROM reading GROUP BY celsius) AS g) AS group_by_rows;Example explained
Line 1COUNT(DISTINCT celsius) removes NULLs first and then de-duplicates, reporting only 12, 15 and 18.
Line 2GROUP BY celsius gathers every NULL row into one group, so the grouped query emits four rows.
Line 3The two numbers differ by exactly one whenever the column contains at least one NULL, which makes this a quick check on a grouped report.
Important notes
SUM and AVG return NULL rather than 0 for a group with no non-null values, so an empty total means nothing was known, not that the total was zero.
The output above is SQLite's; Postgres prints AVG over an integer column as numeric with trailing zeros (15.0000000000000000). COUNT(*) FILTER (WHERE cond) is a shorter spelling of the CASE form in Postgres, SQLite 3.30+ and DuckDB, but MySQL does not support it.
Common mistakes
Counting unfinished work with WHERE status <> 'done': rows where status IS NULL are dropped as well, so the report is short by exactly the number of unknown rows and never mentions them.
Reporting COUNT(email) as the number of customers. It is the number of stored addresses, so the figure silently disagrees with the COUNT(*) used in another report, and a fill rate computed as COUNT(email) / COUNT(email) is always 100%.
Filtering with NOT IN (SELECT manager_id FROM staff) when that subquery can yield a NULL: every row's test becomes unknown, the count comes back 0, and the empty result looks like proof that no unmatched rows exist.
Try it yourself
Change, predict, then run
Add a fourth sensor to the reading table with two rows that are both NULL, then write one query returning per sensor the row count, the average reported temperature and the number of missing readings, and explain why the new sensor's average is NULL instead of 0.
Open the SQL workspaceCheck your understanding
A survey table has 10 rows. The score column holds 4, 6 and 8 in three rows and NULL in the other seven. What do COUNT(*), COUNT(score), SUM(score) and AVG(score) return?
- 10, 3, 18, 6
- 10, 10, 18, 1.8
- 3, 3, 18, 6
- 10, 3, 18, 1.8
Show answer
COUNT(*) counts all 10 rows, COUNT(score) counts only the 3 stored values, and AVG divides SUM by that same non-null count, giving 18 / 3 = 6. The 1.8 option assumes AVG spreads the total over all 10 rows, which would only happen if the missing scores had actually been recorded as 0.