Null values are invalid, unspecified, unknown, or unpredictable values in database tables. The occurrences of null values are not restricted by the
NOT NULL or
PRIMARY KEY constraint. The result of each arithmetic expression that contains
ApsaraDB for OceanBase supports the following three types of null values:
Null values in SQL functions
The null values of this type are the null values of the parameters in SQL functions. When the parameters of SQL functions have null values, most scalar functions return
NULL and analytic functions ignore null values. Null values in SQL functions are divided into two types. The following table describes the two types.
Null values in NVL functions
Null values in analytic functions
When an analytic function such as
Null values in comparison conditions
The null values of this type are the
NULL values that are found in comparison conditions and used for comparison. Only
IS NULL and
IS NOT NULL comparators can be used to test for null values.
NULL is incomparable to other values because it indicates that data is missing. This means that
NULL cannot be equal to, unequal to, greater than, or smaller than another numeric value or another null value.
Null values in conditional expressions
Null values in conditional expressions are the NULL values in the
! = NULL,
NULL =, and
NULL ! = conditions. These
NULL values are used for logical evaluation. If conditions evaluate to
UNKNOWN, no rows are returned.