Working with NULLs

Writing queries against tables that can contain nulls can be a confusing endeavor. Nulls represent missing or unknown information at the column level. If a column "value" can be null, it can mean one of two things: The attribute is not applicable for certain occurrences of the entity, or the attribute applies to all entity occurrences, but the information may not always be known. Of course, it could be a combination of these two situations, too.

Nulls sometimes are inappropriately referred to as null "values." Using the term value to describe a null is inaccurate because the term null implies the lack of a value. Nulls would be better referred to as null "lack of values."

Problems with Nulls

Given that nulls are possible in DB2 tables, SQL must support three-valued logic (3VL) in order to retrieve data. To understand 3VL let's first define two-valued logic (2VL). With 2VL there are two possible answers to a query: true or false. Either the data was found or it was not found. However, if nulls exist, then there is a third possible answer: unknown.

Most of us are not accustomed to working with 3VL, and so handling nulls in DB2 queries can be difficult to master. Here are some basic rules to remember:

• NULL is the lack of a value ”it means unknown or not applicable.

• A NULL is not less than, greater than, or equal to any value.

• A NULL is not less than, greater than, or equal to another NULL.

• When NULL is compared to any other column or value in a predicate the result is unknown.

The bottom line is that 3VL is hard to understand and hard to implement properly.

NULL Guidelines

The following guidelines are helpful to keep in mind as you design, build, and test your DB2 SQL statements.

Use the Special NULL Predicate

To test for the existence of nulls, use the special predicate IS NULL in the WHERE clause of the SELECT statement. For example, the following is not a valid SQL predicate:

 WHERE  column  = NULL 

Instead, you must code this as

 WHERE column IS NULL. 

Furthermore, it is not valid to test whether a column is < NULL , <= NULL , > NULL , or >= NULL . These are all meaningless because null is the absence of a value.

Do Not Try to Design Away the Null Issue

Because of the possible problems associated with nulls, it can be enticing to simply avoid using them. This simplistic thinking goes something like this: "If I create a database without using nulls then I do not need to worry about nulls in my queries." This is not true, though.

It is possible to code a statement against tables with no nullable columns and return NULL as a result. For example, consider the following query:

 SELECT  SUM(SALARY) FROM    DSN8810.EMP WHERE   LASTNAME = 'RAINES'; 

What will be returned by this query if there are no employees with the last name of RAINES ? The answer is NULL . The impact of applying a column function (other than COUNT ) to no data is not zero, but NULL . This is true even if the column upon which the function is operating is not-nullable (as is the case with the SALARY column of the EMP sample table).

Understand Nulls and Collating

When a nullable column participates in an ORDER BY or GROUP BY clause, the returned nulls are grouped at the high end of the sort order.

Nulls are considered to be equal when duplicates are eliminated by SELECT DISTINCT or COUNT (DISTINCT column ) .

A unique index considers nulls to be equivalent and disallows duplicate entries because of the existence of nulls, unless the WHERE NOT NULL clause is specified in the index.

Consider Using IS NOT DISTINCT FROM

Two columns, each set to NULL , are not considered equivalent when compared in a predicate. In other words, as stated previously, a NULL does not equal another NULL . When a nullable column participates in a predicate in the WHERE or HAVING clause, the nulls encountered cause the comparison to evaluate to unknown.

DB2 V8 provides a new comparison operator for handling NULL called IS NOT DISTINCT FROM . This comparison operator basically treats two nulls as equivalent. So, consider the following predicate:

 WHERE COL IS NOT DISTINCT FROM :HOSTVAR 

The predicate will be true when both COL and HOSTVAR are NULL , or when both COL and HOSTVAR are set to the same non- NULL value.

When only COL or only HOSTVAR is NULL , but the other is set to a value, the result is false. Of course, the result is false when both COL and HOSTVAR are set to different values.

Consider Nulls in Your Database Design

When you build your DB2 databases be sure to consider nulls and how they are used before setting any column as nullable. Keep in mind, too, that the default if nothing is specified is a nullable column.

More information on defining and using nulls in DB2 tables is provided in Chapter 5.