Search Wiki:

ISNULL() and COALESCE()

Aaron Alton, April 15, 2008

In dealing with NULL values in SQL Server, the developer is often faced with the requirement to evaluate the content of a field, and when said field in null, return another value (or field). There are two functions in SQL Server which support this requirement: ISNULL() and COALESCE.()

ISNULL
COALESCE
ISNULL vs. COALESCE
Performance Considerations
Further Reading


ISNULL()
ISNULL is a TSQL Function which is built into SQL Server. It is NOT a function defined by ANSI-92 - rather it is a feature which Microsoft has elected to include in TSQL in addition to the ANSI SQL standard.

ISNULL() accepts two parameters. The first is evaluated, and if the value is null, the second value is returned (regardless of whether or not it is null). The following queries will return the second parameter in both cases:

SELECT ISNULL(NULL, 1)
--Returns 1
SELECT ISNULL(NULL, NULL)
--Returns NULL


COALESCE()
COALESCE() is a TSQL function which, like ISNULL, is built into SQL Server. Unlike ISNULL, COALESCE is also a part of the ANSI-92 SQL Standard. Coalesce returns the first non-null expression in a list of expressions. The list can contain two or more items, and each item can be of a different data type. The following are valid examples of COALESCE:

SELECT COALESCE(NULL, 1)
--Returns 1
 
SELECT COALESCE(NULL, 3, NULL, 1)
--Returns 3


ISNULL vs. COALESCE
Whenever multiple methods exist for addressing a single problem, the inevitable question is: which method is better? There are a few differences between the two functions which make COALESCE come out on top more often than not:
- COALESCE is ANSI-92 compliant. In the event that you need to port your code to another RDBMS, COALESCE will not require rework.
- COALESCE accepts greater than two expressions, whereas ISNULL accepts only two. In order to compare three expressions with ISNULL, you would have to nest expressions:
SELECT ISNULL(ISNULL(Col1, Col2), Col3)
- ISNULL constrains the result of a comparison of parameterrs to the datatype of the first value. For example, the following query will produce some often undesirable results using ISNULL, however it will behave as expected with COALESCE:
DECLARE @Field1 char(3), @Field2 char(50)
SET @Field2 = 'Some Long String'
 
SELECT ISNULL(@Field1, @Field2)
--Returns 'Som'
SELECT COALESCE(@Field1, @Field2)
--Returns 'Some Long String'
Note: In other situations, COALESCE will produce unexpected results. COALESCE by nature promotes it's arguments to the highest datatype among compatable arguments (arguments which are not explicitly case, and which aren't compatable, will of course throw an error). When using COALESCE on an integer and a datetime, in that order, COALESCE will cast the integer as a datetime. For example:
SELECT COALESCE(5, GETDATE())
Will not return 5, it will return 1900-01-06 00:00:00.000 (5 as a datetime).


Performance
For most purposes, ISNULL and COALESCE perform in an almost identical fashion. It is generally accepted that ISNULL is slightly quicker than COALESCE, but not sufficiently to outweigh it's inherent limitations. As with any performance related issue, if performance is a significant concern, write it both ways, and test!



For Further Reading
ISNULL (MSDN)
COALESCE (MSDN)
ISNULL vs. COALESCE - Performance Considerations
Last edited Apr 16 2008 at 6:25 AM  by AaronAlton, version 11
Comments
bwperrin wrote  May 30 2009 at 8:44 PM  
I've found what must be an isnull bug. Nothing to do with types as far as I can tell - isnull is just plain wrong.

Try this:
select t.id, val, bug = isnull(val, 0)
from (select id=1 union select 2 union select 3) t
left join (select id = 2, val = 1) u on u.id = t.id

All three "bug" values are 1! Replace "isnull" with "coalesce" and all is well. What gives?

bwperrin wrote  May 30 2009 at 8:49 PM  
I should have mentioned this was SQL 2000 SP 3a or 4. On testing in SQL 2005, this appears to have been fixed.

Updating...
Page view tracker