SQL COALESCE() Very Cool, But Slower Than ISNULL()

Posted August 4, 2006 at 5:25 PM by Ben Nadel

Tags: SQL

Reader Comments

So, what do you recommend? I am use isNull() a lot, especially when working with date. But not on enourmous queries. Do you think I should switch to coalesce?

COALESCE is actually very useful in certain situations. For example, if you had a table with two quantity columns and you wanted to sum the difference between them, you would do,

select sum(coalesce(quanitty1, 0) - coalesce(quantity2, 0)) as difference

If you didn't use coalesce, then the sum will result in a NULL if any quantity1 or quantity2 field is NULL.

I know this comment is a little late, but I found this blog doing a google search and thought I might add to the discussion. :-)

Leon,

I appreciate what you are saying, however I think the same thing could be done with ISNULL as well:

select sum(ISNULL(quanitty1, 0) - ISNULL(quantity2, 0)) as difference

I am not argueing one way or the other. From what people have said, COALESCE is more of a standard across database management systems.

Coalesce is much more useful for something like this

DECLARE @CodeList varchar(1000)

SELECT @CodeList = COALESCE(@CodeList +', ', '') +
CAST(EmpCode AS varchar(20))
FROM Employees

SELECT @CodeList

Where EmpCode is a column in the Employees table, this will return a comma-delimited list of the employees' codes

Dave,

Nice tip. I have never really worked on building a string using a SELECT statement, but I have seen it done and it's probably something I should try.

I would say isnull() is 'as' standard as coalesce(). Oracle and Sybase use it just like MS..

They are the same 'unless' you're testing multiple values. That's the ONLY reason you should use coalesce() ...unless of course its for code consistency.

Actually, it can be done by ISNULL as well.

DECLARE @CodeList varchar(1000)

SELECT @CodeList = ISNULL(@CodeList +', ', '') +
CAST(EmpCode AS varchar(20))
FROM Employees

SELECT @CodeList

COALESCE has 2 major advantages:
1) it is part of the ANSI 2000 standard which can
make your sql more portable. This, however, is pointless to most people, as they usually work in exactly one environment.
2) you are not limited to two values.

ISNULL, logically speaking, should perform better in most cases, although there are some occasions where that does not seem to be the case.

@Jeremy,

I switched to MySQL a few years ago and have since starting using COALESCE() with success. MySQL does have IsNull() functions, but they don't act the same way - they are simply null-value-checkers.

@Ben: Right, MySQL's ISNULL() behaves diffently to MSSQL. But shouldn't ISNULL() return whether a value is NULL or not?
For the desired result MySQL provides IFNULL(), which behaves like ISNULL() in MSSQL and has the 'correct' function naming.

Hi,

None of code of David or Wayos S. working in Sybase ASE. It always return last value from the list. Any ideas?

Post A Comment

Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.

Please review the following issues:

Author Name:
Author Email:
Author Website:
Comment:	Supported HTML tags for formatting: <strong>bold</strong>   <em>italic</em>   <code>code</code>
	Remember my information
	Subscribe to comments
	Send me a copy of this comment