SQL QUIZ: The Difference Between ISNULL and COALESCE

Jan 21, 2005 sql suggest edit

What will the last two lines print. Will they be the same?

DECLARE @test VARCHAR(2)
DECLARE @first VARCHAR(4)
DECLARE @second< VARCHAR(4)

SELECT @first = ISNULL(@test, 'test')
SELECT @second = COALESCE(@test, 'test')

PRINT @first
PRINT @second

What do you think?

Found a typo or mistake in the post? suggest edit

Comments

28 responses

jayson knight • January 21st, 2005
Very interesting. Personally I'd never heard of the coalesce keyword until reading this post (had to run to BOL to check it out). isnull truncates the replacement value to the size of the expression being checked whereas coalesce does not? Interesting behavior, and not at all what I expected. Good post.
Hassan Voyeau • January 21st, 2005
Same, here ... I had to test it myself. ISNULL doesn't truncate the value to the size being checked, that is just a side effect of isnull returning using the same type as the first parameter. Had we used DECLARE @test VARCHAR(4) then the result would have been 'test' and 'test'
Koba • January 21st, 2005
Forget about this SQL stuff... tell me more about these Ads by Goooooogle.
Julian • January 24th, 2005
Something even more interesting (and disturbing) in SQL:

What is NULL + 0 (zero)?
Haacked • January 24th, 2005
It's NULL. I don't find that disturbing though. NULL is like a black hole. Append anything to NULL and the result is NULL.

Besides 0 + something always equals something. ;)
Julian • January 24th, 2005
Ok, maybe it's not disturbing. But imagine inheriting a very large project that the person who originally wrote the code, expected NULL + 0 = 0. Nice one.
Haacked • January 24th, 2005
Ha! Yes, that IS disturbing.
Sheetal • March 9th, 2005
the difference between isnull and coalesce is that

coalesce COALESCE(@test, 'test')

can be interpreted as

case

when @test not null then @test

else

'test'

end
kuantanzai • April 28th, 2005
Actually, Sheetal's interpretation of COALESCE IS the interpretation of ISNULL. The interpretation of COALESCE is really:

if @test is not null then @test

else if 'test' is not null then 'test'

Coalesce makes an additional nullity check on 'test'.
Bobby • May 17th, 2005
The difference between Coalesce and Isnull is that Coalesce can take multiple values (2 or more).... it'll check each value until it finds one that is not null... once it does, it'll return that value...

IsNull, on the other hand, has 2 arguments and will only check the first value to see if it's null, if it is null, it'll return the 2nd arguement.
Sridhara K • May 29th, 2006
Should I use COALESCE() or ISNULL()?

As with many technology questions involving roughly equivalent choices, it depends. There are a variety of minor differences between COALESCE() and ISNULL():
COALESCE() is ANSI standard, so that is an advantage for the purists out there.

Many consider ISNULL()'s readability and common sense naming to be an advantage. While I will agree that it easier to spell and pronounce, I disagree that its naming is intuitive. In other languages such as VB/VBA/VBScript, ISNULL() accepts a single input and returns a single boolean output.

ISNULL() accepts exactly two parameters. If you want to take the first non-NULL among more than two values, you will need to nest your ISNULL() statements. COALESCE(), on the other hand, can take multiple inputs:

SELECT ISNULL(NULL, NULL, 'foo')

-- yields:
Server: Msg 174, Level 15, State 1, Line 1
The isnull function requires 2 arguments.

SELECT COALESCE(NULL, NULL, 'foo')

-- yields:
----
foo

In order to make this work with ISNULL(), you would have to say:

SELECT ISNULL(NULL, ISNULL(NULL, 'foo'))

The result of ISNULL() always takes on the datatype of the first parameter (regardless of whether it is NULL or NOT NULL). COALESCE works more like a CASE expression, which returns a single datatype depending on precendence and accommodating all possible outcomes. For example:

DECLARE @foo VARCHAR(5)
SET @foo = NULL
SELECT ISNULL(@foo, '123456789')

-- yields:
-----
12345

SELECT COALESCE(@foo, '123456789')

-- yields:
---------
123456789

This gets more complicated if you start mixing incompatible datatypes, e.g.:

DECLARE @foo VARCHAR(5), @bar INT
SET @foo = 'foo'
SET @bar = NULL

SELECT ISNULL(@foo, @bar)
SELECT COALESCE(@foo, @bar)

-- yields:

-----
foo

Server: Msg 245, Level 16, State 1, Line 6
Syntax error converting the varchar value 'foo' to a column of data type int.

A relatively scarce difference is the ability to apply constraints to computed columns that use COALESCE() or ISNULL(). SQL Server views a column created by COALESCE() as nullable, whereas one using ISNULL() is not. So:

CREATE TABLE dbo.Try
(
col1 INT,
col2 AS COALESCE(col1, 0)
PRIMARY KEY
)
GO

-- yields:
Server: Msg 8111, Level 16, State 2, Line 1
Cannot define PRIMARY KEY constraint on nullable column in table 'Try'.
Server: Msg 1750, Level 16, State 1, Line 1
Could not create constraint. See previous errors.

Whereas the following works successfully:

CREATE TABLE dbo.Try
(
col1 INT,
col2 AS ISNULL(col1, 0)
PRIMARY KEY
)
GO

