1.7 Null value

The null value is a special value that indicates either that no value exists or the value has not been set. The null value is set in an area that does not contain values or in which values have not been set. The null value in an abstract data type indicates whether a value has been generated by the constructor function.

The following explains how the null value is handled.

Organization of this section
(1) Receiving a column value as a result of a retrieval
(2) Storing a value in a table
(3) Comparison
(4) Join
(5) Sorting
(6) Grouping
(7) Exclusion of duplicates
(8) Set functions
(9) Window functions
(10) Indexing
(11) Arithmetic, date, time, and concatenation operations
(12) Scalar functions
(13) CASE expressions
(14) Abstract data type
(15) Boolean predicate
(16) Repetition column
(17) WRITE specification
(18) GET_JAVA_STORED_ROUTINE_SOURCE specification
(19) CAST specification
(20) Referential constraint

(1) Receiving a column value as a result of a retrieval

The value of an indicator variable indicates whether the null value was received. The null value cannot be received by an embedded variable. For details, see 1.6.5 Setting a value for an indicator variable.

(2) Storing a value in a table

The value of an indicator variable indicates whether the null value was stored. The null value cannot be stored in a table by an embedded variable. For details, see 1.6.5 Setting a value for an indicator variable.

(3) Comparison

If the value of a specified value expression, column, or embedded variable for a row in a predicate other than the NULL predicate is the null value, the predicate is undefined. An indicator variable is required to specify the null value using an embedded variable.

(4) Join

A row containing the null value in the joined column does not satisfy the join conditions.

(5) Sorting

In the case of an ascending-order sort, the null value is output at the end; in the case of a descending-order sort, the null value is output at the beginning.

(6) Grouping

If a row contains null values in grouping condition columns, SQL performs grouping by treating the null values as being the same value.

(7) Exclusion of duplicates

Multiple null values are treated as duplicates.

(8) Set functions

In general, set functions ignore the null value. The COUNT(*) function, however, calculates all eligible rows, regardless of null values that may be present in the rows.

(9) Window functions

The COUNT(*) and OVER() functions calculate all eligible rows, regardless of null values that may be present in the rows.

(10) Indexing

An index can be defined for a column that contains null values.

(11) Arithmetic, date, time, and concatenation operations

An arithmetic, date, time, or concatenation operation performed on the null value as a data value produces the null value.

(12) Scalar functions

Scalar functions other than VALUE and STRTONUM produce the null value as the result when any of the value expressions in an argument is the null value. The VALUE scalar function produces the null value as the result when all value expressions in an argument are the null value. The STRTONUM scalar function produces the null value as the result when the value expression for argument 1 is the null value.

(13) CASE expressions

In COALESCE of CASE abbreviation, if the value expressions of arguments are all null values, the results will also be null values.

(14) Abstract data type

How the null value in an abstract data type is handled is explained in terms of two cases: a value is generated by specifying a constructor function for the abstract data type, and a value is not generated by specifying a constructor function.

(15) Boolean predicate

A Boolean predicate being undefined is equivalent to a Boolean value being the null value.

(16) Repetition column

Some elements may have a null value. If all column elements are 0, the entire column is treated as null.

(17) WRITE specification

In the case of a WRITE specification, the result will be the null value if any of the arguments is the null value.

(18) GET_JAVA_STORED_ROUTINE_SOURCE specification

The result of a GET_JAVA_STORED_ROUTINE_SOURCE specification will be the null value if any of the following conditions is satisfied:

(19) CAST specification

If NULL is specified in the value expression or the result of the value expression is the null value, the value of the result is the null value.

(20) Referential constraint

If the null value is contained in a foreign key component column, that column is not subject to referential constraint operation.