How to Avoid SQL Divide by Zero

Problem

You need to avoid SQL divide by zero errors.

Suppose you need to calculate velocity using the formula v = s / t

Where v = velocity, s = distance, and t = time.  Here is the sample data we’ll use.

select 100 s, 10 t union all
select 110 s, 11 t union all
select 2 s, 0 t union all
select 10 s, 1 t union all
select 120 s, 20 t

Line Three is going to cause us issues, it will throw an SQL divide-by-zero error.

Solution

In the following section we’ll walk through two solutions that take different approaches but solve the sql divide by zero problem the same way.

Solution #1 – Use CASE

Use a case statement to test for a potential divide by zero situation and if you going to have one, then return a result that makes sense for the situation.

In our example, if the time (t) is zero, it results in a SQL divide by zero error, we then return NULL, as the velocity is “undefined.”  This solution works with MS SQL Server, PostgreSQL, and MySQL:

select s, t,
    case when t = 0 then null
          else s/t end v
from (
  select 100 s, 10 t union all
  select 110 s, 11 t union all
  select 2 s, 0 t union all
  select 10 s, 1 t union all
  select 120 s, 20 t
) d

Solution #2 – Use NullIF

The second solution uses NULLIF() to return a NULL when t is null.  It is a more compact form of Solution #1, and it also work with all the databases we cover!

select s, t,
   s / nullif(t,0) v -- null t when 0,  to return null on v
from (
  select 100 s, 10 t union all
  select 110 s, 11 t union all
  select 2 s, 0 t union all
  select 10 s, 1 t union all
 select 120 s, 20 t
) d

SQL Divide By Zero Discussion

For our example I would prefer to use solution #2 as it is more compact.  Some may find a bit harder to read and understand, especially we really null out time (t), and then rely on this to null the entire expression.  Beginners may not get this.   A comment may help to you and them to remember this detail!

In general, the CASE WHEN example provides more flexibility to return varying values whether the divisor is zero or null.

Dig Deeper: SQL CASE Statement – Simple and Searched Forms