NULL Values in GROUP BY
You'll learn: How SQL's GROUP BY treats NULL values and what that means for your aggregate calculations.
The Core Idea
When you use GROUP BY, SQL treats all NULL values as if they belong to the same group. This might surprise you at first—after all, NULL represents "unknown," and logically, one unknown value isn't necessarily equal to another unknown value. But for grouping purposes, SQL collects all NULLs together.
Think of it like sorting mail: if several letters have missing or illegible addresses, the postal worker puts them all in the same "undeliverable" pile, even though they're headed to different unknown destinations.
How It Works in Practice
If you have a sales table with a region column that contains NULL for some rows (perhaps international sales not yet categorized), and you run:
SELECT region, COUNT(*) AS sales_count
FROM sales
GROUP BY region
You'll get one row where region is NULL, showing the count of all rows with missing region data.
Implications for Aggregates
- COUNT(*) includes rows with
NULLin the grouped column - COUNT(column_name) still ignores
NULLs within that specific column for counting - SUM, AVG, MIN, MAX work normally but apply to the
NULLgroup
The NULL group appears in your results just like any other group—you can even sort it, filter it with HAVING, or use it with ORDER BY.
Key Takeaway: GROUP BY treats all NULL values as a single, distinct group, allowing you to see and aggregate data with missing values separately from known categories.