If you are using COALESCE() and or ISNULL() as a method of allowing optional parameters into your WHERE clause, please see Article #2348 for some useful information (the most common techniques will use a scan, but the article shows methods that will force a more efficient seek).

Finally, COALESCE() can generate a less efficient plan in some cases, for example when it is used against a subquery. Take the following example in Pubs and compare the execution plans:

USE PUBS
GO

SET SHOWPLAN_TEXT ON
GO

SELECT COALESCE
(
(SELECT a2.au_id
FROM pubs..authors a2
WHERE a2.au_id = a1.au_id),
''
)
FROM authors a1

SELECT ISNULL
(
(SELECT a2.au_id
FROM pubs..authors a2
WHERE a2.au_id = a1.au_id),
''
)
FROM authors a1
GO

SET SHOWPLAN_TEXT OFF
GO

Notice the extra work that COALESCE() has to do? This may not be a big deal against this tiny table in Pubs, but in a bigger environment this can bring servers to their knees. And no, this hasn't been made any more efficient in SQL Server 2005, you can reproduce the same kind of plan difference in AdventureWorks:

USE AdventureWorks
GO

SET SHOWPLAN_TEXT ON
GO

SELECT COALESCE
(
(SELECT MAX(Name)
FROM Sales.Store s2
WHERE s2.name = s1.name),
''
)
FROM Sales.Store s1

SELECT ISNULL
(
(SELECT MAX(Name)
FROM Sales.Store s2
WHERE s2.name = s1.name),
''
)
FROM Sales.Store s1

GO
SET SHOWPLAN_TEXT OFF
GO
Mark L • July 26th, 2006
the difference is this:
ISNULL is a non standard Microsoft VB derived statement, where COALESCE is an ANSI standard function.
Use ISNULL and your code is likely useless on non MS platforms. Any developer with foresight skips vendor specific additions and sticks to industry standards.
Dolan • September 8th, 2006
I am trying to create a formula for a table that will concatenate the first , middle, and last name columns and append the suffix if not null with the comma.
(coalesce([first_name],'') + ' ' + coalesce([middle_name],'') + ' ' + coalesce([last_name],'') + coalesce([suffix],''))
The problem is getting the comma only when the suffix is not null.
Simon Smith • December 13th, 2006
>It's NULL. I don't find that disturbing
>though. NULL is like a black hole. Append
>anything to NULL and the result is NULL.
>Besides 0 + something always equals something. ;)
what about NULL / NULL ?
cbmeeks • December 18th, 2006
Neat tip!
http://www.codershangout.com
Aparichit • January 16th, 2007
To bobby,
The difference between Coalesce and Isnull is that Coalesce can take multiple values (2 or more).... it'll check each value until it finds one that is not null... once it does, it'll return that value...
IsNull, on the other hand, has 2 arguments and will only check the first value to see if it's null, if it is null, it'll return the 2nd arguement.

As you mentioned that Coalesce can take multiple values (2 or more).... it'll check each value until it finds one that is not null... once it does, it'll return that value...
My question is that, if we dont have any not null value in the multiple values in coalesce then what will be value returned?
Sagar Chavan • April 24th, 2007
Isnull(@test,'test')
replaces all the null values with 'test'
whereas Coalesce returns first non null record
chis • June 12th, 2007
what will happen if this is the case:
TABLE-1
BRANCH CHAR(5) - NOT NULL WITH DEFAULT
AMOUNT DECIMAL(15,2) - NOT NULL WITH DEFAULT
SELECT BRANCH, COALESCE (AMOUNT, 0)
FROM TABLE-1
What will be the output? when it is not null, it will show the value of the amount, right. But let us assume that the amount is null, will the value be 0 (zero)?
Andrew • June 27th, 2007
hey Guys Why the Code is not Working..
jasjeet • August 1st, 2007
A very Good Stuff. Thanks for sharing this wonderful knowledge.
sivsam • September 3rd, 2007
Exactly what I was looking for...thanks
Divya • December 13th, 2007
its a nice article..... got to know a lot .... :)
Gelder • February 13th, 2008
It is correct to say that coalesce will take a multiple value test for nullability for example say that you have a three column in a table and there will only ever be one value in any of the three that is when it will become useful for examle
Table
Col1 – Col2 – Col3
Null – Null – 10
9 – Null – Null
and you ran the query
Select coalesce(col1,col2,col3) as price from table
Then it would just display the column with values entered, where as isnull has a limit of just checking one column
paulo • April 8th, 2008
nice work thanks
ana • June 19th, 2008
DECLARE @test VARCHAR(2)
DECLARE @first VARCHAR(4)
DECLARE @second VARCHAR(4)
SELECT @first = ISNULL(@test, 'test')
SELECT @second = COALESCE(@test, 98988)
PRINT @first
PRINT @second
it displays
te
*
Gangz • March 2nd, 2009
Good article and informative, Gave a great idea about the keywords
COALESCE and ISNULL.
Piyush Bajaj • July 6th, 2011
The answer of your question :
The results will be same.. :)
Kevin • May 21st, 2014
Not sure of the specifics, but the variables are of type varchar, whereas your 98988 value is an integer - so you are checking varchars but trying to return in integer in your coalesce statement.
If you instead enclose 98988 in single quotes ('98988') making it a string value, you results would be:
test
9898