Difference between IsNull and Coalesce in Microsoft SQL Server (with Examples)

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. Also, what is the difference between COALESCE and ISNULL is one of the frequently asked Microsoft SQL Server interview questions, and knowing these differences can help in both your day-to-day SQL development works and during job interviews.

Recently, one of my readers asked the same question to me, he got confused between these two because both are used to replace NULL values with default values in SQL Server. I'll try to answer this question here with a little bit of insight about when to use ISNULL and COALESCE in SQL Server. 

This is an extremely important topic for anyone using SQL Server e.g. programmers who are working on an application using Microsoft SQL Servers or DBA, who is writing stored procedures, functions, and other database objects to deal with null values effectively, mainly while generating reports.

I always ask this question to anyone who claims to know about SQL Server and has used it in the past. It is one of those questions, which you can use to filter the candidates who really have used SQL Server and who just claim to.

By the way, if you are new to Microsoft SQL Server and T-SQL then I also suggest you join a comprehensive course to learn SQL Server fundamentals and how to work with T-SQL. If you need a recommendation then I suggest you go through the Microsoft SQL for Beginners online course by Brewster Knowlton on Udemy. It's a great course to start with T-SQL and SQL queries in SQL Server.

SQL Server ISNULL and COALESCE Example

Nothing can beat examples to understand the difference between ISNULL and COALESCE in Microsoft SQL Server, so before exploring the technical differences let's see some examples of how to use ISNULL() and COALESCE()  function in SQL Server. 

Let's see how both handle null values:

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

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

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

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

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 like MySQL, Oracle, PostgreSQL, DB2, etc but ISNULL() is a T-SQL (Transact SQL) function and only work with Microsoft products like 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 returns the first parameter if it's not null and returns 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. 

However, a number of supported parameters depend upon database implementation, for example, in Microsoft SQL Server total number of 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 an expression in Microsoft SQL Server, but the word function and expression are used interchangeably in this context.

4. Evaluation 

This difference is related to the previous difference and somewhat answers the question that why ISNULL is considered as a function while COALESCE is considered an 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, I also suggest reading Pro SQL Server Internals 2nd Edition by Dmitri Korotkevitch, you will find a lot of such details in this book. You can also check out these SQL Server online courses if you like guided learning. 

5. Type and length of Result 

Type of the COALESCE expression is determined by the returned element, while the type of the ISNULL function is determined by the first input like:

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 the 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).

6. 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 statements 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 an attribute allowing NULLs, whereas the ISNULL() function will still create an attribute with NOT NULL constraint. 

You can further see Microsoft SQL Server 2012 Internals (Developer Reference) 1st Edition by Kalen Delaney and others to learn more about this behavior in Microsoft SQL Server.

7. Speed 

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

When to use COALESCE and ISNULL in SQL?

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, it's better to use COALESCE for database portability reasons.

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.

If you want to learn more, I suggest joining these free SQL Server courses which explain many SQL Server concepts with nice explanations and examples. Most of the concept I learned clearly is only after going through these course sand books. It helped to clear many doubts and misconceptions I have about certain features of SQL Server.

Summary 

Here is a 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.

That's all in the difference between ISNULL and COALESCE in Microsoft SQL Server. It's one of the frequently asked SQL Server interview questions, hence you must know and understand the concept clearly to answer this and all follow-up questions. The topic can be really tricky for casual SQL developers who claim to be working or using Microsoft SQL server for a couple of years but don't know much about ISNULL and COALESCE except that they can substitute null values.


Other SQL Server articles and tutorials you may like

• Difference between rank(), row_number(), and dense_rank() in SQL? (answer)
• How to remove duplicate rows from a table in SQL? (solution)
• How to split String in SQL Server? (answer)
• How to join more than two tables in one SQL query? (solution)
• 5 tips while migrating from Oracle to SQL Server? (tips)
• How to find the second highest salary of an employee in SQL Server? (query)
• What is the difference between WHERE and HAVING clause in SQL Server? (answer)
• How to find duplicate records from a table? (solution)
• 5 Web sites to learn SQL online for FREE? (resource)
• How to find all customers who have never ordered? (solution)
• What is the difference between close and deallocate a cursor? (answer)
• How to create an Identity column in SQL Server? (example)
• How many characters are allowed in VARCHAR(2) columns? (answer)

Thanks for reading this article, if you like this article and are able to understand the difference between COALESCE and ISNULL function then please share it with your friends and colleagues. If you have any questions, suggestions, or feedback, please drop a comment.