There is a strange behaviour of TSQL:
TSQL有一种奇怪的行为:
SELECT 1 + NULL
returns NULL,
返回NULL,
while
而
SELECT sum(someColumn)
will always return an integer, ignoring null values, unless the whole column is null (in which case it returns null).
将始终返回一个整数，忽略空值，除非整个列是null(在这种情况下，它返回null)。
What are the design choices behind the decision to make binary operators different in meaning from matching aggregate functions?
使二进制运算符在意义上与匹配聚合函数不同的决策背后的设计选择是什么?
Is there a way to overcome this 'limitation', except litteraly stuffing my SQL with coalesce()?
是否有办法克服这种“限制”，除了litteraly将我的SQL合并到合并()之外?
(edited after comments)
(编辑评论后)
1 个解决方案
#1
4
This behaviour is common to most forms of SQL - in single-row level arithmetic operations, "(b)ecause Null is not a data value, but a marker for an unknown value, using mathematical operators on Null results in an unknown value, which is represented by Null."
这种行为在SQL的大多数形式中都很常见——在单行级别的算术操作中，“(b)因为Null不是一个数据值，而是一个未知值的标记，使用对Null的数学运算符生成一个未知值，该值由Null表示。”
By contrast, in aggregate functions (ie. operations across multiple rows), "all aggregate functions perform a Null-elimination step, so that Null values are not included in the final result of the calculation."
相比之下，在聚合函数中。“所有聚合函数执行一个Null-elimination步骤，因此Null值不会包含在计算的最终结果中。”
Both quoted behaviours have references in the Wikipedia article, citing the ISO/IEC standards where these behaviours are defined.
这两种引用的行为在*的文章中都有引用，引用了定义这些行为的ISO/IEC标准。
#1
4
This behaviour is common to most forms of SQL - in single-row level arithmetic operations, "(b)ecause Null is not a data value, but a marker for an unknown value, using mathematical operators on Null results in an unknown value, which is represented by Null."
这种行为在SQL的大多数形式中都很常见——在单行级别的算术操作中，“(b)因为Null不是一个数据值，而是一个未知值的标记，使用对Null的数学运算符生成一个未知值，该值由Null表示。”
By contrast, in aggregate functions (ie. operations across multiple rows), "all aggregate functions perform a Null-elimination step, so that Null values are not included in the final result of the calculation."
相比之下，在聚合函数中。“所有聚合函数执行一个Null-elimination步骤，因此Null值不会包含在计算的最终结果中。”
Both quoted behaviours have references in the Wikipedia article, citing the ISO/IEC standards where these behaviours are defined.
这两种引用的行为在*的文章中都有引用，引用了定义这些行为的ISO/IEC标准。