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.

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'

Forget about this SQL stuff... tell me more about these Ads by Goooooogle.

Something even more interesting (and disturbing) in SQL:

What is NULL + 0 (zero)?

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

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.

Ha! Yes, that IS disturbing.

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

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

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.

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

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.

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.

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

Neat tip!

http://www.codershangout.com

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?

Isnull(@test,'test')
replaces all the null values with 'test'
whereas Coalesce returns first non null record

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

hey Guys Why the Code is not Working..

A very Good Stuff. Thanks for sharing this wonderful knowledge.

Exactly what I was looking for...thanks

its a nice article..... got to know a lot .... :)

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

nice work thanks

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
*