DB2- Use of ‘COALESCE’ Function (1 of 2)

The “COALESCE” function is one of the frequently used functions in DB2. Before going into detail, I just want to describe it in layman terms.

This function validates for NULL, and secondly, if NULL is there it assumes some value as we give in the function.

Syntax: COALESCE(epression1,expression2)

It is like decision maker. Its uses are many.


CASE WHEN expression1 IS NOT NULL THEN expression1 ELSE expression2 END.

In other way we can write:

CASE COALESCE(expression1,expression2) END.



If MARKS1 is null it assumes as zero ‘0’.


SELECT COALESCE(EMP.DEPT,ROLE.DEPT) AS DEPT. In this case neither of the DEPT of EMP and ROLE tables is NULL. In my next I will give more on this function.

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.

Comments are closed.