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.


Example
SELECT COALESCE(NULL, NULL) --error

SELECT ISNULL(NULL, NULL) --correct

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
SELECT COALESCE(NULL, NULL, 'Hello') --correct

SELECT ISNULL(NULL, NULL, 'Hello') --error

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 ---output ABC

select coalesce(@test, 'ABCD') AS coalesceResult ---output ABCD

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


Example
DECLARE @a VARCHAR(5)='Hello', @b INT =5

SELECT ISNULL(@a, @b) AS ISNULLResult-- ok

SELECT COALESCE(@a, @b) AS COALESCEResult-- wrong Conversion failed when converting the varchar value 'Hello' to data type int.