How to Handle NULL in Redshift? - Functions

A NULL value in a relational database is a special marker in SQL that indicates a data value does not exist in the database table. In other words, it is just a placeholder to denote values that are missing or that we do not know. Almost all relational databases and bigdata frameworks support functions to handle null values. In this article, we will check how to handle NULL in Amazon AWS Redshift, we shall also check Redshift NULL handling functions, usage and some examples.

Handle NULL in Amazon Redshift

Similar to other cloud databases such as Snowflake, Redshift supports many useful nulls handling functions. A Null functions a commonly used in reporting queries where you need to replace a NULL value with a readable value. For example, you can use NULL functions to display zero in place of NULL on reports.

You can use the following list of functions to handle null value in Amazon Redshift.

Redshift NVL and NVL2 Functions
NULLIF Function in Redshift
COALESCE Function in Redshift

Now, let us check these null handling functions 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.

For examples, consider following NVL and NV2 examples,

select amt, NVL(amt::int, 0) as value 
from (select NULL as amt) a; 
 amt   | value 
-------+------- 
 | 0 
(1 row)

select amt,curr, NVL2(amt::numeric(9,3), 0.0,1.0) as value, NVL2(curr::int,1,0) as curr1 from (select NULL as amt,1 as curr) a; 
 amt   | curr | value | curr1 
-------+------+-------+------- 
 | 1 | 1.0 | 1 
(1 row)

NULLIF Function in Redshift

The NULLIF function compares two arguments and returns null if the arguments are equal. If they are not equal, the first argument is returned.

Following is the syntax of NULLIF expression

NULLIF ( expression1, expression2 )

And following are the some of examples

SELECT NULLIF('a', 'a');
 case
------

NULL
(1 row)

SELECT NULLIF('A', 'a') as Col1;
 col1
------
 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 in Redshift.

COALESCE ( expression1, expression2, ... )

And following are the some of examples.

SELECT COALESCE('a', NULL) as col1;
   col1
----------
 a
(1 row)

SELECT COALESCE(NULL, 'A') as col1;
  col1
----------
 A
(1 row)

NULLIF as Teradata NULLIFZERO Alternative in Redshift

A NULLIFZERO function in Teradata return NULL if the value is zero to avoid problems with division by zero when working with mathematical calculations.

Following is the Teradata NULLIFZERO example

SELECT NULLIFZERO(0);

NullIfZero(0)
-------------
NULL

There 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,

SELECT NULLIF(0, 0) as col1;
 col1
------

NULL
(1 row)