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.

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?

4.3.1 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.

4.3.2 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.