What is coalesce in teradata ? Everything you need to know

Teradata COALESCE function is used for NULL Handling. It returns the first not null value which is passed to it. If all the values passed to COALESCE function are NULL then output would be NULL else output would be first NOT NULL value encountered.

Teradata Coalesce Date

You can use coalesce to convert NULL values in Date columns to some default values too. Make sure the value passed to coalesce is of DATE FORMAT else you may get the error. In the example below we have "dob" (date of birth) column as date in the table. We can pass a default value to convert all the NULL values in this column.

SELECT
	EMPID,
	NAME,
	COALESCE(YEARS_OF_EXP,-1) AS YEARS_OF_EXP,
	SALARY,
	coalesce(dob,date '1900-01-01') as dob
FROM
	TST_CASE
ORDER BY
	EMPID;

Teradata Coalesce Multiple Values

You can also pass multiple values to coalesce function and it will return the first NOT NULL value. Mostly it is used in the business use case when we have multiple columns and we can have value in anyone of them.

SELECT
	EMPID,
	coalesce(name,first_name,last_name,'Not Available') as emp_name
	YEARS_OF_EXP,
	SALARY
FROM
	TST_CASE
ORDER BY
	EMPID;

In the example above, for emp_name the logic checks for "name" column first. If it is NULL then it will check for "first_name" column. If it is NULL too then it will check for "last_name" column. And if that is NULL too then coalesce function will return 'Not Available' as default value for emp_name.

Teradata Coalesce in JOIN

You can also use coalesce in join conditions. The idea is when you have NULL values in joining column then it does not participate in JOINs. If business requirement is to get such rows in output as well then you can apply coalesce on joining column to some default value.

SELECT
	CUST.CUST_ID,
	CUST.CUST_NAME,
	CUST.CUST_CITY,
	OZON.STATE,
	CUST.CUST_OPERATOR
FROM
	CUSTOMERS CUST
INNER JOIN OPRT_ZONE OZON ON
	coalesce(CUST.CUST_CITY,'NotAvailable') = coalesce(OZON.CITY,'NotAvailable');

In the above example, we have used coalesce on joining column. If the value is NULL then it will be defaulted to 'NotAvailable' as default value and thus will participate in JOIN. Hence will get in the output too.

We will create volatile table in this example and run queries to test TERADATA SQL COALESCE expression

CREATE VOLATILE TABLE TST_CASE
(
EMPID INTEGER,
NAME VARCHAR(30),
YEARS_OF_EXP SMALLINT,
SALARY INTEGER
)
PRIMARY INDEX (EMPID)
ON COMMIT PRESERVE ROWS;

INSERT INTO TST_CASE VALUES(1,'WILLIAM',5,50000);
INSERT INTO TST_CASE VALUES(2,'SARA',3,30000);
INSERT INTO TST_CASE VALUES(3,'ROBIN',NULL,40000);
INSERT INTO TST_CASE VALUES(4,'TED',7,70000);
INSERT INTO TST_CASE VALUES(5,'LILY',NULL,20000);

Preview the data in the table.

SELECT * FROM TST_CASE;

Let's apply coalesce to the column YEARS_OF_EXP and if it is NULL then return "-1" else keep the value as-is in the output.

SELECT
	EMPID,
	NAME,
	COALESCE(YEARS_OF_EXP,-1) AS YEARS_OF_EXP,
	SALARY
FROM
	TST_CASE
ORDER BY
	EMPID;

COALESCE can be replaced by CASE statement in which you check the value is NULL then default value else keep the column as the output.