RSS Feed

Null Values in SQL Group By

SQL Having SQL Group By Examples of SQL Group By and SQL Having


It is known that Null <> Null. When two different NULL values are compared, the result is NULL (not TRUE), i.e. the two NULL values are not considered to be equal. Applying the same rule to the GROUP BY clause would force SQL to place each row with a NULL grouping column into a separate group by itself.

But creating a separate group for every row with a NULL in a grouping column is confusing and of no useful value, so designers wrote the SQL standard such that NULL values are considered equal for the purposes of a GROUP BY clause. Therefore, if two rows have NULL values in the same grouping columns and matching values in the remaining non-NULL grouping columns, the DBMS will group the rows together.

Simply put if the grouping column contains more than one null value, the null values are put into a single group.

For example, the grouped query:

SELECT A, B, SUM(amount_purchased) AS 'C'
FROM customers
GROUP BY A, B
ORDER BY A, B

will display a results table similar to

ABC
NULLNULL61438.0000
NULL101196156.0000
AZNULL75815.0000
AZ10336958.0000
CA10178252.0000
LANULL181632.0000


for CUSTOMERS that contain the following rows.

ABamount_purchased
NULLNULL45612.00000
NULLNULL15826.00000
NULL10145852.0000
NULL10174815.0000
NULL10175489.0000
AZNULL75815.0000
AZ10336958.0000
CA10178252.0000
LANULL96385.0000
LANULL85247.0000