The Subtle Differences Between COALESCE and ISNULL

Question

The Subtle Differences Between COALESCE and ISNULL

omu

Old Hand

Points: 385
More actions
April 18, 2025 at 12:00 am

#4576217

Comments posted to this topic are about the item The Subtle Differences Between COALESCE and ISNULL
April 18, 2025 at 4:15 am

This was removed by the editor as SPAM
April 18, 2025 at 10:22 am

This was removed by the editor as SPAM
April 18, 2025 at 10:53 am

This was removed by the editor as SPAM

Viewing 8 posts - 1 through 7 (of 7 total)

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

craig-1097162 SSC Enthusiast Points: 179 More actions · Answer 1

Would there be reasons to not just use COALESCE all the time? For the simple case, the effort to use COALESCE instead of ISNULL is the same.

Toby Harman SSCarpal Tunnel Points: 4169 More actions · Answer 2

One more for your list.

The result of an ISNULL() is NOT NULL, but the result of a coalesce is NULLable.

EXEC sp_describe_first_result_set N'SELECT OrderID, ISNULL(Discount, 0) AS DiscountValueIsNull, COALESCE(Discount, 0) AS DiscountValueCoalesce
FROM Orders;'

edmil SSC Enthusiast Points: 138 More actions · Answer 3

I have found no reason to use ISNULL over COALESCE. I believe ISNULL is specific to SQL Server while COALESCE is specified in the ISO/IEC standard.

I thought this article did nice job of telling us the difference but a not so good job of explaining the difference. I didn't see one example where the 2nd value in the chain was actually used to show a difference in outcome.

ewm2

thisisfutile Hall of Fame Points: 3669 More actions · Answer 4

edmil wrote:
I have found no reason to use ISNULL over COALESCE. I believe ISNULL is specific to SQL Server while COALESCE is specified in the ISO/IEC standard.
I'm just the opposite. I prefer ISNULL because I've never had a need for the depth of coalesce. I learned years (read: decades) ago that the optimizer is replacing "ISNULL(Discount, 0.00)" with "case when Discount is null then 0 else Discount end". I'm not sure if the optimizer is doing that with coalesce, so I've always leaned on ISNULL. My mind likes that comfort knowing that nothing fancy is happening (also, I'll never work on anything outside of SQL Server, so standards-be-damned). 😛