TECH TIP — Uplink Archive

This article explains null values in databases—how they represent unknown or missing data and differ from zero or empty strings—and their practical implications for queries and data management across software like Visual FoxPro and Access.

On being and nothingness

If I were clever, I would title this Tech Tip "On being and nothingness." But I'm not and I did, so there's an existential dilemma for you. It's about nulls, an explicit, unambiguous and logically tricky way of talking about nothing. Knowing about nulls is significant for at least two kinds of queries: summary queries using the count ( ) aggregate function and queries filtering for empty character fields. If you use FoxPro versions 2.x, then you may never have heard of nulls. Even if you've used the last of the old FoxPro, version 2.6, you might not have noticed the idea of nulls. Nothing lost, however: the implementation of nulls in 2.6 was incomplete and not worth bothering with. If you made the switch from old FoxPro to new Visual FoxPro, or to Access, you might have bumped into nulls in the documentation - or been assaulted by one in an error message. Or you might have been using bad words in front of your 11-month-old computer because you kept doing an Access query for blank occupations, the query kept saying there weren't any, but you knew there were plenty. You were in the twilight edge of the null zone and things are different there. Here is an explanation from the Visual FoxPro documentation: Visual FoxPro provides support for null values. This support simplifies the task of representing unknown data and makes it easier to work with Microsoft Access or SQL databases that may contain null values.

Standardizing the unknown

The explanation is intended to be gentle, but it borders on the euphemistic. I would say that nulls standardize the representation of unknown data. And, obviously, software is easier - and gives the user fewer problems when it conforms to standards.

Also from the Visual FoxPro documentation, these definitions:

Null values are: Equal to the absence of any value. Different than zero, the empty string ("6" or blank. Sorted ahead of other data. Propagated in calculations and most functions. Null values affect the behavior of commands and functions, logical expressions and parameters. Null value: Having no explicitly assigned value. NULL is not equivalent to zero or blank. A value of NULL is not considered to be greater than, less than, different than, or equivalent to any other value, including another value of NULL.

Any datatype can contain a null. Nulls should be used to indicate things like missing data or unknown value. It does not stand for a quantity with a meaning. For example, an age column with a null means that the age is unknown, not that the person is unborn or dead. Same thing for a column indicating sex or gender: null means unknown, not a new biological condition.

Nullified values

Here is how nulls affect summary queries using the count ( ) function. Using count (ColumnName) will only include rows where that particular column has some non-null value in it. Using count (*) will count all rows. FoxPro 2.6, for example, allows both methods because this is standard SQL. It just treats them identically and gives the same answer, which is not standard SQL. The other item to explain from the documentation is this statement: "Nulls propagate through calculations and most functions." If a single column or variable containing a null is anywhere inside a nest of calculations or functions, then the answer is null. This fact can be especially tricky if you are looking at logical or true/false columns. Null is neither true nor false. Without knowing this, you could misinterpret your results.

Richard Mullins can be reached by e-mail at richard@nicar.org.