Microsoft SQL：CASE WHEN vs ISNULL / NULLIF

Besides readability is there any significant benifit to using a CASE WHEN statement vs ISNULL/NULLIF when guarding against a divide by 0 error in SQL?

除了可读性之外,在SQL中防止除以0错误时使用CASE WHEN语句与ISNULL / NULLIF有什么显着的好处?

CASE WHEN (BeginningQuantity + BAdjustedQuantity)=0 THEN 0 
ELSE EndingQuantity/(BeginningQuantity + BAdjustedQuantity) END

ISNULL((EndingQuantity)/NULLIF(BeginningQuantity + BAdjustedQuantity,0),0)

6 个解决方案

#1

Remember that NULL is different from 0. So the two code snippets in the question can return different results for the same input.

请记住,NULL与0不同。因此,问题中的两个代码片段可以为同一输入返回不同的结果。

For example, if BeginningQuantity is NULL, the first expression evaluates to NULL:

例如,如果BeginningQuantity为NULL,则第一个表达式的计算结果为NULL:

CASE WHEN (NULL + ?)=0 THEN 0 ELSE ?/(NULL + ?) END

Now (NULL + ?) equals NULL, and NULL=0 is false, so the ELSE clause is evaluated, giving ?/(NULL+?), which results in NULL. However, the second expression becomes:

Now(NULL +?)等于NULL,NULL = 0为false,因此评估ELSE子句,给出?/(NULL +?),结果为NULL。但是,第二个表达式变为:

ISNULL((?)/NULLIF(NULL + ?,0),0)

Here NULL+? becomes NULL, and because NULL is not equal to 0, the NULLIF returns the first expression, which is NULL. The outer ISNULL catches this and returns 0.

这里是NULL +?变为NULL,并且因为NULL不等于0,所以NULLIF返回第一个表达式,即NULL。外部ISNULL捕获此并返回0。

So, make up your mind: are you guarding against divison by zero, or divison by NULL? ;-)

所以,请下定决心:你是用零来防止divison,还是用NULL来区分? ;-)

#2

In your example I think the performance is negligible. But in other cases, depending on the complexity of your divisor, the answer is 'it depends'.

在你的例子中,我认为性能可以忽略不计。但在其他情况下,根据你的除数的复杂性,答案是“它取决于”。

Here is an interesting blog on the topic:

这是一个关于这个主题的有趣博客:

For readability, I like the Case/When.

为了便于阅读,我喜欢Case / When。

#3

In my opinion, using Isnull/Nullif is faster than using Case When. I rather the isnull/nullif.

在我看来,使用Isnull / Nullif比使用Case When更快。我更喜欢isnull / nullif。

#4

I would use the ISNULL, but try to format it so it shows the meaning better:

我会使用ISNULL,但尝试将其格式化以便更好地显示其含义:

SELECT
    x.zzz
        ,x.yyyy
        ,ISNULL(
                   EndingQuantity / NULLIF(BeginningQuantity+BAdjustedQuantity,0)
                ,0)
        ,x.aaa
    FROM xxxx...

#5

CASE WHEN (coalesce(BeginningQuantity,0) + coalesce(BAdjustedQuantity,0))=0 THEN 0 ELSE coalesce(EndingQuantity,0)/(coalesce(BeginningQuantity,0) + coalesce(BAdjustedQuantity,0)) END

your best option imho

你最好的选择imho

#6

Sorry, here is the little more simplify upbuilded sql query.

对不起,这里是更简化的upbuilded sql查询。

SELECT 

(ISNULL([k1],0) + ISNULL([k2],0)) /

CASE WHEN (
(
   CASE WHEN [k1] IS NOT NULL THEN 1 ELSE 0 END +
   CASE WHEN [k2] IS NOT NULL THEN 1 ELSE 0 END
) > 0 )
THEN
(
  CASE WHEN [k1] IS NOT NULL THEN 1 ELSE 0 END +
  CASE WHEN [k2] IS NOT NULL THEN 1 ELSE 0 END
)
ELSE 1 END

FROM dbo.[Table]

#1