How NULL Behaves in COUNT, GROUP BY and DISTINCT
NULLs follow different rules depending on where they appear: COUNT(*) counts every row while COUNT(column) skips the NULLs, SUM and AVG ignore them, and GROUP BY and DISTINCT treat all NULLs as one group even though they do not equal each other. These are exceptions to three-valued logic, deliberately, so that grouping is useful. They matter because swapping COUNT(*) for COUNT(column) to make a query cheaper can change the number it returns.
Questions this Concept answers
- Why do `GROUP BY` and `DISTINCT` put all `NULL`s together even though `NULL = NULL` is unknown?
COUNT in SQL, Explained for Beginners
This article explains the differences between COUNT(*), COUNT(column) and COUNT(DISTINCT column). COUNT(*) counts rows; COUNT(column) skips rows where that column is empty; COUNT(DISTINCT ...) counts…