NULL indicates an invalid, unspecified, unknown, or unpredictable value in a database table. NULLs are allowed in columns whose data types are not subject to the NOT NULL or PRIMARY KEY constraints. Arithmetic expressions that contain a NULL always return NULL.
OceanBase Database supports the following types of NULLs: NULLs in SQL functions, NULLs in comparison conditions, and NULLs in conditional expressions.
NULLs in SQL functions
A NULL in an SQL function means that the argument of the SQL function is NULL. Given a NULL argument, most scalar functions return NULL, while aggregate functions (except for COUNT() and GROUPING()) ignore the NULL.
| NULL | Description |
|---|---|
| NULLs in scalar functions | When NULLs appear, you can determine the NULLs by using the return value of the NVL() function. |
| NULLs in aggregate functions | Aggregate functions such as AVG(), MAX(), and SUM() ignore NULL records. |
NULLs in comparison conditions
NULLs in comparison conditions are compared with any other conditions. To check for NULLs, you can only use 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.
NULLs in conditional expressions
NULLs may also appear in conditional expressions such as = NULL, != NULL, NULL =, and NULL ! =. In this case, NULLs are used for logic judgment and no row is returned as the judgment result. Therefore, the result is UNKNOWN.