SQL COALESCE() Like ISNULL() On Steroids ... With Caveats : Kinky Solutions : A Student's Perspective by Ben Nadel

SQL COALESCE() Like ISNULL() On Steroids ... With Caveats

Posted August 4, 2006 at 5:04 PM

Tags: SQL

My main man, Rick O, just told me about the SQL statement COALESCE(). He had pointed out that my use of ISNULL() is not as universally accepted on DB platforms as COALESCE(). This function, as he explained, takes a list of items and returns the first one that is not NULL. After looking it up, I also see that if no values are non-null, then the function call itself returns NULL.

The main advantage here is that you can have multiple fall back values. As an example, let's say you wanted to return someone's phone number. If they had a business phone, mobile phone, and page, you could return which ever one was available (in order of importance):

Launch code in new window » Download code as text file »

SELECT
COALESCE(
business_phone,
cell_phone,
pager
) AS phone_number
FROM
contact

I think this is a very cool function. The one caveat is that you cannot mix data types in your list. This of course is not something that will come up very open, but, it's worth knowing about. Now, this doesn't always throw an error. Sometimes it works, sometimes it does not; it depends on where null values are found and which data types are being used.

For example, this does NOT throw an error:

Launch code in new window » Download code as text file »