ISNULL vs COALESCE

Question

Post reply

ISNULL vs COALESCE

Thom A

SSC Guru

Points: 99130
More actions
May 16, 2019 at 12:00 am

#3542035

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

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

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

Stewart "Arturius" Campbell SSC Guru Points: 72624 More actions · Answer 1

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 SSCrazy Eights Points: 8511 More actions · Answer 2

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: 7167 More actions · Answer 3

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 More actions · Answer 4

weird i have not run into this before. very cool

MilesC Ten Centuries Points: 1078 More actions · Answer 5

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: 5863 More actions · Answer 6

nice reminder

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

webrunner SSC-Dedicated Points: 31862 More actions · Answer 7

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

Sue_H SSC Guru Points: 90860 More actions · Answer 8

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: 9996 More actions · Answer 9

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

Thom A SSC Guru Points: 99130 More actions · Answer 10

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

Sue_H SSC Guru Points: 90860 More actions · Answer 11

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: 99130 More actions · Answer 12

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