Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

COALESCE Vs ISNULL Expand / Collapse
Author
Message
Posted Friday, November 12, 2010 4:48 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, January 7, 2013 2:22 AM
Points: 376, Visits: 728
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')


Post #1019813
Posted Friday, November 12, 2010 6:47 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, June 11, 2014 9:54 AM
Points: 912, Visits: 654
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.
Post #1019886
Posted Friday, November 12, 2010 7:20 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, July 17, 2014 10:56 AM
Points: 3,924, Visits: 1,607
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.
Post #1019912
Posted Friday, November 12, 2010 7:23 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 7:09 AM
Points: 995, Visits: 8,656
Good question. I missed the note even though it is highlighted.



Steve Eckhart
Post #1019916
Posted Friday, November 12, 2010 7:26 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 6:11 AM
Points: 1,393, Visits: 480
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.
Post #1019922
Posted Friday, November 12, 2010 7:37 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 31, 2013 8:01 AM
Points: 1,232, Visits: 1,046
Nice question. I learned something new about the Coalesce function and NULL values.

IMO: The actual data type for a NULL value once inserted into a table has to do with the fact that a true NULL is a lack of anything at all.
However, SQL server has to use something to maintain that the feild should return a NULL so it uses an int.
Post #1019930
Posted Friday, November 12, 2010 8:12 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 7:15 AM
Points: 1,432, Visits: 1,059
SanDroid (11/12/2010)
...true NULL is a lack of anything at all.
However, SQL server has to use something to maintain that the feild...so it uses an int.


That's my guess, too. NULL doesn't have a default datatype but a column in a table must have a default datatype and that default is int.

Hopefully, someone can confirm this theory.
Post #1019959
Posted Friday, November 12, 2010 8:41 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, May 31, 2014 10:37 PM
Points: 85, Visits: 625
I think this difference in behavior may have something to do with the fact that the construction we're all so used to in SQL Server

SELECT (some thing or expression)

is really a short hand version of

SELECT (some thing or expression)
FROM (some table)

In Oracle and DB2, you can't write

SELECT (something)

You have to specify the FROM clause - which I believe is consistent with the language definition. Oracle gets around this by providing DUAL. DB2 shops often create their own version.

Anyway, I think that this difference in behavior is due to the fact that 1. ISNULL is old, proprietary, and non-standard, and 2. SELECT NULL without a FROM clause is really shorthand for SELECT NULL FROM SomeConvenientTable.

Personally, I always use COALESCE. It is standard SQL, and it is more flexible than ISNULL. ISNULL can do nothing that COALESCE can't do, and COALESCE can do things that ISNULL can't. So... COALESCE, always.
Post #1019993
Posted Friday, November 12, 2010 8:37 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 2:08 PM
Points: 8,844, Visits: 9,405
Nice clean question, answer, and explanation. Thanks.
But I must remember not to do QOTD at this time of day.


Tom
Post #1020284
Posted Wednesday, November 24, 2010 11:43 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:32 PM
Points: 21,755, Visits: 15,459
Thanks for the question.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1026096
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse