2

This is but one of countless little details that make me frustrated all the time, forces me to memorize all kinds of special code and causes my queries to become uglier than they should have to be.

Try this query on for size:

SELECT sum(amount) FROM table WHERE conditions;

If it finds no records to sum() the amount of, it returns empty/null/undefined instead of 0, causing the output to be unexpectedly empty in my application, instead of the "sum" of zero. But sum() means "the sum", so why not just return 0?

I am aware of the solution. You "simply" do:

SELECT COALESCE(sum(amount), 0) FROM table WHERE conditions;

Now it will return 0 even if there are no records. But it's ugly and it no longer feels "fun" to use. Not that databases are supposed to be a "fun game", but you know what I mean: if a query becomes too convoluted/"ugly", it no longer feels satisfying to use it, especially if you know that this will have to be repeated in all kinds of places and it's not just some obscure, one-off edge case.

What was the thought process behind making it behave like this? I have many other issues related to null, but I'll focus on this one thing for this question.

New contributor
Klappoklang is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
6
  • 3
    Because the sum of "nothing" is "nothing" (unknown) not zero Jan 13 at 14:29
  • 1
    Because the SQL standards committee decided so ;) Jan 13 at 14:31
  • @a_horse_with_no_name the sum of an empty set is most usually defined as 0 in mathematics. The SQL committee decided something else ... Jan 13 at 14:31
  • @ypercubeᵀᴹ: empty is not the same as "unknown" (=null). There is no concept of "unknown/NULL" in mathematics Jan 13 at 14:38
  • I know. But when the conditions restrict the rows to be sum, then we are essentialy summing over an empty set of rows. Jan 13 at 14:53
4

This is specified by the SQL standard in section 4.16.4 Aggregate functions: :

If no row qualifies, then the result of COUNT is 0 (zero), and the result of any other aggregate function is the null value.

0

If it finds no records to sum() the amount of, it returns empty/null/undefined instead of 0, causing the output to be unexpectedly empty in my application, instead of the "sum" of zero. But sum() means "the sum", so why not just return 0?

Simply because:

NULL  !=  0 

NULL is not any kind of default value, it is not any kind of known value.
It is the deliberate means of recording the absence of any, meaningful value.

The sum of NULLs is NULL.
The result of any aggregate function on NULLs is NULL.
The result of any operation at all on NULLs is NULL.

Here's a "fun" one:
NULL is not "equal" to anything else, not even NULL itself!

NULL is NULL    True
NULL =  NULL    False
NULL != NULL    False
5
  • 2
    "The result of any aggregate function on NULLs is NULL.": irrelevant. The sum is over an empty set of rows, there are no NULL values involved. Jan 13 at 14:54
  • True, but there are no /non-NULL/ values either! ;-)
    – Phill W.
    Jan 13 at 15:29
  • I see the joke but still. Your answer focuses on NULL, which I think is irrelevant for the question. Jan 13 at 16:51
  • 1
    NULL = NULL is not False, it is NULL. Same with <>
    – Lennart
    Jan 14 at 10:33
  • I agree NULL is irrelevant here, there are no rows to evaluate at all. More the point, COUNT(*) does return 0 so it's not entirely logical. It might make more sense if scalar aggregates did the same as vector aggregates and returned no rows, but they always return 1 row Jan 14 at 12:21

Your Answer

Klappoklang is a new contributor. Be nice, and check out our Code of Conduct.

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.