COALESCE Vs ISNULL

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

  • Nice question, thanks!

  • Good one point question. Thanks

    Thanks

  • 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

  • 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

  • simple one today

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

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

  • 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!

  • Nice question.

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

  • 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')

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

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


    Steve Eckhart

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

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

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