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.

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. To select the values for all rows that contain a null value for the manager number, you can specify:

SELECT DEPTNO, DEPTNAME, ADMRDEPT   FROM CORPDATA.DEPARTMENT   WHERE MGRNO IS NULL

The result follows.

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

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

WHERE MGRNO IS NOT NULL

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 T2 has a column C2 with the following values.

C2
2
null

Run the following SELECT statement:

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

The result follows.

C1	C2
1	2
1	-
2	-
-	2

For more information about the use of null values, see the SQL Reference topic collection.

 

Parent topic:

Retrieving data using the SELECT statement