ISNULL vs COALESCE

  • Thom A

    SSC Guru

    Points: 98300

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

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • Stewart "Arturius" Campbell

    SSC Guru

    Points: 71356

    Nice one, thanks, Thom

    there have been times when the use of COALESCE has come in pretty handy...

    ____________________________________________
    Space, the final frontier? not any more...
    All limits henceforth are self-imposed.
    “libera tute vulgaris ex”

  • Rune Bivrin

    SSCertifiable

    Points: 7554

    Good question!

    This is the main reason I avoid ISNULL, if possible.

    Another aspect is the nullability of the result. ISNULL will infer that from the nullability of the second argument, whereas COALESCE always returns a nullable type. This can be an issue when creating tables with SELECT ... INTO. In that case I sometimes resort to ISNULL(COALESCE(value1, value2, value3, ...), ''), just to make the result NOT NULL.


    Just because you're right doesn't mean everybody else is wrong.

  • SGT_squeequal

    SSCertifiable

    Points: 7063

    nice subject, I use coalesce more than often and its always been very useful

     

     

    ***The first step is always the hardest *******

  • jthomas 6231

    SSC Veteran

    Points: 270

    weird i have not run into this before.  very cool

  • MilesC

    Mr or Mrs. 500

    Points: 583

    Very good question, Thom!!  When I read the question first, I thought the actual answer was the least likely to be correct.  Then, I did some reading and learned a few things and decided that it had to be right.  Thanks for letting me learn!!

  • Shayn Thomas

    SSCertifiable

    Points: 5399

    nice reminder

    ---------------------------------------------------------------------------------------
    The more you know, the more you know that you dont know

  • webrunner

    One Orange Chip

    Points: 29900

    Thanks for this question. I got it wrong but learned something important about COALESCE.

     

    -- webrunner

     

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and says Can I join you?
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • Sue_H

    SSC Guru

    Points: 90165

    I thought it was a fun question. I think the thorough explanation, links (that worked) are great!

    Thanks for doing such a good job with QoD where it ends up being a learning experience for everyone.

     

    Sue

     

  • George Vobr

    SSCrazy Eights

    Points: 8955

    Bravo Thom, it's a great question with an excellent explanation!

  • Thom A

    SSC Guru

    Points: 98300

    Sue_H wrote:

    I think the thorough explanation, links (that worked) are great!

    Did one links not work for you Sue? I tried them and they all seemed to work, but if there is a broken link let me know and I'll ask to get it changed.

    Thanks!

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • Sue_H

    SSC Guru

    Points: 90165

    Nope all worked, meant exactly what I wrote.

    There have been problems with other QoDs with broken links, incorrect explanations and just poor questions. In comparison to some of those, yours was exceptional! Hopefully others follow your great example. Thanks for your efforts on this -

    Sue

  • Thom A

    SSC Guru

    Points: 98300

    Sue_H wrote:

    Nope all worked, meant exactly what I wrote. There have been problems with other QoDs with broken links, incorrect explanations and just poor questions. In comparison to some of those, yours was exceptional! Hopefully others follow your great example. Thanks for your efforts on this - Sue

     

    Ahh apologies, I miss understood. Thanks for the feedback, I appreciate it. 🙂

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

Viewing 13 posts - 1 through 13 (of 13 total)

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