Handling divide by zero in SQL

Division by zero is something that, sooner or later, we all have to capture. For the rest of this post, I am using a small temporary table that I have imaginatively named DIV0. It has two columns, COLUMN1 and COLUMN2 and it looks like this:

COLUMN1   COLUMN2
      5         0
      6         2

Until recently, I have tended to use a CASE statement to check the column values before attempting to divide. It looks something like this:

select
    COLUMN1, COLUMN2,  
    case when COLUMN2 = 0 then null else COLUMN1/COLUMN2 end as DIVIDED
from div0

… and the output will look something like this:

COLUMN1   COLUMN2                             DIVIDED 
      5         0                                    -
      6         2        3.00000000000000000000000000 

This is fine in as far as it goes, but when COLUMN2 is replaced with a calculation, it can all get very messy very quickly.

It turns out that the NULLIF function provides a better approach.

The NULLIF function returns a null value if the arguments compare equal, otherwise it returns the value of the first argument.

What this means is that I can simplify my earlier query to look like this:

select                                     
    COLUMN1, COLUMN2,                      
    COLUMN1 / NULLIF(COLUMN2, 0) as DIVIDED
from div0                                  

And the output remains the same.