Difference between ISNULL and COALESCE in SQL

By | 21st August 2019

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 and COALESCE returns non-null value
  • ISNULL function truncates the second parameter value when it is longer than the first parameter. Whereas, COALESCE doesn’t truncate any values.
ISNULL returns the data type of the first parameter
  • If the data type of both the parameters in ISNULL function are not determined, then the data type returned is integer. In COALESCE expression, at least one of the NULL values must be a typed NULL. If the data type of all the parameters are not determined, then it throws error.
  • The query optimizer rewrites the COALESCE expression as a CASE expression like below. Whereas, the ISNULL function remains 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 evaluated multiple times.

Also, see Pagination in SQL Server

Leave a Reply

Your email address will not be published. Required fields are marked *