A NULL value in a comparison condition refers to a value that cannot be compared with any other value.
You can use only the IS NULL and IS NOT NULL operators to test for NULL values. This is because NULL indicates missing data, and therefore, a NULL value cannot be compared with any other value, including another NULL value.
In addition, OceanBase Database considers two NULL values to be equal when calculating the DECODE function. If two NULL values are part of a composite key, they are also considered equal. That is, OceanBase Database will consider using two composite keys that contain NULL values only if all non-NULL fields in the composite keys are equal.
The following table shows the results of comparison conditions based on the value of A.
| Condition | A Value | Result |
|---|---|---|
| A IS NULL | 10 | FALSE |
| A IS NOT NULL | 10 | TRUE |
| A IS NULL | NULL | TRUE |
| A IS NOT NULL | NULL | FALSE |