为什么在Oracle中SUM（null）不为0？ [英] Why SUM(null) is not 0 in Oracle?

问题描述

当遇到空值时，请解释Oracle中SUM函数的内部功能：

It would be appreciated explaining the internal functionality of SUM function in Oracle, when encountering null values:
The result of

select sum(null) from dual;
is null

但是当一个空值在一个值序列一个可为NULL的列），计算出的null值为0

But when a null value is in a sequence of values (like sum of a null-able column), the calculated value of null value will be 0

select sum(value) from
(
select case when mod(level , 2) = 0 then null else level end as value from dual
connect by level <= 10
)
is 25

当看到
的结果时，这将更有趣

This will be more interesting when seeing the result of

select (1 + null) from dual
is null

由于任何带null的操作都会导致null（除了是null 运算符）。


========================

由于

As any operation with null will result null (except is null operator).

==========================
Some update due to comments:

create table odd_table as select sum(null) as some_name from dual;

会导致：

create table ODD_TABLE
(
  some_name NUMBER
)

为什么 some_name 列的类型为 number ？

推荐答案

在计算 SUM 时，SQL不会将 NULL 值视为零他们：

SQL does not treat NULL values as zeros when calculating SUM, it ignores them:

返回所有值的总和，或只返回 DISTINCT ，在表达式中。

这只会在一种情况下产生差异 - 当总计的序列不包含数字项时， NULL s：如果至少有一个数字，结果将是数字。

This makes a difference only in one case - when the sequence being totalled up does not contain numeric items, only NULLs: if at least one number is present, the result is going to be numeric.

这篇关于为什么在Oracle中SUM（null）不为0？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！