COALESCE vs. ISNULL

Larry Brister
Larry Brister used Ask the Experts™
on
Hey folks,
  What's the difference between coalesce and isnull an which is suggested when?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior .Net Consultant
Top Expert 2016
Commented:
isnull only compares 2 values.
coalesce can compare many values (returning the first non null)
Larry Bristersr. Developer

Author

Commented:
emoreau:,
Thanks...will award points shortly.  You have any good klinks withe examples?

Commented:
If you have just 1 value to compare, are allmost the same (the difference is in the type returning if is null or not), see http://msdn.microsoft.com/es-es/library/ms190349.aspx
If you have to compare a lot of values, coalesce can reduce the sintax
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

As far as which is suggested when, that depends on how many values you need to compare and if you can compare them in an order.....

Coalesce will work its way through a series of values and return the FIRST non-null value.

so if you need to check several values and determine what to return, then use coalesce.  if you just need to see if a value is null, then use isnull.
Larry Bristersr. Developer

Author

Commented:
Ok folks...looking at my original question emoreau: answered it.
Everyone had great comments though but looks like points go to first reply?
Éric MoreauSenior .Net Consultant
Top Expert 2016
Commented:
Larry Bristersr. Developer

Author

Commented:
Thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial