NULL fields and the coalesce statement in SQL

SQL Nulls

One thing that can be confusing with MS-SQL, is how it deals with NULL fields in conditional statements. To illustrate, lets assume that we have a table (TABLEA), with three records.  One of the fields in the table is FIELD1.  FIELD1 contains the following data
     A
     B
     NULL

Then, let’s take the following statement:
     SELECT FIELD1 from TABLEA where FIELD1’A’

Most people would assume that this would return the records where FIELD1 is "B" and FIELD1 is NULL.  This is not the case.  It only returns the one where FIELD1 is "B".  The reason is that the case where FIELD1 is NULL, is not defined as true or false, and is ignored.  So, one way to get the record where FIELD1 is NULL is:
     SELECT FIELD1 from TABLEA where FIELD1’A’ or FIELD1 is NULL

There is another, and often better way however.  That is using the "coalesce" statement.  They relevant syntax is "coalesce(,,…,,)", where is any field from the table (or multiple tables if a join is used), and is a fixed value.  The can be left out, which may result in the result of the coalesce still being NULL.

So, in it’s most basic for, using the table and field from above, it would be something like "coalesce(FIELD1,’B’)".  What coalesce does, is evaluate the fields in the order given.  The result is the first field found that is not NULL, or NULL if all arguments are NULL.  

In the example given, first FIELD1 is checked.  If FIELD1 is not NULL (say "A" for example), then the result of the coalesce is "A".  If FIELD1 is NULL, then the next argument is checked.  In this case the next argument is "B", so that is returned.  That is why having a fixed value as the last argument will never result in coalesce returning NULL.

So, to put it all together, using our initial example.  If you used the statement
     SELECT FIELD1 from TABLEA where coalesce(FIELD1,”)’A"
will return two records.  The ones where FIELD1 is "B", and where FIELD1 is NULL.  The result in the second case will be NULL, and not "B".  This is because the coalesce was done in the "where" clause, and not in the field list.  In other words the coalesce is done only when determining what records are to be selected, but the actual value contained in the field is what is returned.

A coalesce can be used in the field list also, if desired.  So, using:
     SELECT coalesce(FIELD1,'(empty)’) from TABLEA where coalesce(FIELD1,”)’A"
will again return two records.  One with "B", and the second one (where FIELD1 is NULL), will be returned as the string "(empty)".

Dave.