Testing if something is NULL

Testing if something is `NULL`

In light of the havoc that NULL can wreak, you will often want to know whether something is NULL before you use it in an expression. To some, the obvious test would seem to be

if (A = NULL) then...

and indeed there are database management systems that support this syntax to determine nullness. But the SQL standard doesn't allow it, and neither does Firebird. In versions prior to 2.0 this entire syntax is even illegal. From 2.0 onward it is permitted, but the comparison always returns NULL, regardless of A's state and value. It is therefore worthless as a nullness test - what we need is a clear true or false result.

The correct way to test for NULL is:

...is null / ...is not null

These tests always return true or false - no messing around. Examples:

```
if (MyField is null) then...
```

select * from Pupils where PhoneNumber is not null

select * from Pupils where not (PhoneNumber is null)
/* does the same as the previous example */

update Numbers set Total = A + B + C where A + B + C is not null

You could say that whereas “=” (when used as an equality operator) can only compare values, “is” tests for a state.