Using NULLIF() to Handle the “Divide by Zero” Error in SQL Server

by

If you’ve written SQL long enough, you’ve probably run into the dreaded “Divide by zero error encountered” message. This happens when you try to perform a division and the denominator turns out to be zero. SQL Server throws an error immediately, which stops your query. This can be annoying, especially if the zero values are expected in the data but you don’t want them breaking your query.

One simple way to deal with this is by using the NULLIF() function.

What NULLIF() Does

SQL Server’s NULLIF() function compares two expressions and returns NULL if they are equal. If they’re not equal, it simply returns the first expression. That’s it.

The syntax goes like this:

NULLIF ( expression , expression )

This is useful because if you wrap your denominator in NULLIF(), then whenever the denominator equals zero, SQL Server will substitute it with NULL instead. Since dividing by NULL doesn’t throw an error (it just results in NULL), you avoid the divide by zero issue entirely.

Example

Let’s say you have a table that tracks product sales and returns:

CREATE TABLE SalesSummary (
    ProductID INT,
    TotalSales DECIMAL(10,2),
    TotalReturns DECIMAL(10,2)
);

INSERT INTO SalesSummary VALUES
(1, 1000, 50),
(2, 500, 0),
(3, 0, 0);

Now you want to calculate the return rate for each product, which is simply:

Return Rate = TotalReturns / TotalSales

If you try to run:

SELECT 
    ProductID,
    TotalReturns / TotalSales AS ReturnRate
FROM SalesSummary;

SQL Server will fail on the third row because TotalSales is 0.

Here’s what I get when I run that code:

Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.

Here’s where NULLIF() helps:

SELECT 
    ProductID,
    TotalReturns / NULLIF(TotalSales, 0) AS ReturnRate
FROM SalesSummary;

Output:

ProductID  ReturnRate
---------  ----------
1          0.05      
2          0         
3          null

Notice that for the third product, instead of an error, we get NULL. This tells us that there was no way to calculate the rate since the denominator was zero.

Benefits

The benefits of this approach include:

  • Prevents errors: The query always runs, even if zero values exist.
  • Keeps results meaningful: Instead of substituting arbitrary values like 0 or 1, NULL communicates that the calculation isn’t valid.
  • Simple to use: Just wrap your denominator with NULLIF(x, 0) and you’re done.

Alternatives

NULLIF() is great, but you might want different behavior. For example if you’d rather return 0 instead of NULL, you can wrap it in COALESCE():

SELECT 
    ProductID,
    COALESCE(TotalReturns / NULLIF(TotalSales, 0), 0) AS ReturnRate
FROM SalesSummary;

Output:

ProductID  ReturnRate
---------  ----------
1          0.05      
2          0         
3          0

If the denominator is rarely zero and you want to exclude those rows altogether, a simple WHERE TotalSales > 0 filter could be cleaner:

SELECT 
    ProductID,
    COALESCE(TotalReturns / NULLIF(TotalSales, 0), 0) AS ReturnRate
FROM SalesSummary
WHERE TotalSales > 0;

Output:

ProductID  ReturnRate
---------  ----------
1          0.05      
2          0

Using NULLIF() is just one of many different approaches for solving the “divide by zero” error. See 5 Ways to Fix the “Divide by zero error” in SQL Server (Msg 8134) for more ideas on addressing this error.