Course contentsShow
← Computer Science
Lesson 1600 of 2,87236. Database FundamentalsFree lesson

NULL Handling in Aggregations

Learning how aggregate functions treat NULL values and techniques for handling them correctly.

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 NULLs
  • COUNT(column): Counts only non-NULL values in that column
  • SUM, AVG, MIN, MAX: Ignore NULL values entirely
  • GROUP 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 NULL before grouping
  • Remember HAVING can 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.