Oracle7 Server SQL Reference
Nulls
If a column in a row has no value, then column is said to be null, or to contain a null. Nulls can appear in columns of any datatype that are not restricted by NOT NULL or PRIMARY KEY integrity constraints. Use a null when the actual value is not known or when a value would not be meaningful.
Oracle7 currently treats a character value with a length of zero as null. However, this may not continue to be true in future versions of Oracle7.
Do not use null to represent a value of zero, because they are not equivalent. Any arithmetic expression containing a null always evaluates to null. For example, null added to 10 is null. In fact, all operators (except concatenation) return null when given a null operand.
Nulls in SQL Functions
All scalar functions (except NVL and TRANSLATE) return null when given a null argument. The NVL function can be used to return a value when a null occurs. For example, the expression NVL(COMM,0) returns 0 if COMM is null or the value of COMM if it is not null.
Most group functions ignore nulls. For example, consider a query that averages the five values 1000, null, null, null, and 2000. Such a query ignores the nulls and calculates the average to be (1000+2000)/2 = 1500.
Nulls with Comparison Operators
To test for nulls, only use the comparison operators IS NULL and IS NOT NULL. If you use any other operator with nulls and the result depends on the value of the null, the result is UNKNOWN. Because null represents a lack of data, a null cannot be equal or unequal to any value or to another null. However, note that Oracle7 considers two nulls to be equal when evaluating a DECODE expression. For information on the DECODE syntax, see the section "Expr" ![[*]](jump.gif)
.
Nulls in Conditions
A condition that evaluates to UNKNOWN acts almost like FALSE. For example, a SELECT statement with a condition in the WHERE clause that evaluates to UNKNOWN will return no rows. However, a condition evaluating to UNKNOWN differs from FALSE in that further operations on an UNKNOWN condition evaluation will evaluate to UNKNOWN. Thus, NOT FALSE evaluates to TRUE, but NOT UNKNOWN evaluates to UNKNOWN.
Table 2 - 8 shows examples of various evaluations involving nulls in conditions. If the conditions evaluating to UNKNOWN were used in a WHERE clause of a SELECT statement, then no rows would be returned for that query.
If A is:
Condition
Evaluates to:
10
a IS NULL
FALSE
10
a IS NOT NULL
TRUE
NULL
a IS NULL
TRUE
NULL
a IS NOT NULL
FALSE
10
a = NULL
UNKNOWN
10
a != NULL
UNKNOWN
NULL
a = NULL
UNKNOWN
NULL
a != NULL
UNKNOWN
NULL
a = 10
UNKNOWN
NULL
a != 10
UNKNOWN
Table 2 - 8. Conditions Containing Nulls
For the truth tables showing the results of logical expressions containing nulls, see Table 3 - 6, Table 3 - 7, and Table 3 - 8.