SQL : Differences Between ISNULL and COALESCE Functions

The ISNULL function replaces NULL with the specified replacement value.

ISNULL ( check_expression, replacement_value )

The COALESCE function returns the first non-NULL expression among its arguments.

COALESCE ( expression_1, expression_2 [, … expression_n] )

Here's a list of differences between the ISNULL function and the COALESCE function:
:

ISNULL	COALESCE
Takes only 2 parameters.	Takes a variable number of parameters.
A proprietary T-SQL function.	ANSI SQL standard.
Data type returned is the data type of the first parameter.	Data type returned is the expression with the highest data type precedence.  If all expressions are non-nullable, the result is typed as non-nullable.
Built-in function implemented in the database engine.	Translates to a CASE expression: COALESCE (exp_1, exp_2, … exp_n) Translates to CASE WHEN exp_1 IS NOT NULL THEN exp_1 WHEN exp_2 IS NOT NULL THEN exp_2 … ELSE exp_n END
If the data types of both parameters are not determined, the data type returned is int. ISNULL(NULL, NULL) – Returns int	At least one of the NULL values must be a typed NULL.  If the data types of all parameters are not determined, the COALESCE function will throw an error: COALESCE(NULL, NULL) – Throws an error COALESCE(CAST(NULL AS INT), NULL) – Returns int

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

Differences Between IsNull() and Coalesce() Functions

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, 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

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 length 3. So the ISNULL function returns tes and the COALESCE() function does not; depending on the length, it returns 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

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