NULLs in Aggregates, Joins and DISTINCT
How NULL behaves differently across grouping, joining, and uniqueness.
NULL in Three Surprising Places
NULL does not behave the same everywhere. The final lesson covers the three contexts where its behavior surprises candidates the most: aggregates, joins, and DISTINCT / GROUP BY.
The recurring twist is that aggregates and filtering treat NULL as 'skip me', but grouping and DISTINCT treat NULL as 'a value that equals other NULLs'. That inconsistency is exactly what interviewers probe.
Master these and you have closed the loop on the most common NULL questions in SQL screens.
Aggregates Ignore NULL
The headline rule: aggregate functions skip NULLs. SUM, AVG, MIN, MAX, and COUNT(column) all ignore NULL inputs entirely rather than treating them as zero.
This is why AVG can return a different number than you expect. It divides the sum of non-NULL values by the count of non-NULL values, not by the total row count.
-- bonus values: 100, 200, NULL
SELECT
SUM(bonus) AS total, -- 300 (NULL ignored)
AVG(bonus) AS average, -- 150 = 300 / 2, not / 3
COUNT(bonus) AS cnt -- 2 (NULL not counted)
FROM employees;All lessons in this course
- Three-Valued Logic and UNKNOWN
- IS NULL, IS NOT NULL and NULL-Safe Equality
- COALESCE, NULLIF and ISNULL
- NULLs in Aggregates, Joins and DISTINCT