Why does ANSI SQL define SUM(no rows) as NULL?

Question

The ANSI SQL standard defines (chapter 6.5, set function specification) the following behaviour for aggregate functions on empty result sets:

COUNT(...) = 0
AVG(...) = NULL
MIN(...) = NULL
MAX(...) = NULL
SUM(...) = NULL

Returning NULL for AVG, MIN and MAX makes perfect sense, since the average, minimum and maximum of an empty set is undefined.

The last one, however, bothers me: Mathematically, the SUM of an empty set is well-defined: 0. Using 0, the neutral element of addition, as the base case makes everything consistent:

SUM({})        = 0    = 0
SUM({5})       = 5    = 0 + 5
SUM({5, 3})    = 8    = 0 + 5 + 3
SUM({5, NULL}) = NULL = 0 + 5 + NULL

Defining SUM({}) as null basically makes "no rows" a special case that does not fit in with the others:

SUM({})     = NULL  = NULL
SUM({5})    = 5    != NULL + 5 (= NULL)
SUM({5, 3}) = 8    != NULL + 5 + 3 (= NULL)

Is there some obvious advantage of the choice that was made (SUM being NULL) that I have missed?

Answer 1

I'm afraid that the reason is simply that the rules were set in an adhoc fashion (like quite many other "features" of the ISO SQL standard) at a time when SQL aggregations and their connection with mathematics were less understood than they are now (*).

It's just one of the extremely many inconsistencies in the SQL language. They make the language harder to teach, harder to learn, harder to understand, harder to use, harder to whatever you want, but that's just the way things are. The rules cannot be changed "cold" and "just like that", for obvious reasons of backward compatibility (If the ISO committee publishes a final version of the standard, and vendors then set out to implement that standard, then those vendors will not appreciate it very much if in a subsequent version, the rules are changed such that existing (compliant) implementations of the former version of the standard "automatically fail to comply" the new version ...)

(*) It is now better understood that aggregations over an empty set behave more consistently if they systematically return the identity value (= what you call the 'neutral element') of the underlying binary operator at hand. That underlying binary operator for COUNT and SUM is addition, and its identity value is zero. For MIN and MAX, that identity value is the highest and lowest value of the type at hand, respectively, if the concerned types are finite. Cases like averaging, harmonic means, medians, etc. are extremely intricate and exotic in this respect, though.

Answer 2

In a pragmatic sense the existing result of NULL is useful. Consider the following table and statements:

C1 C2
-- --
 1  3 
 2 -1 
 3 -2 

SELECT SUM(C2) FROM T1 WHERE C1 > 9;

SELECT SUM(C2) FROM T1 WHERE C1 < 9;

The first statement returns NULL and the second returns zero. If an empty set returned zero for SUM we would need another means to distinguish a true sum of zero from an empty set, perhaps using count. If we indeed want zero for the empty set then a simple COALESCE will furnish that requirement.

SELECT COALESCE(SUM(C2),0) FROM T1 WHERE C1 > 9;

Answer 3

The main difference I can see is with regard to the datatype. COUNT has a well defined returntype: A whole number. All the others depend on the type of the column/expression they are looking at. Their return type must be compatible with all members of the set (think float, currency, decimal, bcd, timespan, ...). Since there is no set you cannot imply a return type, thus NULL is your best option.

Note: In most cases you could imply a return type from the column type you are looking at, but you can do SUMs not only on columns but on all kinds of things. Implying a return type might get very difficult if not impossible under certain circumstances, especially when you think about possible expansions of the standard (dynamic types come to mind).