SQL/MX 2.x Reference Manual (H06.04+)

SQL/MX Language Elements
HP NonStop SQL/MX Reference Manual—540440-003
6-76
Null
Null
Null is a special symbol, independent of data type, that represents an unknown. The
SQL/MX keyword NULL represents null. Null indicates that an item has no value. For
sorting purposes, null is greater than all other values. You cannot store null in a column
by using either INSERT or UPDATE, unless the column allows null.
A column that allows null can be null at any row position. A nullable column has extra
bytes associated with it in each row. A special value stored in these bytes indicates
that the column has null for that row.
Consider these guidelines:
•
Using Null Versus Default Values on page 6-76
•
Defining Columns That Allow or Prohibit Null on page 6-77
•
Determining Whether a Column Allows Null on page 6-77
•
Null in DISTINCT, GROUP BY, and ORDER BY Clauses on page 6-78
•
Null and Expression Evaluation Comparison on page 6-78
Using Null Versus Default Values
There are various scenarios in which a row in a table might contain no value for a
specific column. For example:
•
A database of telemarketing contacts might have null AGE fields if contacts did not
provide their age.
•
An order record might have a DATE_SHIPPED column empty until the order is
actually shipped.
•
An employee record for an international employee might not have a social security
number.
You allow null in a column when you want to convey that a value in the column is either
unknown (such as the age of a telemarketing contact) or not applicable (such as the
social security number of an international employee).
In deciding whether to allow nulls or use defaults, also note:
•
Nulls are not the same as blanks. Two blanks can be compared and found equal,
while the result of a comparison of two nulls is indeterminate.
•
Nulls are not the same as zeros. Zeros can participate in arithmetic operations,
while nulls are excluded from any arithmetic operation.