NULL
NULL is a marker that represents missing, unknown, or inapplicable data.
A column or variable of any datatype can be NULL because NULL is not of NUMBER, CHAR, or any other specific data type. Do not use NULL to represent a value of zero, because they are not equivalent.
NOT NULL constraint
Columns in a table can be defined as NOT NULL to indicate that they may not contain NULL values (a value must be entered). Example:
CREATE TABLE t1 (c1 NUMBER PRIMARY KEY, c2 DATE NOT NULL);
Comparisons
Any arithmetic expression containing a NULL always evaluates to NULL. For example, 10 + NULL = NULL. In fact, all operators (except concatenation and the DECODE function) return null when given a null operand.
Some invalid examples
Nothing is ever equal to a NULL not even NULL ("anything = NULL" evaluates as UNKNOWN):
SELECT * FROM emp WHERE NULL = NULL;
returns no rows.
Nothing can be "not equal" to a NULL either ("anything <> NULL" evaluates as UNKNOWN):
SELECT * FROM emp WHERE comm <> NULL;
returns no rows even if some rows contains a not NULL value in its COMM column.
An empty string literal is evaluated in the same way as NULL literal ("anything = " evaluates as UNKNOWN):
SELECT * FROM dept WHERE loc = '';
returns no rows even if some rows have an empty string (or NULL) in its LOC column.
Valid examples
The correct way to test a NULL value is to use the IS NULL or IS NOT NULL test.
Select column values that are NULL:
SELECT * FROM emp WHERE comm IS NULL;
Select column values that are NOT NULL:
SELECT * FROM emp WHERE comm IS NOT NULL;
Change a column value to NULL:
UPDATE emp SET comm = NULL WHERE deptno = 20;
Sorting
In ascending order, NULL values will always be sorted last and thus appear after the other data. In descending order NULL values will appear first. The sort order of NULL values can be overridden using the NULLS FIRST/LAST clause.
Examples:
select * from emp order by sal desc NULLS FIRST;
select * from emp order by sal desc NULLS LAST;
Also see
- Constraint
- NVL, Null value function
| Glossary of Terms | ||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | # |
