Dealing with NULLs

Testing for `NULL` - if it matters

Quite often, you don't need to take special measures for fields or variables that may be NULL. For instance, if you do this:

select * from Customers where Town = 'Ralston'

you probably don't want to see the customers whose town is unspecified. Likewise:

if (Age >= 18) then CanVote = 'Yes'

doesn't include people of unknown age, which is also defendable. But:

if (Age >= 18) then CanVote = 'Yes';
else CanVote = 'No';

seems less justified: if you don't know a person's age, you shouldn't explicitly deny her the right to vote. Worse, this:

if (Age < 18) then CanVote = 'No';
else CanVote = 'Yes';

won't have the same effect as the previous. If some of the NULL ages are in reality under 18, you're now letting minors vote!

The right approach here is to test for NULL explicitly:

if (Age is null) then CanVote = 'Unsure';
else 
  if (Age >= 18) then CanVote = 'Yes';
  else CanVote = 'No';
Note

else always refers back to the last if in the same block. But it's often good to avoid confusion by putting begin...end keywords around a group of lines. I didn't do that here though - I wanted to keep the number of lines down. And then I made up for it by adding this note ;-)

Finding out if fields are the same

Sometimes you want to find out if two fields or variables are the same and you want to consider them equal if they are both NULL. The correct test for this is:

if (A = B or A is null and B is null) then...

or, if you want to make the precedence of the operations explicit:

if ((A = B) or (A is null and B is null)) then...

A word of warning though: if exactly one of A and B is NULL, the test expression becomes NULL, not false! This is OK in an if statement, and we can even add an else clause which will be executed if A and B are not equal (including when one is NULL and the other isn't):

if (A = B or A is null and B is null) 
  then ...stuff to be done if A equals B...
  else ...stuff to be done if A and B are different...

But you shouldn't get the bright idea of inverting the expression and using it as an inequality test (like I once did):

/* Don't do this! */
if (not(A = B or A is null and B is null))
  then ...stuff to be done if A differs from B...

The above code will work correctly if A and B are both NULL or both non-NULL. But it will fail to execute the then clause if exactly one of them is NULL.

If you only want something to be done if A and B are different, either use one of the correct expressions shown above and put a dummy statement in the then clause, or use this longer test expression:

/* This is a correct inequality test: */
if (A <> B
    or A is null and B is not null
    or A is not null and B is null) then...

Finding out if a field has changed

In triggers, it's often useful to know if a certain field has changed (including: gone from NULL to non-NULL or vice versa) or stayed the same. This is nothing but a special case of testing the (in)equality of two fields. Just use New.Fieldname and Old.Fieldname for A and B:

if (New.Job = Old.Job or New.Job is null and Old.Job is null)
  then ...Job field has stayed the same...
  else ...Job field has changed...

Substituting `NULL` with a value

The `COALESCE` function

Firebird 1.5 has a function that can convert NULL to most anything else. This enables you to do an on-the-fly conversion and use the result in your further processing, without the need for “if (MyExpression is null) then” constructs. The function is called COALESCE and you call it like this:

COALESCE(Expr1, Expr2, Expr3, ...)

COALESCE returns the first non-NULL expression in the argument list. If all the expressions are NULL, it returns NULL.

This is how you would use COALESCE to construct a person's full name from the first, middle and last names, assuming that some middle name fields may be NULL:

select FirstName
       || coalesce(' ' || MiddleName, '')
       || ' ' || Lastname
from Persons

Or, to create an as-informal-as-possible name from a table that also includes nicknames, and assuming that both nickname and first name may be NULL:

select coalesce (Nickname, FirstName, 'Mr./Mrs.')
       || ' ' || Lastname
from OtherPersons

COALESCE will only help you out in situations where NULL can be treated in the same way as some allowed value for the datatype. If NULL needs special handling, like in the “right to vote” example used previously, your only option is to go for “if (MyExpression is null) then” after all.

Firebird 1.0: the `*NVL` functions

Firebird 1.0 doesn't have COALESCE. However, you can use four UDFs that provide a good part of its functionality. These UDFs reside in the fbudf lib and they are:

iNVL, for integer arguments
i64NVL, for bigint arguments
dNVL, for double precision arguments
sNVL, for strings

The *NVL functions take exactly two arguments. Like COALESCE, they return the first argument if it's not NULL; otherwise, they return the second. Please note that the Firebird 1.0 fbudf lib - and therefore, the *NVL functions - is only available for Windows.