Understanding SUM(NULL) in MySQL

问题:

Usually when NULL is involved in any equation then the whole result resolves into NULL (e.g. SELECT 2 + NULL + 5 returns NULL)

Same holds for the following case:

SELECT SUM(NULL) returns NULL. Proposition #1

What happens when SUM is used to aggregate a column and the column can contain NULL values too ?

Based on the proposition #1 why the output doesn't result in NULL.

CREATE TABLE t (age INT NULL);

INSERT INTO t (age)  VALUES (15),(20), (NULL), (30), (35);

SELECT 
SUM(age)
FROM t;

Output: 100

But I was expecting NULL.

Does MySQL silently skips those NULL values in this case?

http://sqlfiddle.com/#!9/3f99bb/2

回答1:

Well it's explained in the manual

SUM([DISTINCT] expr)
Returns the sum of expr. If the return set has no rows, SUM() returns NULL. The DISTINCT keyword can be used to sum only the distinct values of expr.

SUM() returns NULL if there were no matching rows.

What's more it's also said that:

This section describes group (aggregate) functions that operate on sets of values. Unless otherwise stated, group functions ignore NULL values.

in other words SUM behaves like this because that's the way it's defined to be.

回答2:

You are right, aggregate functions handles null values in a different way than non aggregate functions:

select 2 + NULL + 5

returns NULL because NULL on this context it means an unknown value, so the result will be NULL (unknown) as well.

This will return 7 instead:

select SUM(n)
from (
  select 2 as n
  union all select null
  union all select 5
) s

because on this context a NULL value, even if it is unknown, can be seen as a "not specified value". The reason is that it's very common to use NULL as a not specified value and it's little pratical use to include NULLs in an aggregate function, that's why aggregate functions are defined to ignore NULLs.