Using of COALESCE() and ISNULL() in Sql Server

COALESCE() function -The COALESCE function returns the first non-NULL value from a provided list of expressions. The COALESCE function is passed an undefined number of arguments and it tests for the first nonnull expression among them.If all arguments are NULL then COALESCE returns NULL.

The syntax is as follows:

COALESCE(expression [,...n])

Suppose we have 'employee' table with some data-

we can see that there are some null in mobile column and some null in phone column, so if we need contact details of employee,it may be mobile or phone(if mobile is null then phone and if both are null then it will return null)

so In this case we have to use COALESCE function.

After executing this query, we have found this result-

this is all about COALESCE() function , now Let's discuss about ISNULL() function-

ISNULL() function-

ISNULL validates if an expression is NULL, and if so, replaces the NULL value with an alternate value. In this example, any NULL value will be replaced with a different value.

Suppose we want to mobile number of employee table, and if any value is null in mobile column then we have to return 'unknown' word in place of null as a alternative value,then we write following query -

The result set is come in this way-

Difference between ISNULL and COALESCE-

• ISNULL is limited to two arguments but in COALESCE we can use more number of arguments
• Performancewise COALESCE is faster than ISNULL
• COALESCE is ANSI SQL standard whereas ISNULL is a proprietary TSQL function
• Using select ISNULL(NULL, NULL) doesn’t throw error but select COALESCE(NULL, NULL) throw an  error