COALESCE DB2 Function in SQL Queries

COALESCE DB2 function is used to handle the NULL values that might be present in the data or a list of values that are specified. If the NOT NULL constraint on the column is not applied, then the default value that gets inserted in those columns when not specified is the NULL value. It returns the first non-null value in a list of input expressions. This function takes a comma-separated list of arguments that are evaluated in the order in which they are specified and returns the first non-NULL value found. All input expressions must be compatible. VALUE is a synonym for this function. If all arguments are NULL, the COALESCE() function returns NULL. NULL can be handled by use of Null indicator or by using Scalar function – VALUE and COALESCE.

Syntax

COALESCE(epression1,expression2)

--NUMERIC EXAMPLE--
SELECT COALESCE({field1}, {default_value}) FROM{table_name};

--CHARACTER EXAMPLE--
SELECT COALESCE({field1}, '{default_value}') FROM{table_name};

Example:
 Input:
 ID NAME     DEPT  JOB    YEARS     SALARY    COMM
 -- ----     ----  ----   -------   -----     ------ 
 10 Sanders  20    Mgr    7         18357.50  -
 20 Pernal   20    Sales  8         18171.25  612.45
 30 Marenghi 38    Mgr    5         17506.75  -
 40 O’Brien  38    Sales  6         18006.00  846.55

 
 SELECT ID 
       ,COMM 
       ,COALESCE(COMM,0) 
 FROM STAFF 
 WHERE ID < 30 
 ORDER BY ID;


 Answer:
 ID    COMM       3
 10    -          0.00
 20    612.45     612.45

COALESCE DB2 function in CASE statement

This function can also handle a subset of the functions provided by CASE expressions. The result of using COALESCE(e1,e2) is the same as using the expression:

   CASE WHEN e1 IS NOT NULL THEN e1 ELSE e2 END

A CASE expression can be written to do exactly the same thing as the COALESCE function. The following SQL statement shows two logically equivalent ways to replace nulls:

 WITH TEMP1(C1,C2,C3) AS 
 (VALUES (CAST(NULL AS SMALLINT) 
 ,CAST(NULL AS SMALLINT) 
 ,CAST(10 AS SMALLINT))) 
 

 SELECT COALESCE(C1,C2,C3) AS CC1 
 ,CASE
 WHEN C1 IS NOT NULL THEN C1
 WHEN C2 IS NOT NULL THEN C2
 WHEN C3 IS NOT NULL THEN C3
 END AS CC2
 FROM TEMP1;
 
 Answer:
 CC1 CC2
 10  10

Please note that a field can return a null value, even when it is defined as not null. This occurs if a column function is applied against the field, and no row is returned:

 SELECT COUNT(*) AS #ROWS 
 ,MIN(ID) AS MIN_ID 
 ,COALESCE(MIN(ID),-1) AS CCC_ID 
 FROM STAFF 
 WHERE ID < 5; 

 Answer:
 ROWS MIN_ID CCC_ID
 0      -     -1

COALESCE DB2 function in Join Queries

In an outer join, fields in non-matching rows are given null values as placeholders. These nulls can be eliminated using the COALESCE function. The COALESCE function can be used to combine multiple fields into one, and/or to eliminate null values where they occur. The result of the COALESCE is always the first non-null value encountered. In the following example, the two ID fields are combined, and any null NAME values are replaced with a question mark.

Note: You can use COALESCE function to convert the null to zero or some other value. The COALESCE function cannot be used as a source function when creating a user-defined function. Because this function accepts any compatible data types as arguments, it is not necessary to create additional signatures to support user-defined data types.

Example:
 Input:
 STAFF_V1
 ID|NAME
 10|Sanders
 20|Pernal
 30|Marenghi
 
 STAFF_V2
 ID|JOB
 20|Sales
 30|Clerk
 30|Mgr
 40|Sales
 50|Mgr
 

 SELECT COALESCE(V1.ID,V2.ID) AS ID 
 ,COALESCE(V1.NAME,’?’) AS NAME 
 ,V2.JOB 
 FROM STAFF_V1 V1 
 FULL OUTER JOIN 
 STAFF_V2 V2 
 ON V1.ID = V2.ID 
 ORDER BY V1.ID 
 ,V2.JOB;
 
 
 Answer:
 ID NAME     JOB
 10 Sanders  –
 20 Pernal   Sales
 30 Marenghi Clerk
 30 Marenghi Mgr
 40 ?        Sales
 50 ?        Mgr

