Null values in table columns

Some columns cannot have a meaningful value in every row. Db2 uses a special value indicator, the null value, to stand for an unknown or missing value. A null value is a special value that Db2 interprets to mean that no data is present.

If you do not specify otherwise, Db2 allows any column to contain null values. Users can create rows in the table without providing a value for the column.

Using the NOT NULL clause enables you to disallow null values in the column. Primary keys must be defined as NOT NULL.

Example

Begin general-use programming interface information.

For example, The table definition for the DEPT table specifies when you can use a null value. Notice that you can use nulls for the MGRNO column only:

CREATE TABLE DEPT
      (DEPTNO    CHAR(3)           NOT NULL,
       DEPTNAME  VARCHAR(36)       NOT NULL,
       MGRNO     CHAR(6)                   ,
       ADMRDEPT  CHAR(3)           NOT NULL,
       PRIMARY KEY (DEPTNO)                )
  IN MYDB.MYTS;

End general-use programming interface information.

Before you decide whether to allow nulls for unknown values in a particular column, you must be aware of how nulls affect results of a query:

Nulls in application programs: Nulls do not satisfy any condition in an SQL statement other than the special IS NULL predicate. Db2 sorts null values differently than non-null values. Null values do not behave like other values. For example, if you ask Db2 whether a null value is larger than a given known value, the answer is UNKNOWN. If you then ask Db2 whether a null value is smaller than the same known value, the answer is still UNKNOWN.
If getting a value of UNKNOWN is unacceptable for a particular column, you could define a default value instead. Programmers are familiar with the way default values behave.
Nulls in join operations: Nulls need special handling in join operations. If you perform a join operation on a column that can contain null values, consider using an outer join.