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
|