Skip Navigation Links
Home
Functions
Tips & Tricks
SQL Server 2005
SQL Server 2008
SQL Server 2012
FAQ
Forums
Practice Test
Bookstore
Tip of the Day : Differences Between ISNULL and COALESCE Functions
SQL Server Helper
Home > > Tip of the Day
SQL Server Helper - Tip of the Day

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

Back to Tip of the Day List Next Tip