An empty value (NULL) is an invalid, unspecified, unknown, or unpredictable value in a database table. An empty value can exist in a column that is not constrained by the NOT NULL or PRIMARY KEY integrity constraints. The result of any arithmetic expression that contains an empty value is also an empty value.
OceanBase Database supports three types of empty values: empty values in SQL functions, empty values in comparison conditions, and empty values in conditional expressions.
Empty values in SQL functions
An empty value in an SQL function refers to an empty value in the function's parameter. When an SQL function's parameter is an empty value, most scalar functions return NULL, and aggregate functions (excluding COUNT() and GROUPING()) ignore empty values.
When you use ORDER BY to sort data, NULL values are placed after other values in ascending order and before other values in descending order.
Empty value |
Description |
|---|---|
| Empty value in scalar functions | When an empty value appears, you can use the NVL() function to return a value to determine the empty value. |
| Empty value in aggregate functions | When you use aggregate functions such as AVG, MAX, and SUM, records with empty values are ignored. |
Empty values in comparison conditions
An empty value in a comparison condition refers to a NULL value that is compared with any other condition. You can only use the IS NULL and IS NOT NULL operators to test for empty values. Since NULL indicates missing data, it cannot be compared with any other value, including itself. Therefore, you cannot use equality, inequality, greater than, or less than operators to compare NULL with other values.
Empty values in conditional expressions
An empty value in a conditional expression refers to a NULL value in conditions such as = NULL, != NULL, NULL =, and NULL !=. These conditions are used for logical judgment. The result of such a condition is UNKNOWN, and no rows are returned.