Microsoft SQL:CASE WHEN 与 ISNULL/NULLIF [英] Microsoft SQL: CASE WHEN vs ISNULL/NULLIF

问题描述

除了可读性之外，在防止 SQL 中的除以 0 错误时，使用 CASE WHEN 语句与 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?

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

对比

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

推荐答案

请记住 NULL 与 0 不同.因此问题中的两个代码片段对于相同的输入可以返回不同的结果.

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

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

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

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

现在 (NULL + ?) 等于 NULL，NULL=0 为假，所以 ELSE 子句被求值，给出 ?/(NULL+?)，结果为 NULL.但是，第二个表达式变为:

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:

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

这里是NULL+?变为 NULL，并且因为 NULL 不等于 0，所以 NULLIF 返回第一个表达式，即 NULL.外层 ISNULL 捕捉到它并返回 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 除?;-)

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

这篇关于Microsoft SQL:CASE WHEN 与 ISNULL/NULLIF的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！