Use cases for NULLIF

Question: I understand the theory behind NULLIF – based on the MS documentation “Returns a null value if the two specified expressions are equal”.

But I’ve rarely had the opportunity to apply this function in real word scenarios . Can you expand on some real world scenarios for NULLIF ?

Answer: You are right – it’s not a widely used function , I think mainly because the more widely used expression CASE is used

Basically

NULLIF(value1,value2)

Is the equivalent to

CASE WHEN value1 = value2 THEN NULL ELSE value1 END

Using CASE gives you the advantage of utilising other expressions within the CASE structure.

I’ve seen also used to identify empty strings and convert the empty string into a NULL. So that would be : NULLIF(col1, '').   I prefer to not convert to NULL as that can lead to ambiguity , so I would translate into something else – such as a -1

As a general usage pattern – NULLIF is useful in cleansing data. You may need to prepare some data or integrate different data sets and find there are all sorts of empty strings, NULLs etc.

Developers have been known to avoid \workaround the classic “Division by zero” error and utilise the NULLIF.

--will raise an Divide by zero error encountered if the denominator = 0 
select value1 / value2
from (
	select 1 as value1, 0 as value2
	) t1

--will not raise an error if the numerator has a value and denoninator = 0
select value1 / nullif (value2, 0)
from (
	select 1 as value1, 0 as value2 
) t2

Read More on t-sql