COALESCE Examples

Example 1: When selecting all the values from all the rows in the DEPARTMENT table, if the department manager (MGRNO) is missing (that is, null), then return a value of ‘ABSENT’.

SELECT DEPTNO, DEPTNAME, COALESCE(MGRNO, 'ABSENT'), ADMRDEPT
FROM DEPARTMENT

Example 2: When selecting the employee number (EMPNO) and salary (SALARY) from all the rows in the EMPLOYEE table, if the salary is missing (that is, null), then return a value of zero.

SELECT EMPNO, COALESCE(SALARY, 0)
FROM EMPLOYEE

Example 3: In the following COALESCE statement, if the value of c1 is:

5, the statement returns a value of 5

NULL, the statement returns a value of 10

‘AB’, the statement returns an error, because the data types of the two expressions are incompatible

COALESCE(c1,10)

Example 4: Assume that SCORE1 and SCORE2 are SMALLINT columns in table GRADES and that nulls are allowed in SCORE1 but not in SCORE2. Select all the rows in GRADES for which SCORE1 + SCORE2 > 100, assuming a value of 0 for SCORE1 when SCORE1 is null.

SELECT * FROM GRADES
WHERE COALESCE(SCORE1,0) + SCORE2 > 100;

Example 5: Assume that a table named DSN8A10.EMP contains a DATE column named HIREDATE, and that nulls are allowed for this column. The following query selects all rows in DSN8A10.EMP for which the date in HIREDATE is either unknown (null) or earlier than 1 January 1960.

SELECT * FROM DSN8A10.EMP
WHERE COALESCE(HIREDATE,DATE('1959-12-31')) < '1960-01-01';

The predicate could also be coded as COALESCE(HIREDATE,’1959-12-31′) because, for comparison purposes, a string representation of a date can be compared to a date.

Example 6: Assume that for the years 1993 and 1994 there is a table that records the sales results of each department. Each table, S1993, and S1994, consists of a DEPTNO column and a SALES column, neither of which can be null. The following query provides the sales information for both years.

SELECT COALESCE(S1993.DEPTNO,S1994.DEPTNO) AS DEPT,   
       S1993.SALES, S1994.SALES
FROM S1993 FULL JOIN S1994 ON S1993.DEPTNO = S1994.DEPTNO
ORDER BY DEPT;

The full outer join ensures that the results include all departments, regardless of whether they had sales or existed in both years. The COALESCE function allows the two join columns to be combined into a single column, which enables the results to be ordered.

Example 7:COALESCE function to replace the NULL value

ORDER_ID	ORDER_DATE	ORDER_DESCRIPTION	INVOICE_ID
123456789	2020-09-01	NULL	W10001

Since COBOL cannot handle NULL values, we can use the below query which can replace the NULL value as an “NA” string. Please note that this will work only if the ORDER_DESCRIPTION column has NULL value else the original value of ORDER_DESCRIPTION will be fetched.

EXEC SQL
      SELECT INVOICE_ID, COALESCE(ORDER_DESCRIPTION, ‘NA’)
         INTO :INVOICE_ID,
              :ORDER_DESCRIPTION
   FROM ORDERS
   WHERE ORDER_ID = ‘123456789’
END-EXEC

COALESCE Function with NULL Values

The COALESCE function returns the first non-NULL value it finds in the supplied parameters.

COALESCE (expression1, expression2, expression3…..)

EXAMPLE

Let’s take an example to find if any of the Personal, Work, or Alternative emails are present for an employee.

EMP_ID NAME WORK_EMAIL         PERSONAL_EMAIL        ALT_EMAIL
1 Jane Jane.D@outlook.com jane.doe@yahoo.com    j.doe@gmail.com
1 John (null)      john.kurian@gmail.com (null)
1 Sam  (null)             (null)                sam.huges@yahoo.com

SELECT
COALESCE(WORK_EMAIL, PERSONAL_EMAIL, ALT_EMAIL)
FROM EMPLOYEE;

This query would return personal_email, but if that is NULL it would return work_email, and if that is NULL then alternative_email is returned.

Result
Jane.D@outlook.com
john.kurian@gmail.com
sam.huges@yahoo.com

DB2 Blog: Click Here IBM DB2 Manual: Click Here