SQL Interview: 82 Avoiding divide by zero errors

This is a post in the SQL Interview series. These aren’t trick or gotcha questions, they’re just questions designed to scope out a candidate’s knowledge around SQL Server and Azure SQL Database.

Section: Development Level: Medium

Question:

You regularly execute the following query:

SELECT ProductID,
       UnitPrice / OuterPrice AS PriceRatio
FROM dbo.Products;

When OuterPrice is zero, an error is returned. What is the simplest way to change the query to return NULL for PriceRatio if OuterPrice is zero, without wrapping the expression in a CASE statement?

Answer:

The simplest option is:

SELECT ProductID,
       UnitPrice / NULLIF(OuterPrice, 0) AS PriceRatio
FROM dbo.Products;

You could also use a CASE statement, but it would not meet the stated requirement, and would not be simpler anyway.

2025-09-29