Rarely Used but Useful SQL Function "NULLIF"

We all are very well aware and familiar with the function IFNULL, which is used to check if the value is NULL and provide replacement value for NULL.

Recently I came across one more useful SQL function "NULLIF". This works exactly opposite to IFNULL. In here you can find for some specific value and can replace it with NULL.
Now you will wonder why we may need to replace some useful value with NULL. Well I have one situation for you. Suppose you are doing some mathematical operations and you have to account for division by zero. Instead of writing multiple checks and IF clauses, NULLIF function comes in handy.

One of the simplest solutions is to do something like this:

SELECT @v1 / NULLIF( @v2, 0)

This will return NULL if division was invalid (Division by zero) or else
it will give the proper result.

If you prefer the result of an invalid division to be zero, you can add
a COALESCE:

SELECT COALESCE( @v1 / NULLIF(@v2,0), 0)


Hope that you will also find this function useful. Feel free to put your comments.