Difference Between COALESCE() AND ISNULL()

In SQL many times we have null values and we wants return another value, for that purpose in SQL there is two methods:
1. COALESCE()
2. ISNULL()
COALESCE() : It is ANSI standard function, and takes 2 or more number of parameters and each value can be of different datatype. It returns the first non-null parameter value in a list of parameter values. In COALESCE () all parameter can’t be null if all parameter are null then we need to cast the value.
For Example:

SELECT COALESCE (NULL, ‘Vijendra’)
----OUTPUT----
Vijendra
SELECT COALESCE (NULL, ‘Vijendra’, NULL, 1983)
----OUTPUT----
Vijendra

SELECT COALESCE (NULL, NULL)
when we execute this expression we got following type of error:
Msg 4127, Level 16, State 1, Line 1
At least one of the arguments to COALESCE must be a typed NULL.
In this case we need to cast atleast one parameter like:
SELECT COALESCE (NULL, CAST (NULL AS VARCHAR (10)))
OR
SELECT COALESCE(NULL,CAST(NULL AS INT),NULL)
In both the cases output will be null.
----OUTPUT----
NULL

COALESCE correctly promotes its parameter to the highest data type in the parameter list.

SELECT 3/COALESCE(CAST(NULL AS INT),2.00)
----OUTPUT----
1.500000

ISNULL (): It is not ANSI standard function but it is a TSQL function, and takes 2 parameters and it uses as the first parameter datatype.ISNULL will returns second parameter value if the first parameter value is NULL.
For Example:

SELECT ISNULL(NULL,NULL)
----OUTPUT----
NULL
SELECT ISNULL(NULL,'Vijendra')
----OUTPUT----
Vijendra

If we want compare three parameter values with ISNULL then we use nested ISNULL ().

SELECT ISNULL (ISNULL (NULL, NULL),’Vijendra’)
----OUTPUT----
Vijendra

ISNULL uses as the first parameter datatype.

SELECT 3/ISNULL(CAST(NULL AS INT),2.00)
----OUTPUT----
1