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 is that the theorists who originally conceived the idea of relational databases decided that the existence of nulls warranted the creation of three-valued logic, illustrated in Figure 4-1.

Figure 4-1. The monster that is three-valued logic has bitten more than one unwary SQL developer (sketch by Matt Williams)

If I were to ask any person on the street where I live whether Roxolana's termination date is today, the answer I'd most likely get back would be no. People generally think in terms of something being either true or false. Roxolana's termination date is null, which is not the same as today, right? The database however, adds a new truth value, that of unknown. With some specific exceptions, any comparison to null yields a result of unknown. Ask the database whether Roxolana was terminated today, and the database would answer the equivalent of unknown. And unknown is neither true nor false.

Tip

Nullity is most critical to understand. You can't be an effective SQL user without a good grounding in three-valued logic and the effects of nulls in expressions.

The three queries in Example 4-21 clearly illustrate the effect of nulls on comparison expressions. You'll notice that Roxolana appears in none of the results. The first query illustrates that null is not not equal to today. The second query shows that null is not equal to today. The third query shows that null is not even equal to itself.

Example 4-21. The effects of "unknown"

               SELECT employee_name
               FROM employee
               WHERE employee_termination_date <> SYSDATE;

EMPLOYEE_NAME
----------------------------------------
Mykhailo Hrushevsky
Pavlo Virsky
Pavlo Chubynsky
Ivan Mazepa
Igor Sikorsky
Mykhailo Verbytsky

SELECT employee_name
               FROM employee
               WHERE employee_termination_date = SYSDATE;

no rows selected

SELECT employee_name
               FROM employee
               WHERE employee_termination_date = NULL;

no rows selected

If null is not equal to null, how then do you even detect its existence? How do you go about returning rows with nulls?

Tip

I tend to refer to a specific column or expression result as being null. I don't usually refer to null as a value. Others, including even the authors of the ANSI/ISO SQL standard, do use the term "null value." I don't like that approach because, logically, null represents the absence of a value. The term "null value" confuses the issue. Don't get worked up over this terminology issue though. I don't. Just be aware that null and null value mean the same thing: no value at all.

Detecting Nulls

Fortunately, SQL's designers had the forethought to provide a mechanism for detecting and dealing with null values. The standard way to detect null values is to use the IS NULL predicate, as demonstrated in Example 4-22. You can use IS NOT NULL to return rows for which a given column is not null.

Example 4-22. Using IS NULL to detect nulls

                  SELECT employee_name, employee_termination_date
                  FROM employee
                  WHERE employee_termination_date IS NULL;

EMPLOYEE_NAME        EMPLOYEE_TERMINATI
-------------------- ------------------
Marusia Churai
Mykola Leontovych
Lesia Ukrainka
Taras Shevchenko
Roxolana Lisovsky

SELECT employee_name, employee_termination_date
                  FROM employee
                  WHERE employee_termination_date IS NOT NULL;

EMPLOYEE_NAME        EMPLOYEE_TERMINATI
-------------------- ------------------
Mykhailo Hrushevsky  05-MAY-04
Pavlo Virsky         01-APR-04
Pavlo Chubynsky      15-NOV-04
Ivan Mazepa          30-SEP-04
Igor Sikorsky        04-APR-04
Mykhailo Verbytsky   31-OCT-04

Notice that the null termination dates in Example 4-22 are blank. This is unlike the results you saw in Example 4-20 but represents the default behavior of SQL*Plus. If you want to see nulls as something other than blanks, you will need to issue a SET NULL command to specify an alternative representation.

Nulls in Expressions

The expressions involving nulls in Examples Example 4-21 and Example 4-22 are all WHERE clause expressions. Nulls make their presence felt in other types of expressions. The general rule is that if any value in an expression is null, then the result of that expression will be null. Example 4-23 demonstrates this concept by attempting to add $100 to the hourly rate of Roxolana.

Example 4-23. Adding to NULL yields NULL

                  SELECT employee_name, employee_billing_rate, 
                         employee_billing_rate + 100 increased_rate
                  FROM employee
                  WHERE employee_id = 116;

EMPLOYEE_NAME        EMPLOYEE_BILLING_RATE INCREASED_RATE
-------------------- --------------------- --------------
Roxolana Lisovsky

In this case, null + 100 is still null. Often treating null as if it were zero can be handy. Oracle's NVL function allows you to do that. NVL takes two arguments. The first argument may be any expression or column name; the type does not matter. If the first argument is not null, then that argument will be returned. The second argument is an alternate value to be returned only when the first is null. Example 4-24 uses NVL to treat null billing rates as zero values.

Example 4-24. Using NVL to provide an alternative, non-NULL value

                  SELECT employee_name, NVL(employee_billing_rate,0) billing_rate, 
                         NVL(employee_billing_rate,0) + 100 increased_rate
                  FROM employee
                  WHERE employee_id IN (113, 116);

EMPLOYEE_NAME        BILLING_RATE INCREASED_RATE
-------------------- ------------ --------------
Mykhailo Verbytsky            300            400
Roxolana Lisovsky               0            100

If you work with nulls often, and especially if the NVL and IS NULL functionality is not meeting your needs, you'll want to get familiar with Oracle's NVL2 and DECODE functions, as well as with CASE expressions. Any good book on Oracle SQL will cover these functions and expressions.