Handling null values

A null value indicates the absence of a column value in a row. A null value is an unknown value; it is not the same as zero or all blanks.

About this task

Null values can be used as a condition in the WHERE and HAVING clauses. For example, a WHERE clause can specify a column that, for some rows, contains a null value. A basic comparison predicate using a column that contains null values does not select a row that has a null value for the column. This is because a null value is not less than, equal to, or greater than the value specified in the condition. The IS NULL predicate is used to check for null values.

Examples

Example 1: Selecting rows that contain null in a column

To select the values for all rows that contain a null value for the manager number, you can issue the following statement:

SELECT DEPTNO, DEPTNAME, ADMRDEPT
  FROM DSN8C10.DEPT  
  WHERE MGRNO IS NULL

The following table shows the result.

DEPTNO	DEPTNAME	ADMRDEPT
D01	DEVELOPMENT CENTER	A00
F22	BRANCH OFFICE F2	E01
G22	BRANCH OFFICE G2	E01
H22	BRANCH OFFICE H2	E01
I22	BRANCH OFFICE I2	E01
J22	BRANCH OFFICE J2	E01

Example 2: Selecting rows that do not contain a null value

To get the rows that do not have a null value for the manager number, you can change the WHERE clause in the previous example like this:

WHERE MGRNO IS NOT NULL

Example 3: Comparing values that contain the NULL value

Another predicate that is useful for comparing values that can contain the NULL value is the DISTINCT predicate. Comparing two columns using a normal equal comparison (COL1 = COL2) will be true if both columns contain an equal non-null value. If both columns are null, the result will be false because null is never equal to any other value, not even another null value. Using the DISTINCT predicate, null values are considered equal. So COL1 is NOT DISTINCT from COL2 will be true if both columns contain an equal non-null value and also when both columns are the null value.

For example, suppose that you want to select information from two tables that contain null values. The first table T1 has a column C1 with the following values.

C1
2
1
null

The second table has column C2 with the following values.

C2
2
null

Assume that you issue the following SELECT statement:

SELECT * 
    FROM T1, T2
    WHERE C1 IS DISTINCT FROM C2

The result follows.

C1	C2
1	2
1	-
2	-
-	2