All Products
Search
Document Center

Null value overview

Last Updated: Jun 18, 2021

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 NULL is NULL.

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 value

Description

Null values in NVL functions

If expr1 in the NVL(expr1,expr2) expression is not NULL, the expression returns expr1. Otherwise, the expression returns expr2.

Null values in analytic functions

When an analytic function such as AVG, MAX, SUM, or COUNT is used, NULL records are ignored.

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, NULL =, and NULL ! = conditions. These NULL values are used for logical evaluation. If conditions evaluate to UNKNOWN, no rows are returned.