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.