Monday, July 27, 2015

Differences Between ISNULL() and COALESCE()

ISNULL() Function
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
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

1.  The COALESCE() function is based on the ANSI SQL standard whereas 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
ISNULL() function:
SELECT ISNULL(NULL, ISNULL(NULL, 'Hello'))

COALESCE() function:
SELECT COALESCE(NULL, NULL, 'Hello')

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
declare @test varchar(3)
select isnull(@test, 'ABCD') AS ISNULLResult
select coalesce(@test, 'ABCD') AS coalesceResult

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
ISNULL() function:
DECLARE @a VARCHAR(5)='Hello',
@b INT =5
SELECT ISNULL(@a, @b) AS ISNULLResult