SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Ujar / COALESCE Vs ISNULL / Latest Posts

RE: COALESCE Vs ISNULL

SQLRNNR — Wed, 24 Nov 2010 11:43:00 GMT

Thanks for the question.

RE: COALESCE Vs ISNULL

L' Eomot Inversé — Fri, 12 Nov 2010 20:37:33 GMT

Nice clean question, answer, and explanation. Thanks.But I must remember not to do QOTD at this time of day.

RE: COALESCE Vs ISNULL

Dean Cochrane — Fri, 12 Nov 2010 08:41:01 GMT

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.

RE: COALESCE Vs ISNULL

Rose Bud — Fri, 12 Nov 2010 08:12:12 GMT

[quote][b]SanDroid (11/12/2010)[/b][hr]...true NULL is a lack of [i]anything at all[/i]. However, SQL server has to use something to maintain that the feild...so it uses an int.[/quote]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.

RE: COALESCE Vs ISNULL

SanDroid — Fri, 12 Nov 2010 07:37:19 GMT

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 [i]anything at all[/i]. However, SQL server has to use something to maintain that the feild should return a [i]NULL[/i] so it uses an int.

RE: COALESCE Vs ISNULL

Mike Is Here — Fri, 12 Nov 2010 07:26:28 GMT

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.

RE: COALESCE Vs ISNULL

Steve Eckhart — Fri, 12 Nov 2010 07:23:45 GMT

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

RE: COALESCE Vs ISNULL

SanjayAttray — Fri, 12 Nov 2010 07:20:01 GMT

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#

RE: COALESCE Vs ISNULL

dave.farmer — Fri, 12 Nov 2010 06:47:19 GMT

[quote][b]paul.jones (11/12/2010)[/b][hr]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:[code="sql"]SELECT ISNULL( ISNULL(NULL,NULL) ,'A')SELECT ISNULL( CAST(NULL AS INT) ,'A')[/code][/quote]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.

RE: COALESCE Vs ISNULL

paul.jones — Fri, 12 Nov 2010 04:48:07 GMT

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:[code="sql"]SELECT ISNULL( ISNULL(NULL,NULL) ,'A')SELECT ISNULL( CAST(NULL AS INT) ,'A')[/code]

RE: COALESCE Vs ISNULL

Koen Verbeeck — Fri, 12 Nov 2010 02:59:23 GMT

Nice question.

RE: COALESCE Vs ISNULL

paul.knibbs — Fri, 12 Nov 2010 02:54:56 GMT

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!

RE: COALESCE Vs ISNULL

vk-kirov — Fri, 12 Nov 2010 02:15:27 GMT

I found it interesting that the expression "ISNULL(NULL, NULL)" is of type INT.[code="sql"]SELECT ISNULL(NULL, NULL) AS AINTO QOTD_TABLE;EXEC sp_help 'QOTD_TABLE';-- Column_name Type -- ------------- ------ -- A int [/code]Is there any reasonable explanation on this behavior?

RE: COALESCE Vs ISNULL

philip.cullingworth — Fri, 12 Nov 2010 01:30:41 GMT

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?

RE: COALESCE Vs ISNULL

sharath.chalamgari — Fri, 12 Nov 2010 00:55:13 GMT

simple one today

RE: COALESCE Vs ISNULL

Mike John — Fri, 12 Nov 2010 00:19:51 GMT

Because in this case ALL the arguments are "untyped" nulls. so declare @a intselect coalesce(@a,null) will retunr NULL (and not an error).Mike

RE: COALESCE Vs ISNULL

Hardy21 — Thu, 11 Nov 2010 21:34:40 GMT

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?

RE: COALESCE Vs ISNULL

Hardy21 — Thu, 11 Nov 2010 21:28:19 GMT

Good one point question. Thanks

RE: COALESCE Vs ISNULL

UMG Developer — Thu, 11 Nov 2010 20:42:55 GMT

Nice question, thanks!

COALESCE Vs ISNULL

Ujar — Thu, 11 Nov 2010 20:42:27 GMT

Comments posted to this topic are about the item [B]COALESCE Vs ISNULL[/B]