SQL Interview: 17: Using NULLIF

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 see the following statement in a query that you are reviewing.

What is the purpose of the NULLIF function in this statement?

SELECT @a / NULLIF(@b, 0);

What was the author trying to achieve?

Answer:

The variable @a is being divided by the variable @b, but the variable @b might be zero. In that case, a divide by zero error would be returned.

By adding the NULLIF, if @b is zero, NULL would be returned, and the variable @a would be divided by NULL instead of zero. That would then safely return NULL as the result.

2025-02-05