<< Altering populated tables | Firebird Null Guide | Summary >>

Testing for NULL and equality in practice

This section contains some practical tips and examples that may be of use to you in your everyday dealings with NULLs. It concentrates on testing for NULL itself and testing the (in)equality of two things when NULLs may be involved.

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 defensible. 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';

Since this code covers more than two possibilities, it's more elegant to use the CASE syntax (available in Firebird 1.5 and up):

 CanVote = case
             when Age is null then 'Unsure'
             when Age >= 18 then 'Yes'
             else 'No'
           end;

Or, prettier:

 CanVote = case
             when Age >= 18 then 'Yes'
             when Age < 18 then 'No'
             else 'Unsure'
           end;

back to top of page

Equality tests

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 way to do this depends on your Firebird version.

Firebird 2.0 and up

In Firebird 2 and higher, you test for null-encompassing equality with DISTINCT. This has already been discussed, but here's a quick recap. Two expressions are considered:

• DISTINCT if they have different values or if one of them is NULL and the other isn't;
• NOT DISTINCT if they have the same value or if both of them are NULL.

[NOT] DISTINCT always returns true or false, never NULL or something else. Examples:

 if (A is distinct from B) then...

 if (Buyer1 is not distinct from Buyer2) then...

Skip the next section if you're not interested in the pre-Firebird-2 stuff.

Earlier Firebird versions

Pre-2.0 versions of Firebird don't support this use of DISTINCT. Consequently, the tests are a little more complicated and there are some pitfalls to avoid.

The correct equality test for pre-2.0 Firebird versions 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 don't get the bright idea of inverting the expression and using it as an inequality test:

 /* 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 (starting at 1.5, an empty BEGIN..END block is also allowed), or use this longer test expression:

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

Remember, all this is only necessary in pre-2.0 Firebird versions. From version 2 onward, the inequality test is simply if (A is distinct from B).

back to top of page

Summary of (in)equality tests

Please keep in mind that with Firebird 1.5.x and earlier:

• the equality test returns NULL if exactly one operand is NULL;
• the inequality test returns NULL if both operands are NULL.

In an IF or WHERE context, these NULL results act as false – which is fine for our purposes. But remember that an inversion with NOT() will also return NULL – not true. Also, if you use the 1.5-and-earlier tests within CHECK constraints in Firebird 2 or higher, be sure to read the section CHECK constraints, if you haven't done so already.

Tip: Most JOINs are made on equality of fields in different tables, and use the "=" operator. This will leave out all NULL-NULL pairs. If you want NULL to match NULL, pick the equality test for your Firebird version from the table above.

back to top of page

Finding out if a field has changed

In triggers you often want to test if a certain field has changed (including: gone from NULL to non-NULL or vice versa) or stayed the same (including: kept its NULL state). This is nothing but a special case of testing the (in)equality of two fields, so here again our approach depends on the Firebird version.

In Firebird 2 and higher we use this code:

 if (New.Job is not distinct from Old.Job)
   then ...Job field has stayed the same...
   else ...Job field has changed...

And in older versions:

 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...

back to top of page
<< Altering populated tables | Firebird Null Guide | Summary >>