COALESCE, NULLIF and ISNULL function in SQL

Written on May 12, 2016

COALESCE function in SQL

COALESCE returns first non-null parameter among all parameters passed to it. If all parameters are null then it'll return null. Its syntax is as follows.

COALESCE(arg1, arg2 [,argn])

Let's take an example to get things clear. Suppose we have customer table which is having contact no's like office-phone, mobile-no, home-phone and email. We want to give our sales guys list of customer with best available contact no. What I mean by "best available contact no" is if office phone is not present in our database then we'll return mobile phone. If mobile phone is also not available then we'll return home phone. If home phone is also not available then we'll return email id. Below is SQL query for that.

Now let's analyze results. In case of "Gigi N Matthew", Office mobile no is present, so it is selected. In case of "Michael Raheem" we didn't had office contact no, but mobile no was present, so it selected mobile no.

Now if we look at contact no of "Thierry B D'Hers", if is returning blank value. Although he's having email address. Reason is that he's having blank string as office contact no, which is not NULL.

So to deal with these scenario, we can use NULLIF function. Let's dive into.


NULLIF function in SQL

NULLIF returns null value if both arguments are same. Its syntax is as following.

NULLIF(arg1,arg2)

Below is our modified example with use of NULLIF.

Now, we have contact no of "Thierry B D'Hers", but for customer "Janice M Galvin" we received NULL output. Its because we don't have any information available. So we might want to change out query so instead of returning NULL we can have informative text like ‘'. For this we can use ISNULL function.


ISNULL function in SQL

ISNULL function has two arguments, first argument is variable or expression to evaluate; and second argument is value which we want to return if first argument is NULL. Its syntax is as following.

ISNULL(expression,value)

Following is our modified example with use of ISNULL function.

Now, we got desired output.