Differences Between IsNull() and Coalesce() Functions in SQL Server 2012

Introduction

In this article, I will explain the differences between the IsNull() and Coalesce() functions in SQL Server. The ISNULL and Coalesce functions are both used to replace null values with user-defined values. So let's take a look at a practical example of how these functions differ. The example is developed in SQL Server 2012 using the SQL Server Management Studio. There are some differences which are explained below.

ISNULL() Function in SQL Server

The ISNULL() function is used to replace NULL with the specified replacement value. This function contains only two arguments.

Syntax

ISNULL (check_exp, change_value)

Coalesce() Function in SQL Server

The Coalesce() function returns the first non-null value among its arguments. This function doesn't limit the number of arguments, but they must all be of the same data type.

Syntax

COALESCE ( expression [ ,...n ] )

COALESCE() function is equivalent to the following CASE expression.CASE

WHEN (exp1 IS NOT NULL) THEN exp1

WHEN (exp2 IS NOT NULL) THEN exp2

...

ELSE expN

Differences Between IsNull() and Coalesce() Functions

1. The COALESCE() function is based on the ANSI SQL standard whereas the ISNULL function is a Transact-SQL function

2. An expression involving ISNULL with non-null parameters is considered to be NOT NULL, while expressions involving COALESCE with non-null parameters is considered to be NULL.

3. The ISNULL() function contains only two parameters. The COALESCE() function contains multiple parameters. If we use more than two parameters with the ISNULL function then we must use nested ISNULL functions. 

Example 1

ISNULL() function

SELECT ISNULL(NULL, NULL, 'Hello')  

Output

COALESCE() function

SELECT COALESCE(NULL, NULL, 'hello')  

Output

4. The ISNULL() function looks at the first value and the second parameter value is automatically limited to that length but COALESCE() does not have this restriction.

Example 2

declare @test varchar(3)  
select isnull(@test, 'ABCD') AS ISNULLResult  
select coalesce(@test, 'ABCD') AS coalesceResult  

Output

In the above image, the test variable has a length of 3. So the ISNULL function returns tes and the COALESCE() function does not; depending on the length, it returns the test.

5. The ISNULL() function contains various types of parameters. The COALESCE() function doesn't limit the number of arguments, but they must all be of the same data type.

Example 3

ISNULL() function

DECLARE @a VARCHAR(5)='Hello',  
@b INT =5  
SELECT ISNULL(@a, @b) AS ISNULLResult  

Output

The COALESCE() function

DECLARE @a VARCHAR(5)='Hello',  
@b INT =5  
SELECT COALESCE(@a, @b) AS COALESCEResult  

Output

Conclusion

In this article, I will explain the differences between the IsNull() and Coalesce() functions in SQL Server.