A NULL value in a relational database is a special marker used in SQL to indicate that a data value does not exist in the database. In other words, it is just a placeholder to denote values that are missing or that we do not know. Almost all relational databases support functions to handle nulls. In this article, we will check Redshift NULL handling functions, usage and some examples
Redshift NULL Handling Functions
A NULL functions are used to handle NULL values that you may receive as a part of the data. For example, you can use NULL functions to display zero in place of NULL on reports.
Following is the list of Redshift NULL handling functions.
Now, let us check these function in brief.
Redshift NVL and NVL2 Functions
An NVL expression returns the value of the first expression in the list that is not null. And Redshift NVL2 Function returns the second argument if the first argument is not null, otherwise it returns the third argument.
You can read more about these two function in my other post:
NULLIF Function in Redshift
The NULLIF expression or function compares two arguments and returns null if the arguments are equal. If they are not equal, the first argument is returned.
The NULLIF function is the inverse of the NVL or COALESCE.
Following is the syntax of NULLIF expression
NULLIF ( expression1, expression2 )And following are the some of examples
dev=# SELECT NULLIF('a', 'a');
case
------
NULL
(1 row)
dev=# SELECT NULLIF('A', 'a');
case
------
A
(1 row)COALESCE Function in Redshift
The COALESCE function in an Amazon Redshift is a synonym of the NVL expression.
following is the syntax of COALESCE function.
COALESCE ( expression1, expression2, ... )And following are the some of examples.
dev=# SELECT COALESCE('a', NULL);
coalesce
----------
a
(1 row)
dev=# SELECT COALESCE(NULL, 'A');
coalesce
----------
A
(1 row)NULLIF as Teradata NULLIFZERO Alternative in Redshift
The NULLIFZERO function in Teradata converts data from zero to null to avoid problems with division by zero.
Following is the Teradata NULLIFZERO example
SELECT NULLIFZERO(0);
NullIfZero(0)
-------------
NULLThere are many Teradata functions that are not available in Redshift. On of such a function is NULLIFZERO.
You can use a NULLIF function as a NULLIFZERO alternative in Redshift.
For example,
dev=# SELECT NULLIF(0, 0);
case
------
NULL
(1 row)Related Articles,
Hope this helps 🙂