Tutorial :Microsoft SQL: CASE WHEN vs ISNULL/NULLIF



Question:

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  

vs

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


Solution:1

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

For example, if BeginningQuantity is NULL, the first expression evaluates to 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:

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.

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


Solution: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.


Solution:3

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


Solution:4

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

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


Solution: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


Solution:6

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

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]  

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »