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.45COALESCE 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 [email protected] [email protected] [email protected] 1 John (null) [email protected] (null) 1 Sam (null) (null) [email protected]
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 [email protected] [email protected] [email protected]