Differences Between IsNull() and Coalesce() Functions?

ISNULL() Function:

1)ISNULL function is regraded as Transact-SQL function.
2)This function is used to replace NULL with the replacement value specified in second argument or from from the input expression it returns first expression that is not null.
3)This function put a limitation on number of arguments i.e only two arguments can be provided to this function as input.

Syntax:

ISNULL (check_exp, change_value)

COALESCE() Function:

1)COALESCE function is provided by ANSI Standard.
2)In COALESCE expression, input with the highest data type precedence is treated as datatype of the COALESCE expression.
3)This function returns the first argument with a not null value from among it's arguments given as input.
4)This function contains or supports multiple inputs.

Syntax:

COALESCE ( expression [ ,...n ] ) 

Examples to explain the difference between IsNull() and Coalesce() Functions:

Example1:

Declare 3 variables x,y,z
DECLARE @x AS INT = NULL,@y AS INT = 3999,@Z AS INT = 42;
SELECT COALESCE(@x,@y,@z) COALESCEOUTPUT ISNULL(@x,@y) ISNULLOUTPUT;

Explanation

COALESCEOUTPUT will contain 3999. Coalesce() function returns the first non-null value among its arguments.

ISNULLOUTPUT will also contain 3999. ISNULL() function is used to replace NULL with the specified replacement value.

Example2:

DECLARE @test varchar(2);
SELECT ISNULL(@test, 'ABCD') AS ISNULLOUTPUT;
SELECT COALESCE(@test, 'ABCD') AS  COALESCEOUTPUT;

Explanation

ISNULLOUTPUT will contain AB. The ISNULL() function looks at the first value and the second parameter value is automatically limited to that length.

COALESCEOUTPUT will contain ABCD. COALESCE() does not have above specified restriction.

Example3:

DECLARE @a VARCHAR(5)='ABCD', @b INT =5;
SELECT COALESCE(@a, @b) AS COALESCEOUTPUT;

Explanation

This query will result in an error:Conversion failed when converting the varchar value 'ABCD' to int datatype. This is because the data type of a COALESCE expression is the data type of the input with the highest data type precedence and Coalesce() function returns the first non-null value among its arguments so INT datatype has higher precedence than varchar so INT wil be datatype of expression and since first non-null argument is a varchar, it fails to covert it to INT datatype.