SQL can have two types of ’empty’ fields: fields that are full of blanks and fields that are so empty they don’t even contain that. It’s like the difference between a jar full of air and a jar that contains a vacuum. They both look the same but they are really quite different.

In SQL queries

When you are writing SQL queries you need to take account of both types otherwise you will get results that, seemingly, make no sense.

Queries that deal with empty fields always have to say something like:


where my_field is null or my_field=''


You also need to be careful when looking for a field to be not equal to a particular value, eg:

where my_field <> 'xxx'


On the face of it this seems straightforward enough, but SQL can’t look inside a field that contains a NULL; that means that it can’t say for sure that it doesn’t contain ‘xxx’!

You could say:


where my_field is NULL or my_field <> 'xxx'


but sometimes it’s easier to use a subquery, as in this slightly more complete example:


select * from contact1 where
key1='abc' and
accountno not in
(select accountno from contact1 where key2='xxx')


In Crystal Reports

In Crystal formulae you need to do something similar:


isnull({table.my_field}) or {table.my_field}=''


Here’s an example:


if isnull({contact1.country}) or {contact1.country}='' then 'Undefined' else {contact1.country}


when inserted into a report in place of {contact1.country} will print the word ‘Undefined’ for either type of empty country field.