Difference between ISNULL and COALESCE in SQL Server

Difference between ISNULL and COALESCE in SQL Server

ISNULL : It’s used to replace NULL value to specific value, it’s a sql server function.

                    Syntax:     IsNULL(<Expression to test>,<Replacement Value>),

Example:

IsNull(null, 15)  returns 15

IsNull(5,15)         returns 5 because no null value is found. 

COALESCE :It’s evaluates the arguments in order and returns the current value of the first expression that initially does not evaluate to NULL.

COALESCE expression is a syntactic shortcut for the CASE expression

when the code COALESCE((sub-query), 1) is executed, the sub-query is evaluated twice. As a result, you can get different results depending on the isolation level of the query.

Comparing COALESCE and ISNULL:

Both can be used for similar purpose but behave differently.

  • Because ISNULL is a function, it is evaluated only once. As described above, the input values for the COALESCE expression can be evaluated multiple times.
  • Data type determination of the resulting expression is different. ISNULL uses the data type of the first parameter, COALESCE follows the CASE expression rules and returns the data type of value with the highest precedence.
  • Validations for ISNULL and COALESCE are also different. For example, a NULL value for ISNULL is converted to int whereas for COALESCE, you must provide a data type.
  • IsNull only takes two parameters while COALESCE have variable parameter based on expression.
  • The NULLability of the result expression is different for ISNULL and COALESCE.