How to check for null values

Before you retrieve a column value, you might first want to determine if the column value is null.

Applications frequently need to check two values to see if they are equal or not equal. You can use a basic predicate to do an equal or not equal comparison. An equal comparison or a not equal comparison can return true, false, or unknown. The normal rule in SQL, except for the DISTINCT predicate, is that one null value is never equal to another null value. If either or both operands of a basic predicate are the null value, the result is unknown.

Depending on your application, you might want to include or exclude rows that have a NULL value in a column. You can use the NULL predicate to do that.

MY_EMP is a table that has a row with the last name and phone number for each of the employees in a company. In this company, no employees share a phone number, but some employees might not have a phone number. The LASTNAME column contains the last name of each employee. The PHONENO column contains the phone number for each employee. If an employee does not have a phone, the PHONENO column value is NULL. The table might look like this:

LASTNAME	PHONENO
HAAS	-------
THOMPSON	3476
KWAN	4738
GEYER	6789
STERN	6423

Suppose that you want to know the last name of the employee who has no phone number. Using a query like this one does not work, because if the PHONENO column value is NULL, the WHERE clause compares a NULL column value to a null host variable value. The result of that comparison is unknown.


MOVE -1 TO PHONENO-IND. 
EXEC SQL
 SELECT LASTNAME
  INTO :LASTNAME-HV
  FROM MY_EMP
  WHERE PHONENO = :PHONENO-HV :PHONENO-IND 
END-EXEC.

To find the employee with a NULL value for the phone number, you need to use a NULL predicate:

EXEC SQL
 SELECT LASTNAME
  INTO :LASTNAME-HV
  FROM MY_EMP
  WHERE PHONENO IS NULL 
END-EXEC.

The SELECT statement returns a LASTNAME value of 'HAAS'.

Now suppose that you want to select the last name of an employee whose phone number matches a certain value or whose phone number is NULL. To do that, you need to code two search conditions: one to handle the case where the phone number is not NULL, and another to handle the case where the phone number is NULL. The SELECT statement might look like this:

EXEC SQL
  SELECT LASTNAME
  INTO :LASTNAME-HV
  FROM MY_EMP
  WHERE (PHONENO IS NOT NULL AND :PHONENO-HV :PHONENO-IND IS NOT NULL
         AND PHONENO = :PHONENO-HV )  -- Search condition for non-NULL
                                      -- phone number                             
         OR
        (PHONENO IS NULL AND :PHONENO-HV :PHONENO-IND IS NULL)
                                      -- Search condition for NULL
                                      -- phone number
END-EXEC.

If you set :PHONENO-HV to '3476' and :PHONENO-IND to 0, the SELECT statement returns 'THOMPSON' because the search condition for a non-NULL phone number is used. If you set :PHONENO-HV to any value, and set :PHONENO-IND to -1, the SELECT statement returns 'HAAS' because the search condition for a NULL phone number is used.