Oracle SQLPlus [Electronic resources] : The Definitive Guide, 2nd Edition نسخه متنی

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

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.

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?

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.

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.