Null is a special marker used in SQL to represent missing or inapplicable data. The Null maker is used for different data types (e.g. numeric, character, date) and NULL is a reserved word in SQL. A more detailed discussion of the role and function of NULL in SQL can be found here.

EXAMPLE DATABASE: Nhanes

DATA INPUT

Table nhanes in the database Nhanes.mdb is used as input.

QUERY null_weight

SELECT rseqn, fseqn, ethn, sex, age, ageunit, weight, height
FROM nhanes
WHERE weight IS NULL;

Query null_weight produces the output below which contains 179 records where the weight is missing.

QUERY null_weight_height

SELECT rseqn, fseqn, ethn, sex, age, ageunit, weight, height
FROM nhanes
WHERE weight IS NULL OR height IS NULL;

Query null_weight_height produces the output below which contains 3481 records where either the weight or the height are missing.

QUERY notnull_weight_height

SELECT rseqn, fseqn, ethn, sex, age, ageunit, weight, height
FROM nhanes
WHERE weight IS NOT NULL AND height IS NOT NULL;

Query notnull_weight_height produces the output below which contains 27830 records where neither the weight nor the height are missing.

DISCUSSION

The queries above show how records can be retrieved based on missing values or non-missing values in numeric fields. The same query syntax is also applicable for text fields and date fields. A value of zero (0) for a numeric field or a space character ( ) for a text field are different from a missing (NULL) value.

The special operators IS and IS NOT must be used to determine whether a field contains Null values or not. The = and <> operators may not generate an error message, but will return a wrong result if they are used for Null values.