SQL Tips - How to avoid Divide By Zero Error (NULLIF and CASE)

When you're performing a division operation then you're likely to run into a divide by zero error when your denominator is a zero.

For example in this test data we have declared two variables 

declare @num int = 5;

declare @den int = 0;

The numerator is 5 and the denominator is zero.

If we simply try to perform a division  we might be running into an error

Select @num/@den

There are certain solutions that you can apply to fix this error.

In the first solution,  we are going to see how we can avoid it using a case statement .

We are going to check in the Case statement if the denominator is equal to 0.

If the denominator is 0, we will output the result as NULL else we will perform the division.

Select Case WHEN @den = 0 Then NULL

           Else @num/@den End 

Now there's another way using an interesting function in SQL Server which is a NULLIF function.

But first lets understand how a NULLIF function works.

The NULLIF function expects two arguments. 

If the two arguments have the same value, it outputs a NULL.

If the two arguments have different values, it outputs the value of the first argument.

For example - 

Select NULLIF(1,1)

NULL

Select NULLIF(1,2)

1

Using this logic, lets compare the denominator value to 0. 

Select NULLIF(@den,0)

NULL

Since the value of denominator(@den) is 0, so the values of the two arguments match and hence the output is NULL.

If we now change the value of denominator to 1

declare @num int = 5;

declare @den int = 1;

Select NULLIF(@den,0)

1

The output in this case is 1, since the values of the two arguments do not match and therefore, NULLIF function returns the value of the first argument which is 1.

Now to solve our Divide By Zero error, we simple need to execute the below SQL statement -

Select @num/NULLIF(@den,0)

In SQL, dividing by NULL results in NULL, so if the denominator value is 0, the NULLIF function will return a NULL and numerator divided by NULL will return NULL in the final output.

This is one interesting way to solve the Divide By Zero error. 

We have many other interesting blog posts on our blog. Please check them out and learn new SQL tips and tricks!