The DB2 Coalesce  function returns the value of the first non-NULL expression. The Coalesce  function takes a comma separated list of arguments which are evaluated in the order in which they are specified and returns the first non-NULL value found.

Syntax : Coalesce(expr1expr2….expr_n)

Example : Assume that Score1 and Score2 are  columns in table Grades and that nulls are allowed in Score1 but not in Score2.

 

Select * From Grades

Where Coalesce(Score1,0) + Score2 > 100;

When this query is run, if the Score1 is null, it will be assumed as 0.

Suppose if the table Grade has 2 rows as follows:

Score1             Score2

Row 1              null                  120

Row2              20                     60

1st row will be returned ( Score1 will be assumed as 0.)