NULL values in conditional expressions are used for logical judgments. The judgment result does not return any rows, i.e., UNKNOWN.
Conditional expressions include = NULL, != NULL, NULL =, and NULL !=.
In OceanBase Database, to test for NULL values, you must use the comparison operator IS NULL, which returns TRUE or FALSE. However, the judgment result of NULL values in conditional expressions, UNKNOWN, is different from FALSE. The judgment result of NOT FALSE is TRUE, while the judgment result of NOT UNKNOWN is still UNKNOWN.
The following table shows the results of conditional expressions based on the value of A.
| 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 |
Note
If a condition with a judgment result of UNKNOWN is used in the WHERE clause of a SELECT statement, the query will not return any rows.