NULL

From Oracle FAQ
Jump to: navigation, search

NULL is a marker that represents missing, unknown, or inapplicable data.

Null is untyped in SQL, meaning that it is not designated as a NUMBER, CHAR, or any other specific data type. Do not use NULL to represent a value of zero, because they are not equivalent.

Contents

[edit] 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);

[edit] 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.

[edit] Some invalid examples

A NULL is not equal to a NULL:

SELECT * FROM emp WHERE NULL = NULL;

A NULL cannot be "not equal" to a NULL either:

SELECT * FROM emp WHERE NULL <> NULL;

A NULL does not equal an empty string either:

SELECT * FROM emp WHERE NULL = ''; 

[edit] Valid examples

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;

[edit] 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;

[edit] Also see

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 #