为什么 TSQL 运算符 + 的行为与聚合函数 sum() 不同 [英] Why does TSQL operator + behave differently than aggregation function sum()

问题描述

TSQL 有一个奇怪的行为:

There is a strange behaviour of TSQL:

SELECT 1 + NULL

返回 NULL,

同时

SELECT sum(someColumn)  

将始终返回一个整数，忽略空值，除非整列为空(在这种情况下它返回空值).

will always return an integer, ignoring null values, unless the whole column is null (in which case it returns null).

使二元运算符与匹配聚合函数的含义不同的决定背后的设计选择是什么?

What are the design choices behind the decision to make binary operators different in meaning from matching aggregate functions?

有没有办法克服这种限制"，除了用 coalesce() 随意填充我的 SQL?

Is there a way to overcome this 'limitation', except litteraly stuffing my SQL with coalesce()?

(评论后编辑)

推荐答案

这种行为在大多数 SQL 形式中都很常见 - 在单行级别 算术运算, "(b) 因为 Null 不是数据值，而是未知值的标记，对 Null 使用数学运算符会导致未知值, 用 Null 表示."

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."

相比之下，在聚合函数(即跨多个操作行)，所有聚合函数都执行 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."

这两个引用的行为在维基百科文章中都有引用，引用了定义这些行为的 ISO/IEC 标准.

Both quoted behaviours have references in the Wikipedia article, citing the ISO/IEC standards where these behaviours are defined.

这篇关于为什么 TSQL 运算符 + 的行为与聚合函数 sum() 不同的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！