Even though both ISNULL and COALESCE is used to provide default values for NULLs there are some key differences between them like ISNULL() is a T-SQL or Microsoft SQL Server-specific function or operator, and datatype and length of the result depends upon a parameter, but COALESCE is a SQL ANSI standard, it can accept more than one parameter and unlike ISNULL, the result doesn't directly depend upon a parameter, it is always the type and length of the value returned.

Difference between COALESCE and ISNULL in SQL Server

Those were some key differences between the coalesce() and isnull() operator. Let's examine those in a little bit more detail now to understand them better.

1) Availability

The COALESCE function is defined by the ANSI SQL standard and supported in all major databases e.g. MySQL, Oracle, PostgreSQL, DB2 etc but ISNULL() is a T-SQL (Transact SQL) function and only work with Microsoft products e.g. Microsoft SQL Server 2004, 2008, 2012, and 2014. When you use coalesce for replacing nulls with default values, your queries become more portable i.e. you can run them on other databases hence It's recommended to use COALESCE over ISNULL wherever possible.

2) Number Of Parameters
The ISNULL() method takes only two parameters, it return the first parameter if its not null and return the second parameter if the first parameter is null. On the contrary, COALESCE can take multiple parameters, and return the NOT NULL parameter starting from first to last. Though, a number of supported parameters depends upon database implementation e.g. in Microsoft SQL Server total number of a supported parameters depends upon the SQL Server version you are running.

3) Function over Expression

I am not fully convinced with this difference because they both look like a function to me but in general ISNULL() is a function while COALESCE is expression in Microsoft SQL Server, but the word function and expression is used interchangeably in this context.

4) Evaluation

This difference is related to the previous difference and somewhat answer the question that why ISNULL is considered as function while COALESCE is considered expression in SQL Server. Since ISNULL() is a function it is only evaluated once, but the input values for the COALESCE expression can be evaluated multiple times. If you want to learn more about performance in SQL Server

5) Example :

Nothing can beat examples to understand the difference between ISNULL and COALESCE in Microsoft SQL Server. Let's see how both handle null values:

ISNULL(Name, '') will return value of column Name if its not null, otherwise it will return empty string ''.

ISNULL(Active, 'N') will return value of column Active otherwise return 'N'

COALESCE(Mobile, Phone1, Phone2) will return value of Mobile column if its NOT NULL, otherwise Phone1, if that is also NULL then value of Phone2.

Here are a couple of more examples of using ISNULL and COALESCE in SQL Server:


6) Type and length of Result

Type of the COALESCE expression is determined by the returned element, while type of the ISNULL function is determined by the first input e.g.

DECLARE
@i AS VARCHAR(4) = NULL,
@j AS VARCHAR(6) = 123456

PRINT ISNULL(@i, @j);   -- outputs 1234
PRINT COALESCE (@i, @j); -- outputs 123456

because in case of ISNULL() type of return element is VARCHAR(4) and not VARCHAR(10)
hence ISNULL() returned 1234 and COALESCE() returned 123456.

Here is another example, which confirms that ISNULL() converts the replacement value (second parameter) to the type of the check expression (first parameter).

Differences between COALESCE vs ISNULL in Microsoft SQL Server


7) Using ISNULL and COALESCE in SELECT INTO Statement


One more subtle difference between COALESCE and ISNULL comes when you are using them in SELECT INTO statements.

Suppose, a SELECT list of SELECT INTO statement contains the expression COALESCE(LeaveBallance, 0) as CarriedForwardLeave vs ISNULL(LeaveBalance, 0) as CarriedForwardLeave.

If the source attribute is defined as NOT NULL then both COALESCE and ISNULL() will create a NOT NULL attribute in the new table. However, if the source attribute, LeaveBalance allows NULLs, then COALESCE will create attribute allowing NULLs, whereas the ISNULL() function will still create an attribute with NOT NULL constraint.

8) Speed

ISNULL is faster than COALESCE in SQL Server because of its a built-in function implemented in the Database engine, while COALESCE translates to CASE statements.


Summary

Here is the nice summary of all the differences between ISNULL() and COALESCE() in Microsoft SQL Server, you can go through it again to revise the concepts you have learned in this article.

Differences between COALESCE and ISNULL in SQL Server

When to use COALESCE and ISNULL function?

Now that you have learned and understood key differences between COALESCE and ISNULL in SQL Server, it's very easy to answer this question.

In general, If you want to check multiple inputs before returning default values then you should use the COALESCE method as it allows multiple inputs, but, if you have just one column then you can use any of them.

By the way, since COALESCE is based on the ANSI SQL standard whereas ISNULL is a proprietary T-SQL function, its better to use COALESCE for database portability reason.

By the way, If you are concerned about performance and only have one value to checked upon, then consider using ISNULL() because it provides better performance since it is an in-built function into database engine and COALESCE is translated into CASE statements.