Difference between ISNULL() and COALESCE() function in SQL? Example

Even though both ISNULL() and COALESCE() function provides alternate values to NULL in T-SQL and Microsoft SQL Server e.g. replacing NULL values with empty String, there are some key differences between them, which is often the topic of SQL Server interview. In this article, you will not only learn the answer to this question but also learn how to use COALESCE and ISNULL function properly. One of the main differences between them is that  COALESCE() is a standard SQL function but ISNULL() is Microsoft SQL Server-specific, which means it's not guaranteed to be supported by other database vendors like Oracle, MySQL, or PostgreSQL.

But, perhaps the most important difference between them is that COALESCE is more flexible and powerful than ISNULL().

With ISNULL(), you can only provide one alternate value but with COALESCE you can provide more than one e.g. if col1 IS NULL then take value from column2, if that is NULL then take the default value.

Btw, that's not the only difference, there are three key differences between ISNULL() and COALESCE() which we will explore in this article. Btw, if you are new to SQL Server, then I first suggest you go through a good introductory Microsoft SQL Courses for Beginners. That will help you to better prepare for the interview as well instead of just going through random articles.

Difference between ISNULL() vs COALESCE() in SQL Server

There are three major differences between these two functions besides being ANSI standard or not:

1. COALESCE correctly promotes its arguments to the highest data type in the expression list, but ISNULL doesn't.

2. COALESCE is more flexible and allows you to provide multiple columns and default values but ISNULL can only work with two values.

3. In the case of ISNULL, the alternate value takes the length of the first value but this doesn't happen in the case of COALESCE. This means the type of COALESCE expression is determined by the returned element whereas the return type of ISNULL is determined by the first input.

4. When you use them in a SELECT INTO query then both will produce a NON-NULL value in the result table if the attribute has NON NULL constraint but if it doesn't then COALESCE will create an attribute that allows NULL and ISNULL will create which doesn't allow NULLs


Now let's understand each point in little more detail.

1.  COALESCE promotes its argument to the higher data type.

As I have said before that, COALESCE correctly promotes its arguments to the highest data type in the expression list, while ISNULL just looks at the data type of the first argument and makes everything of that type. Let's see an SQL query to understand this point:

SELECT 19 / ISNULL(CONVERT(INT,NULL), 3.00);
Output
6

SELECT 19 / COALESCE(CONVERT(INT,NULL), 3.00)
Output
6.333333

In the first SQL query, we have used ISNULL and the first data type is INT but because of its NULL, it also converts 3.00 to INT and performed integer arithmetic, but COALESCE correctly promotes the 19 to FLOAT and performed floating-point arithmetic. See Microsoft SQL for Beginners to learn more about floating-point arithmetic and data types in SQL Server.

2. COALESCE allows multiple values but ISNULL allows only one value

You can provide COALESCE multiple values to use in case the target is NULL. For example, in the following query, we have provided four options to COALESCE

DECLARE @x VARCHAR(10)
DECLARE @y VARCHAR(10)
DECLARE @z VARCHAR(10)
DECLARE @a VARCHAR(10)
SELECT @a = 'SQL'

--This will return SQL
SELECT COALESCE(@x,@y,@z,@a)

Output
SQL

With ISNULL, you can only provide two values e.g.

SELECT ISNULL(@x,@y); --NULL
SELECT ISNULL(@x,@a); --SQL

This flexibility allows you to replace complex case statements with simple coalesce functions called on SQL Server stored procedures and functions.  See these free SQL and database courses to learn more about it.

3. Length of Result

In the case of COALESCE data type of the result, value determines the type of COALESCE expression but in the case of ISNULL, it's the type of the first argument. For example, see the following T-SQL Query:

DECLARE

@a AS VARCHAR(4) = NULL,
@b AS VARCHAR(10) = '1234567890';

SELECT COALESCE(@a, @b) AS [COALESCE], ISNULL(@a, @b) AS [ISNULL];

Output
COALESCE ISNULL
1234567890 1234

You can see that in the case of COALESCE() the result has type and length VARCHAR(10) but in the case of ISNULL() is the length of the first value i.e. length is 4 characters. Another worth noting thing is the use of square bracket e.g. [ISNULL], we do this when we use any keyword or function as literal i.e. variable name or column name.  If you want to learn more, I suggest you read Microsoft SQL Server 2012 T-SQL Fundamentals, one of the best books to understand SQL Server.

4. The behavior of COALESCE and ISNULL when used in SELECT INTO

One more difference between COALESCE and ISNULL comes when you are using them in the SELECT INTO clause. If you don't know you can create a table by copying data and schema from another table by using the SELECT INTO clause.

If you are using something like:
  COALESCE(column1, 0) as new_column
vs
ISNULL(column1, 0) as new_column.

Then, both expressions will produce a NOT NULL attribute in result table if the source is defined as NOT NULL, but in case source attribute allows NULLs then COALESCE will create an attribute that allows NULL and ISNULL will create which doesn't allow NULLs.


Btw, if you are preparing for Microsoft SQL Server certification like Microsoft SQL Server 2012/2014 Certification Training Exam 70-461 then also this concept will help you a lot. There is a sure-shot chance that you will get at least one question from this concept.

And if you really want to do well then you can also take a look at this list of free 70-461: Querying Microsoft SQL Server Practice questions and exam dumps from David Mayer. 

Finally, here is a nice summary of the differences between COALESCE and ISNULL functions in Microsoft SQL Server.

That's all about the difference between ISNULL and COALESCE in SQL Server. Generally, it's recommended to stick to standard features unless there is some flexibility or major performance you get by using a non-standard feature.

Since ISNULL is actually more limited than COALESCE, so there is no reason to use ISNULL over COALESCE, unless you find ISNULL more readable than COALESCE, like many beginners.

Btw, you must remember these key differences between ISNULL and COALESCE if you are refactoring code and replacing ISNULL with COALESCE in your SQL Script.


Other SQL Server articles and Interview Questions you may like to explore

• 5 Free Oracle and SQL Server courses for Programmers (courses)
• Difference between rank(), row_number(), and dense_rank() in SQL? (answer)
• Difference between Cast, Convert, and Parse in SQL Server? (answer)
• The right way to compare dates in SQL query? (example)
• How to remove duplicate rows from a table in SQL? (solution)
• How to split String in SQL Server 2008? (answer)
• 5 Websites to learn SQL online for FREE? (resource)
• How to find the length of a String in SQL Server? (example)
• How to find all customers who have never ordered? (solution)
• How to convert the result of a SELECT command into a CSV String? (example)
• What is the difference between close and deallocate a cursor? (answer)
• How to create an Identity column in SQL Server? (example)
• How to add columns to an existing table in MSSQL? (example)

Thanks for reading this article, if you like the information is given here then please share it with my friends and colleagues.