In this article, we will see the difference between ISNULL and COALESCE in SQL Server. Both ISNULL and COALESCE evaluates the expression for NULL and returns a non-null value.
ISNULL
The ISNULL function replaces NULL with a specified value.
ISNULL ( check_expression, replacement_value )
It contains two parameters. The first parameter is to evaluate the expression for NULL. If the first parameter is NULL, the function replaces the second parameter.
COALESCE
COALESCE evaluates the expression in order and returns the first non-null value.
COALESCE ( expression1, expression2 [, … expressionN] )
It can contain N no of parameters. It returns the first non-null parameter value.
Difference between ISNULL and COALESCE
- ISNULL function returns the data type of the first parameter whereas, COALESCE returns the data type of expression with highest data type precedence.
- When the first parameter is NULL, ISNULL function implicitly converts the second parameter value to the data type of the first parameter.
- ISNULL function truncates the second parameter value when it is longer than the first parameter. Whereas, COALESCE doesn’t truncate any values.
- If the data type of both the parameters in ISNULL function is not determined, then the data type returned is an integer. In COALESCE expression, at least one of the NULL values must be a typed NULL. If the data type of all the parameters is not determined, then it throws an error.
- The query optimizer rewrites the COALESCE expression as a CASE expression like below. Whereas, the ISNULL function remains the same.
CASE
WHEN (expression1 IS NOT NULL) THEN expression1
WHEN (expression2 IS NOT NULL) THEN expression2
ELSE expressionN
END
- ISNULL function is evaluated only once whereas the COALESCE expression is evaluated multiple times.
Also, see Pagination in SQL Server