All Products
Search
Document Center

Null values in comparison conditions

Last Updated: Jun 18, 2021

Null values in comparison conditions are NULL compared to other conditions. To test for nulls, you can use only the comparison operators IS NULL and IS NOT NULL. NULL is not comparable to other values because it indicates a lack of data. That is, NULL cannot be compared to other values, including null values, by using "equal to", "not equal to", "greater than", or "smaller than".

In addition, ApsaraDB for OceanBase considers two null values to be equal when it performs calculations by using the DECODE function. Two null values are also considered to be equal if they appear in compound keys.

The following example shows that you judge the results of comparison conditions based on the values of A:

Condition

Value of A

Result

A IS NULL

10

FALSE

A IS NOT NULL

10

TRUE

A IS NULL

NULL

TRUE

A IS NOT NULL

NULL

FALSE