COALESCE in Teradata with example

COALESCE in Teradata is used for NULL handling. The COALESCE returns the first NOT NULL value encountered in the provided list. Otherwise it returns NULL if all the arguments in the expression list evaluate to NULL. 

COALESCE Function Syntax 

The basic syntax of COALESCE function is as below-

COALESCE(expression1, expression2 [,expression list])

COALESCE Function Example 

Consider the below employee table.

Now, you can prioritize which phone number to select using COALESCE function as below.

SELECT
 emp_no,
 first_name,
 last_name,
 COALESCE(ph_no1,home_no,ph_no2,'Phone# not available') AS Phone_No
 FROM teradatapoint.employee;

The above query will search for ph_no1 first. If that is NULL , it will search for home_no and ph_no2 respectively. If none of the argument is returning not null value, it will return the default value.

*** Query completed. 3 rows found. 4 columns returned.
  *** Total elapsed time was 1 second.

 

     emp_no  first_name  last_name   Phone_No
 -----------  ----------  ----------  --------------------
         345  Amal        Roy         Phone# not available
         123  Alex        Martin                7209756747
         134  Sager       Sharma                  34567548

From the above example, you can see that Employee Amal has not provided any phone number so COALESCE statement returned NULL i.e. Phone# not available. In case of other employee it returns first not null value.