Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 COALESCE Vs ISNULL Rate Topic Display Mode Topic Options
Author
 Message
 Posted Friday, November 12, 2010 4:48 AM
 Old Hand Group: General Forum Members Last Login: Monday, January 07, 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! Group: General Forum Members Last Login: Thursday, November 21, 2013 9:40 AM Points: 858, Visits: 607
 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 Group: General Forum Members Last Login: 2 days ago @ 11:21 AM Points: 3,924, Visits: 1,572
 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! Group: General Forum Members Last Login: Friday, November 08, 2013 11:31 AM Points: 951, Visits: 8,651
 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 Group: General Forum Members Last Login: Thursday, December 05, 2013 8:59 AM Points: 1,378, Visits: 451
 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 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 Group: General Forum Members Last Login: Monday, October 28, 2013 1:27 PM Points: 1,219, Visits: 1,047
 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 Group: General Forum Members Last Login: Thursday, December 05, 2013 11:35 AM Points: 85, Visits: 620
 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 writeSELECT (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
 SSCertifiable Group: General Forum Members Last Login: Today @ 1:42 PM Points: 7,938, Visits: 8,353
 Nice clean question, answer, and explanation. Thanks.But I must remember not to do QOTD at this time of day. Tom'S iomadh doigh a th’ air cu a mharbhadh gun a thachdadh le ìme
Post #1020284
 Posted Wednesday, November 24, 2010 11:43 AM
 SSCoach Group: General Forum Members Last Login: 2 days ago @ 9:08 AM Points: 19,464, Visits: 13,052
 Thanks for the question. Jason AKA CirqueDeSQLeilI have given a name to my pain...MCM SQL Server 2008SQL RNNRPosting Performance Based Questions - Gail ShawPosting Data Etiquette - Jeff ModenHidden RBAR - Jeff ModenVLFs and the Tran Log - Kimberly Tripp
Post #1026096

 Permissions