ISNULL vs COALESCE

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

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

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

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

     

     

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

  • weird i have not run into this before.  very cool

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

  • nice reminder

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

  • 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 asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • 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

     

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

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

  • 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

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

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

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