4 ms·

I'm also curious as to why this is the case [?]. A quick search yielded a discussion on StackOverflow [1], where people were similarly surprised, but at least

I'm also curious as to why this is the case [?]. A quick search yielded a discussion on StackOverflow [1], where people were similarly surprised, but at least pointed out that this is spelled out in the SQL 92 spec [2] (page 126). The relevant part: b) If AVG, MAX, MIN, or SUM is specified, then Case: i) If TXA is empty, then the result is the null value. [1] https://stackoverflow.com/questions/12730457/why-does-sum-on-an-empty-recordset-return-null-instead-of-0 https://stackoverflow.com/questions/12730457/why-does-sum-on... [2] http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt [?] As in, the design decision behind it. By comparison, the NULL + 10 = NULL concept seems obvious.

heavenlyblue 9y ago
It seems that way you get more information from a sibggle operation.
mannykannot 9y ago
Indeed. The cardinality of the empty set is zero, right? - and would there not be all sorts of problems in the formalization of number theory if this were not the case? Now you have me wondering if, for any given database containing nulls, there is an equivalent normalized schema that represents the same information without nulls - for example, if you have a Student table with a Date of Graduation attribute that might be null, use instead an additional table of Graduated Students. It looks to me like there might be some tricky corner cases in defining 'equivalent' (you cannot query the second database for students with null graduation dates, but you can query for those without graduation dates.) If so, then the follow-up question is whether there is a three-valued logic that always gives the same result as a particular null-free representation (my guess is yes and no, respectively.)
- jessaustin 9y ago
  Another difference is that the latter schema would allow students to have multiple graduations. Which could be totally valid, but in that sense it's not equivalent to the former schema.
  - mannykannot 9y ago
    For the purposes my question, it would be a complication, but resolvable by using a unique identifier. Perhaps I should have limited my speculation to databases that are in at least first normal form.
    - jessaustin 9y ago
      I'm not sure how the 1NF limitation helps the question. One-to-many relations can certainly exist in 1NF. If the question is amended to specify that the table that represents the nullable column must have its primary key also be its foreign key to the main table, then I agree that the answer is "yes".
      - mannykannot 9y ago
        I thought 1NF was a solution to a follow-on problem with handling repeating groups, that, on reflection, I see does not exist.

heavenlyblue 9y ago

It seems that way you get more information from a sibggle operation.

mannykannot 9y ago

Indeed. The cardinality of the empty set is zero, right? - and would there not be all sorts of problems in the formalization of number theory if this were not the case? Now you have me wondering if, for any given database containing nulls, there is an equivalent normalized schema that represents the same information without nulls - for example, if you have a Student table with a Date of Graduation attribute that might be null, use instead an additional table of Graduated Students. It looks to me like there might be some tricky corner cases in defining 'equivalent' (you cannot query the second database for students with null graduation dates, but you can query for those without graduation dates.) If so, then the follow-up question is whether there is a three-valued logic that always gives the same result as a particular null-free representation (my guess is yes and no, respectively.)

jessaustin 9y ago
Another difference is that the latter schema would allow students to have multiple graduations. Which could be totally valid, but in that sense it's not equivalent to the former schema.
- mannykannot 9y ago
  For the purposes my question, it would be a complication, but resolvable by using a unique identifier. Perhaps I should have limited my speculation to databases that are in at least first normal form.
  - jessaustin 9y ago
    I'm not sure how the 1NF limitation helps the question. One-to-many relations can certainly exist in 1NF. If the question is amended to specify that the table that represents the nullable column must have its primary key also be its foreign key to the main table, then I agree that the answer is "yes".
    - mannykannot 9y ago
      I thought 1NF was a solution to a follow-on problem with handling repeating groups, that, on reflection, I see does not exist.