IS and NULL or NOT NULL
For the IS operator there are two special modifiers: NULL and NOT NULL. They are only relevant for very specific database operations. The database distinguishes between a text field which is totally empty and not used, and one which contains the empty string (character string of length 0, represented by ‘’). The operator NOT NULL will find all fields containing a character string, even if it is the empty string.
The rule LASTNAME IS NULL will, for instance, filter only those recipients where a last name is missing. By contrast, LASTNAME = ‘’ will find recipients where the last name was originally there but was subsequently deleted or replaced by the “empty” string.
This also works with numeric fields. If a field contains the digit 0, the database does not consider this to be an empty string. SHOE_SIZE = 0 will find only those recipients where the shoe size field SHOE_SIZE in fact contains the entry 0. SHOE_SIZE IS NULL, on the other hand, will find recipients who have never entered their shoe size in that particular field.