All Products
Search
Document Center

Null values in conditional expressions

Last Updated: Jun 18, 2021

Null values in conditional expressions are the NULL values in = NULL, ! = NULL, NULL =, and NULL ! = conditions. These NULL values are used for logical evaluation. If conditions evaluate to UNKNOWN, no rows are returned.

In ApsaraDB for OceanBase, use the IS NULL comparison operator to test for null values. This operator returns TRUE or FALSE. However, UNKNOWN that is returned for null values in conditional expressions is different from FALSE. NOT FALSE evaluates to TRUE, but NOT UNKNOWN still evaluates to UNKNOWN.

The following table lists the results that are returned for conditional expressions based on the A value.

Condition

A value

Result

A = NULL

10

UNKNOWN

A ! = NULL

10

UNKNOWN

A = NULL

NULL

UNKNOWN

A ! = NULL

NULL

UNKNOWN

A = 10

NULL

UNKNOWN

A ! = 10

NULL

UNKNOWN

No rows are returned if a condition that evaluates to UNKNOWN is used in the WHERE clause of a SELECT statement.