为什么空记录集上的SUM(…)返回NULL而不是0?

I understand why null + 1 or (1 + null) returns null: null means "unknown value", and if a value is unknown, its successor is unknown as well. The same is true for most other operations involving null.[*]

我理解为什么null + 1或(1 + null)返回null: null表示“未知值”，如果一个值是未知的，那么它的后续值也是未知的。对于涉及null的大多数其他操作也是如此。

However, I don't understand why the following happens:

然而，我不明白为什么会发生以下情况:

SELECT SUM(someNotNullableIntegerField) FROM someTable WHERE 1=0

This query returns null. Why? There are no unknown values involved here! The WHERE clause returns zero records, and the sum of an empty set of values is 0.[**] Note that the set is not unknown, it is known to be empty.

这个查询返回null。为什么?这里没有未知的值!WHERE子句返回零记录，空值集的和为0。注意集合不是未知的，它是空的。

I know that I can work around this behaviour by using ISNULL or COALESCE, but I'm trying to understand why this behaviour, which appears counter-intuitive to me, was chosen.

我知道我可以通过使用ISNULL或合并来处理这种行为，但我试图理解为什么这种行为在我看来是违反直觉的。

Any insights as to why this makes sense?

关于这为什么有意义的见解?

[*] with some notable exceptions such as null OR true, where obviously true is the right result since the unknown value simply does not matter.

[*]除了一些显著的例外，如null或true，显然true是正确的结果，因为未知值根本不重要。

[**] just like the product of an empty set of values is 1. Mathematically speaking, if I were to extend $(Z, +)$ to $(Z union {null}, +)$, the obvious choice for the identity element would still be 0, not null, since x + 0 = x but x + null = null.

就像空值集的乘积是1。从数学上讲，如果我将$(Z， +)$扩展到$(Z union {null}， +)$，那么显然对于identity元素的选择仍然是0，而不是null，因为x + 0 = x，但x + null = null。

2 个解决方案

#1

The ANSI-SQL-Standard defines the result of the SUM of an empty set as NULL. Why they did this, I cannot tell, but at least the behavior should be consistent across all database engines.

ansi - sql标准将空集的和定义为NULL。我不知道他们为什么这样做，但至少所有数据库引擎的行为应该是一致的。

Reference: http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt on page 126:

第126页上的http://www.后悔。andrew.cmu.edu/~影子/sql/sql1992.txt:

b) If AVG, MAX, MIN, or SUM is specified, then

b)如果指定AVG、MAX、MIN或SUM，则
         Case:

         i) If TXA is empty, then the result is the null value.

TXA is the operative resultset from the selected column.

TXA是从选定列中选取的操作结果集。

#2

When you mean empty table you mean a table with only NULL values, That's why we will get NULL as output for aggregate functions. You can consider this as by design for SQL Server.

当你指的是空表时，你指的是一个只有空值的表，这就是为什么我们会得到NULL作为聚合函数的输出。您可以将其视为SQL Server的设计。

Example 1

示例1

CREATE TABLE testSUMNulls
(
    ID TINYINT
)
GO

INSERT INTO testSUMNulls (ID) VALUES (NULL),(NULL),(NULL),(NULL)

SELECT SUM(ID) FROM testSUMNulls

Example 2

示例2

CREATE TABLE testSumEmptyTable
(
    ID TINYINT
)
GO

SELECT SUM(ID) Sums FROM testSumEmptyTable

In both the examples you will NULL as output..

在这两个例子中，你的输出都是空的。

#1