在SQL server中ISNULL(SUM(x)，0)或SUM(ISNULL(x,0)之间的区别。

Which one of the following is correct?

下面哪个选项是正确的?

        SUM(ISNULL(Sales,0)) AS Sales,
        ISNULL(SUM(Sales),0) AS Sales,

Or are they both correct?

或者他们都是对的?

4 个解决方案

#1

They both return the same except if you are running a query on an empty result set.

除了在空结果集中运行查询之外，它们都返回相同的结果。

WITH Sales(Sales) AS
(
SELECT 1
)
SELECT
        SUM(ISNULL(Sales,0)) AS Sales,
        ISNULL(SUM(Sales),0) AS Sales
FROM     Sales    
WHERE 1=0

Returns

Sales       Sales
----------- -----------
NULL        0

The SUM(ISNULL(Sales,0)) version would avoid the ANSI WARNINGS about aggregating NULL.

SUM(ISNULL(Sales,0))版本将避免关于聚合NULL的ANSI警告。

One other subtle difference is that the datatype of the result column of ISNULL(SUM(Sales),0) is not regarded as nullable.

另一个微妙的区别是ISNULL(SUM(Sales)，0)结果列的数据类型不被认为是可空的。

#2

Try this:

试试这个:

DECLARE @table TABLE
(
  id INT IDENTITY,
  Alborz INT
)
INSERT  INTO @table
        SELECT  1
        UNION ALL
        SELECT  1
        UNION ALL
        SELECT  1
        UNION ALL
        SELECT  1
        UNION ALL
        SELECT  1
        UNION ALL
        SELECT  NULL

SELECT  ISNULL(SUM(Alborz), 0)
FROM    @table
SELECT  SUM(ISNULL(Alborz, 0))
FROM    @table

DELETE  FROM @table

SELECT  ISNULL(SUM(Alborz), 0)
FROM    @table
SELECT  SUM(ISNULL(Alborz, 0))
FROM    @table

You'll get 21, 21, 0, and NULL. If you don't want to handle nulls, then ISNULL(SUM(X),0) is the way to go.

你会得到21 21 21 0零。如果您不想处理nulls，那么ISNULL(SUM(X)，0)就是一个不错的选择。

#3

The first one says

第一个说

SUM up every Sales field, and if the field is NULL, treat it as zero.

将每个销售字段相加，如果该字段为空，则将其视为零。

The second says Sum up the sales field, and if the total is NULL, report a zero instead...

第二种是总结销售领域，如果总数为零，报告为零……

However, the SUM() command skips NULL (although you'll be warned about it), so the first one will cause you not to get the error message

然而，SUM()命令跳过NULL(尽管您会被警告)，因此第一个命令将导致您无法获得错误消息

#4

There is a difference yes. If I am not mistaken

有区别，是的。如果我没弄错的话

5 + NULL = NULL

so the first case

第一个案例

SUM(ISNULL(Sales,0)) AS Sales

would change the null value to 0 and then sum them which would return 5
while the second case

是否会将空值改为0，然后将它们相加得到5，而在第二种情况下

ISNULL(SUM(Sales),0) AS Sales,

Would return null in the same data

在相同的数据中返回null吗

#1