Nulls in comparison conditions are compared with other conditions.
To test for null values, you can use only the IS NULL and IS NOT NULL operators. Because NULL means a lack of data, a NULL value cannot be equal to, unequal to, greater than, or less than any other value. It cannot be compared with any values, including itself.
OceanBase Database considers two nulls to be equal when it evaluates a DECODE function. Two nulls are also considered equal if they appear in compound keys. This means that OceanBase Database considers identical two compound keys containing nulls if all the nonnull fields of the keys are equal.
The following table lists results of the comparison conditions based on the value 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 |