Oracle SQL*Plus: The Definitive Guide, 2nd Edition

The Concept of Null

When writing SQL statements, you must be vigilant for possible nulls, always taking care to consider their possible effect on a WHERE clause or other SQL expression. What is a null? It's what you get in the absence of a specific value. Suppose that you issue the INSERT statement shown in Example 4-20 to record a new hire in your database:

INSERT INTO employee (employee_id, employee_name)
   VALUES (116, 'Roxolana Lisovsky');

Quick! What value do you have for a hire date? What about the termination date? The answer is that it depends. The employee table happens to have a default value specified for the employee_hire_date column. Because the INSERT statement doesn't specify a hire date, the hire date defaults to the current date and time. What about the termination date? There's no default for that column, so what's the value? The answer is there is no value. Because no value is supplied, employee_termination_date is said to be null. Example 4-20 uses the SET NULL command to make the null termination date obvious.

Example 4-20. Inserting a NULL value

               INSERT INTO employee (employee_id, employee_name)
                  VALUES (116, 'Roxolana Lisovsky');

               SET NULL ***NULL***

               SELECT employee_id, employee_name, 
                      employee_hire_date, employee_termination_date
               FROM employee
               WHERE employee_id = 116; 

EMPLOYEE_ID EMPLOYEE_NAME      EMPLOYEE_HIRE_DATE EMPLOYEE_TERMINATI
----------- ------------------ ------------------ ------------------
        116 Roxolana Lisovsky  03-JUN-04          ***NULL***

The problem with nulls ...