NULL Handling in Aggregations
You'll learn how aggregate functions behave when they encounter NULL values and how to manage them properly in your queries.
The Core Idea
When you use aggregate functions like COUNT, SUM, AVG, MIN, or MAX, SQL has specific rules about how to handle NULL values. Understanding these rules prevents subtle bugs and incorrect results in your grouped data.
Most aggregate functions ignore NULLs completely. Think of NULLs as "unknown" or "missing" data—they're excluded from calculations rather than treated as zero or empty strings.
How Each Function Treats NULLs
COUNT(*): Counts all rows, including those with NULLsCOUNT(column): Counts only non-NULL values in that columnSUM,AVG,MIN,MAX: Ignore NULL values entirelyGROUP BY: Treats all NULLs as one group
Critical Implications
If you calculate AVG(salary) on three rows with values 100, 200, and NULL, the result is 150 (not 100). The NULL is excluded, so it's (100 + 200) / 2, not (100 + 200 + 0) / 3.
This can surprise you when NULL counts differ across columns. If you're comparing COUNT(*) to COUNT(column), the difference reveals how many NULLs exist.
Handling Techniques
To include NULLs in calculations, use techniques you've already learned:
- Replace NULLs before aggregating (conceptually using logic to convert them)
- Use
COUNT(*)when you want all rows regardless of NULLs - Filter NULLs explicitly with
WHERE column IS NOT NULLbefore grouping - Remember
HAVINGcan filter out groups after aggregation
Key Takeaway: Aggregate functions (except COUNT(*)) ignore NULL values, which can significantly affect your results—always consider whether NULLs should be excluded or handled explicitly in your aggregations.