Ben Nadel
On User Experience (UX) Design, JavaScript, ColdFusion, Node.js, Life, and Love.
Ben Nadel at cf.Objective() 2010 (Minneapolis, MN) with: Doug Hughes and Ezra Parker and Dan Wilson and John Mason and Jason Dean and Luis Majano and Mark Mandel and Brian Kotek and Wil Genovese and Rob Brooks-Bilson and Andy Matthews and Simeon Bateman and Ray Camden and Chris Rockett and Joe Bernard and Dan Skaggs and Byron Raines and Barney Boisvert and Simon Free and Steve 'Cutter' Blades and Seth Bienek and Katie Bienek and Jeff Coughlin
Ben Nadel at cf.Objective() 2010 (Minneapolis, MN) with: Doug Hughes@doughughes ) , Ezra Parker , Dan Wilson@DanWilson ) , John Mason@john_mason_ ) , Jason Dean@JasonPDean ) , Luis Majano@lmajano ) , Mark Mandel@Neurotic ) , Brian Kotek , Wil Genovese@wilgeno ) , Rob Brooks-Bilson@styggiti ) , Andy Matthews@commadelimited ) , Simeon Bateman@simBateman ) , Ray Camden@cfjedimaster ) , Chris Rockett ( @RockettMan ) , Joe Bernard@JEBernard ) , Dan Skaggs@dskaggs ) , Byron Raines ( @byronraines ) , Barney Boisvert@barneyb ) , Simon Free@simonfree ) , Steve 'Cutter' Blades@cutterbl ) , Seth Bienek@sethbienek ) , Katie Bienek@KatieBienek ) , and Jeff Coughlin@jeffcoughlin )

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

By Ben Nadel on
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):

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

  • SELECT
  • COALESCE(
  • -- DB date/time field.
  • began_dating,
  •  
  • -- System date/time.
  • getDate(),
  •  
  • -- Text expression
  • 'sometime'
  • ) AS began_dating
  • FROM
  • girl

Even though 'sometime' is not of type date/time, no errors are thrown. In fact, you can force a null to be found and still no errors are thrown:

  • SELECT
  • COALESCE(
  • -- Force null value.
  • NULL,
  •  
  • -- System date/time.
  • getDate(),
  •  
  • -- Text expression
  • 'sometime'
  • ) AS began_dating
  • FROM
  • girl

Works fine. However, if you switch the second and third arguments, such that the string value comes before the date value, it DOES throw an error:

  • SELECT
  • COALESCE(
  • -- Force null value.
  • NULL,
  •  
  • -- Text expression
  • 'sometime',
  •  
  • -- System date/time.
  • getDate()
  • ) AS began_dating
  • FROM
  • girl

This throws the error:

Syntax error converting date/time from character string.

So again, this is rarely gonna be an issue, if ever, but still, good to know that there are errors sometimes and why they might seem random.




Reader Comments

Clearly the advantage of multiple fallbacks is not exclusive to coalesce.

Merely do ISNULL(A, ISNULL(B, ISNULL(C, D))) to choose between 4 values.