COALESCE Vs ISNULL

Question

Post reply

COALESCE Vs ISNULL

Ujar

Say Hey Kid

Points: 669
More actions
November 11, 2010 at 8:42 pm

#231239

Comments posted to this topic are about the item COALESCE Vs ISNULL

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply

UMG Developer SSChampion Points: 13482 More actions · Answer 1

UMG Developer

SSChampion

Points: 13482

November 11, 2010 at 8:42 pm

#1249799

Nice question, thanks!

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 2

Hardy21

SSCrazy Eights

Points: 9708

November 11, 2010 at 9:28 pm

#1249810

Good one point question. Thanks

Thanks

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 3

As per the BOL:

If all arguments are NULL, COALESCE returns NULL with Note: At least one of the null values must be a typed NULL.

Then why "SELECT COALESCE(NULL,NULL) AS [COALESCE]" is failed?

Thanks

Mike John SSCertifiable Points: 7216 More actions · Answer 4

Because in this case ALL the arguments are "untyped" nulls.

so

declare @a int

select coalesce(@a,null)

will retunr NULL (and not an error).

Mike

sharath.chalamgari SSCertifiable Points: 5680 More actions · Answer 5

sharath.chalamgari

SSCertifiable

Points: 5680

November 12, 2010 at 12:55 am

#1249860

simple one today

philip.cullingworth SSCrazy Points: 2150 More actions · Answer 6

An interesting question, but am I missing something here? When would you use coalesce entirely with untyped values?

If I were using coalesce it would be to test column values or variables/parameters and in both these cases typing is specified. Can anyone think of a situation where you would be sending only untyped values?

vk-kirov SSCertifiable Points: 7686 More actions · Answer 7

I found it interesting that the expression "ISNULL(NULL, NULL)" is of type INT.

SELECT ISNULL(NULL, NULL) AS A

INTO QOTD_TABLE;

EXEC sp_help 'QOTD_TABLE';

-- Column_name Type

-- ------------- ------

-- A int

Is there any reasonable explanation on this behavior?

paul.knibbs SSCoach Points: 15320 More actions · Answer 8

Interesting point there, vk-kirov. I guess that means that the default type for NULL is int--but in that case, there's not really any such thing as an "untyped NULL" and the COALESCE thing becomes a bit strange!

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 9

Nice question.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

paul.jones SSC Eights! Points: 854 More actions · Answer 10

OK, I'm happy with the explanation of why COALESCE fails, but why does ISNULL work? vk-kirov observed that ISNULL(NULL,NULL) appears to be a NULL of type INT, but it doesn't always have to be, as the following are not equivalent:

SELECT ISNULL( ISNULL(NULL,NULL) ,'A')

SELECT ISNULL( CAST(NULL AS INT) ,'A')

dave.farmer SSCrazy Points: 2431 More actions · Answer 11

paul.jones (11/12/2010)
OK, I'm happy with the explanation of why COALESCE fails, but why does ISNULL work? vk-kirov observed that ISNULL(NULL,NULL) appears to be a NULL of type INT, but it doesn't always have to be, as the following are not equivalent:
SELECT ISNULL( ISNULL(NULL,NULL) ,'A')
SELECT ISNULL( CAST(NULL AS INT) ,'A')

Presumably because ISNULL says choose the first unless it is null, in which case choose the second, (regardless of whether it is null) whereas COALESCE is choosing the first non-null value in the list (not sure how typing your null helps with that actually?)

In your first example, the only typed value is the 'A', so that type is applied to the rest of the values. In the second example it encounters the INT type first and tries to cast everything else to that.

SanjayAttray SSChampion Points: 13157 More actions · Answer 12

More explanation on COALESCE expression error..

http://connect.microsoft.com/SQLServer/feedback/details/301695/misleading-error-message-on-case-expression-with-untyped-null-in-each-result-expression#

SQL DBA.

Steve Eckhart SSCommitted Points: 1880 More actions · Answer 13

Good question. I missed the note even though it is highlighted. :blush:

Steve Eckhart

Mike Is Here Hall of Fame Points: 3348 More actions · Answer 14

ISNULL and COALESCE though equivalent, can behave differently. An expression involving ISNULL with non-null parameters is considered to be NOT NULL, while expressions involving COALESCE with non-null parameters is considered to be NULL.