Two Ways to Prevent Division by Zero in SQL

Published: May 12, 2010

by Ted Holt

Everybody above the age of 8 knows that division by zero is a no-no, or at least they should. Everybody who's used SQL for any amount of time knows that we can use a CASE expression to prevent division by zero. But do you know the other way to prevent division by zero in SQL?

Assume a database file called SomeTable, with fields called Quantity and Amount. Let's divide Amount by Quantity. First, here's the usual case statement.

select itnbr, Amount, Quantity,
       case when Quantity <> 0
               then Amount / Quantity
            else 0 end
  from SomeTable

If Quantity is not zero, the division takes place. If Quantity is zero, the calculation yields a zero. We could just as easily return any other numeric value, the value one, for example.

select itnbr, Amount, Quantity,
       case when Quantity <> 0
               then Amount / Quantity
            else 1 end
  from SomeTable

The other way to prevent division by zero is to use the NULLIF function. NULLIF requires two arguments. If the arguments are equal, NULLIF returns a null value. If they are not equal, NULLIF returns the first value.

Here's the SQL.

select itnbr, Amount, Quantity,
       Amount / nullif(Quantity,0)
  from SomeTable

If Quantity is not zero, the two arguments do not match, and the resulting calculation is Amount divided by Quantity. But if Quantity is zero, the arguments do match, and the calculation is Amount divided by null, which yields a null value.

If you use this technique, be sure to put the zero in the second argument of NULLIF.

                     Post this story to del.icio.us
               Post this story to Digg
    Post this story to Slashdot