Redshift COALESCE Function

The Redshift COALESCE function returns the first NOT NULL expression from a list. The COALESCE function starts reading a list, and when the first NOT NULL value is found, the function will not read the remaining and return the first NOT NULL value. If all values of the list are NULL, the COALESCE function will return the NULL value. The COALESCE function is also called NULL handling function NVL. It is used to handle the NULL values in the list.

Syntax of the COALESCE function

The syntax of COALESCE function is given below:

COALESCE ( expression 1, expression 2, ... ,expression N)

NVL ( expression 1, expression 2, ... ,expression N)

There is no limit to value that you can input. The COALESCE function works like an if-else condition. It will start searching from left to right until it finds the NOT NULL expression. It means it will start its search from the first expression. If it is NULL, it will move to the second expression to check the expression and so on until it get the NOT NULL expression.

The COALESCE function is independent of data type, which means you can use COALESCE function on every data type expression. But all expressions should be of the same type. For example, the COALESCE function does not allow expression 1 of numerical data type and expression 2 of character data type. So, while using the COALESCE function, ensure all the input expressions should be of the same data type.

Examples of COALESCE Function

This section will use different examples to explain how the COALESCE or NVL function can be used in different scenarios. We will use the following examples to understand the COALESCE function:

Using the COALESCE function on multiple values
Using the COALESCE function on a single value
Using the NVL function
Using the COALESCE function with tables

Using the COALESCE Function on Multiple Values

In the first example, we will try to understand the working of COALESCE function by applying it to multiple values.

Use the following query to implement the COALESCE function:

select Coalesce(NULL, NULL, NULL, 56, NULL, 100)

The previous query will generate the following output:

56

(1 row)

The COALESCE function starts its search by checking the first expression, which is NULL. It moves to the second expression, which is also NULL. It moves to the third expression, which also has a NULL value. Then, it will move to the fourth expression, which is not a null value, and return the fourth expression, i.e., 56.

In this example, we will apply the COALESCE function on a varchar data type to show that the COALESCE function is independent of data types. Use the following query to apply the COALESCE function on varchar data type parameters.

select coalesce(NULL, NULL, 'CocaCola', NULL, 'Pepsi');

It will generate the following output:

CocaCola

(1 row)

So, this example proves that the COALESCE function is independent of the data types of the parameters passed. The COALESCE function starts searching for the NOT NULL expression, so CocaCola is the first NOT NULL expression. The COALESCE function returns the CocaCola, and the remaining expressions are unevaluated.

Using the COALESCE Function on Single Value

In this example, we will use the COALESCE function on a single expression. Use the following query to implement the COALESCE function on a single expression:

select coalesce(56);

The result of the previous query will be as follows:

56

(1 row)

The Coalesce function starts its search, and the first expression is NOT NULL so that it will return that expression, i.e., 56.

Now, we apply the COALESCE function on a single expression, and its value is NULL.

select coalesce(NULL);

It will return an empty response because there is only one NULL expression and the COALESCE function only returns the NOT NULL expression.

Using the NVL Function

In this example, we will apply the NVL function instead of the COALESCE function to return the first NOT NULL value.

Use the following query to apply the NVL function instead of the COALESCE function:

select NVL(NULL, NULL, 100, NULL, 1000);

It will return the following response.

100

(1 row)

This example shows that NVL works exactly the same as the COALESCE function. In short, we can say that NVL is synonymous with the COALESCE function.

Using the COALESCE Function With Tables

This section will use multiple examples to implement the COALESCE function with tables. First, we will create a table and insert data into it. Then we will use the COALESCE function on this table.

Use the following query to create the AWS_Redshift_coalesce table in the Redshift cluster:

create table AWS_Redshift_coalesce(first_value int,Second_value int);

Now, insert some data into this table, including some NULL values

insert into AWS_Redshift_coalesce values ('240',null);

insert into AWS_Redshift_coalesce values (null,'360');

insert into AWS_Redshift_coalesce values ('480',null);

insert into AWS_Redshift_coalesce values (null,'720');

insert into AWS_Redshift_coalesce values (null,'1080');

After inserting the data, apply the COALESCE function to it by using the following query:

select coalesce(first_value, Second_value)

from aws_redshift_coalesce

order by 1;

It will generate the following output:

240

360

480

720

1080

(5 rows)

The COALESCE function starts checking the first column. In the first column, 240, 480, and 1080 are the NOT NULL values. So, the COALESCE function returns these values and then checks the second column where it found 360 and 720 as NOT NULL values.

In the following example, we will use the same table to understand the concept of the COALESCE function on a single column.

select coalesce(first_value)

from aws_redshift_coalesce

order by 1;

This query, when executed, will generate the following output.

240

480

(2 rows)

In the first_column, only 240 and 480 are NOT NULL values. So, the COALESCE function will return only these two values.

If we apply the COALESCE function on the second column of the table.

select coalesce(first_value)

from aws_redshift_coalesce

order by 1;

This query will generate the following result when executed:

360

720

1080

(3 rows)

In the second column, 360, 720, and 1080 are the NOT NULL values. So, the COALESCE function will return only these three values.

Conclusion

In this article, we studied how to use the COALESCE function to get the first NOT NULL expression. The COALESCE function is very handy to find the NOT NULL value from the data. If all the column values are NULL, the COALESCE function will return the empty result. It should be the same data type expression in the single column. When the COALESCE function finds out the NOT NULL expression, it stops executions, and the remaining expressions remain unchecked.