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)

Answer 1

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

Answer 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.

Answer 3

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

Answer 4

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? ;-)

Answer 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

Answer 6

This is how i protect against division with zero

very simple

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]

Now the part

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

Is calculating the right number that isn't NULL, if we got zero number because everything was NULL then we got atleast 1 to avoid division with zero happening.

But this was an another case/story correlate to division with zero

Answer 7

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]