The NULL values are ‘unknown’ or unavailable values. You cannot treat these values like how you treat blank values. The data generation system may add NULL values if values are missing or unknown value. Vertica provides various NULL functions using those you can tell the database how to treat NULL values. In this article, we will check Vertica NULL handling functions usage with some examples.
Vertica NULL Handling Functions
As mentioned earlier, you can tell Vertica database on how to treat NULL values using NULL handling functions.
Below is the list of various Vertica NULL handling functions with descriptions:
| Condition | Description |
| ISNULL (expr1, expr2) | Returns the value of the first non-null expression in the list. It is an alias of Vertica NVL function. |
| IFNULL (expr1, expr2) | Returns the value of the first non-null expression in the list. It is an alias of Vertica NVL function. |
| NULLIF (expr1, expr2) | Compares two values. Returns NULL if equal otherwise returns first expression. |
| COALESCE (expr1, expr2 …) | Returns the value of the first non-null expression in the list. |
| NVL (expr1, expr2) | Returns the value of the first non-null expression in the list. Read Vertica NVL Function. |
| NVL2 (expr1, expr2, expr2) | If the first argument is not NULL, it returns the second argument, otherwise it returns the third argument. Read Vertica NVL2 Function. |
| NULLIFZERO (expr) | Evaluates to NULL if the value in the column or expression is zero. |
| ZEROIFNULL (expr) | Evaluates to zero if the value in the column or expression is NULL. |
Related Readings:
Vertica NULL Handling Functions Examples
Below are examples on usage of above mentioned functions.
Vertica ISNULL Function Example
SELECT ISNULL(NULL, 111);Vertica IFNULL Function Example
SELECT IFNULL(NULL, 111);Vertica NULLIF Function Example
SELECT NULLIF(111, 111);
SELECT NULLIF(111, NULL);Vertica COALESCE Function Example
SELECT COALESCE(NULL, 111, 777, NULL);Vertica NVL and NVL2 Functions Example
You can read about NVL and NVL2 functions in my other article: Vertica NVL and NVL2 Functions
Vertica NULLIFZERO Function Example
SELECT NULLIFZERO(0);Vertica ZEROIFNULL Function Example
SELECT ZEROIFNULL(col1)
FROM (SELECT NULL AS col1) AS ds; Hope this helps ?