Absence of a value in a record
In SQL, a record must include a value if a column is declared as not null. When a record contains no value for a column, the column is considered to be null. The system provides explicit and implicit methods for conveying nullness.
The
methods are as follows:
- The explicit method uses a specific token in the field instead of a value. By default, this token is the word “null” (not case sensitive). You can use the NullValue option to change this token to any other 1 - 4 character alphabetic token. You can precede or follow an occurrence of the explicit null token in a non-string field with adjacent spaces. For the system to recognize an explicit null token in a string field, the token cannot have preceding or trailing adjacent spaces. The explicit null token method makes it impossible to express a string that consists of exactly the text of the null token.
- The implicit
method interprets an empty field as null. This method is always available
to
non-string fields, independent of any NullValue option setting, and works even
if the non-string field contains spaces. You can use the implicit
method on string fields only if
you set the NullValue option to the empty string
('').
The system considers a string field empty (potentially null) only if it contains truly zero characters (no spaces). Setting the NullValue option to the empty string makes it impossible to set any character varying (varchar) column to an empty, zero-length string. If the system encounters an empty string and the NullValue option is set to '', the system treats the empty string as a null value.