Skip to main content
Ben Nadel at CFCamp 2023 (Freising, Germany) with: Brett Payne-Rhodes
Ben Nadel at CFCamp 2023 (Freising, Germany) with: Brett Payne-Rhodes ( @bpaynerhodes )

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

By on
Tags:

As I just blogged, COALESCE() was just pointed out to me. Its a cool function, but I was curious to see how it compared in speed to ISNULL(). It seems that COALESCE() is about 2 to 3 times slower on enormous queries:

<!--- Test the COALESCE() SQL method. --->
<cftimer label="COALESCE" type="outline">

	<cfquery name="qCoalesce" datasource="...">
		SELECT
			id,
			COALESCE( date_created, getDate() ) AS date_created
		FROM
			web_stats_hit
		WHERE
			COALESCE( date_created, getDate() ) IS NOT NULL
		AND
			COALESCE( id, 0 ) IS NOT NULL
	</cfquery>

</cftimer>

<!--- Test the ISNULL() SQL method. --->
<cftimer label="ISNULL" type="outline">

	<cfquery name="qIsNull" datasource="...">
		SELECT
			id,
			ISNULL( date_created, getDate() ) AS date_created
		FROM
			web_stats_hit
		WHERE
			ISNULL( date_created, getDate() ) IS NOT NULL
		AND
			ISNULL( id, 0 ) IS NOT NULL
	</cfquery>

</cftimer>

The ISNULL() method performed on average at about 550 ms. The COALESCE() method performed on average at about 1500 ms. The other interesting thing was that ISNULL() was very very consistent. COALESCE() on the other hand, would range from 1000 ms to 2000 ms from test to test.

I am not doing this to point out that one is better than the other; they do different things. I am just saying, consider one or the other based on the situation.

Want to use code from this post? Check out the license.

Reader Comments

3 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?

1 Comments

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. :-)

15,663 Comments

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.

1 Comments

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

15,663 Comments

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.

1 Comments

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.

1 Comments

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

1 Comments

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.

15,663 Comments

@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.

1 Comments

@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.

1 Comments

Hi,

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

I believe in love. I believe in compassion. I believe in human rights. I believe that we can afford to give more of these gifts to the world around us because it costs us nothing to be decent and kind and understanding. And, I want you to know that when you land on this site, you are accepted for who you are, no matter how you identify, what truths you live, or whatever kind of goofy shit makes you feel alive! Rock on with your bad self!
Ben Nadel