COALESCE in DB2

In IBM DB2, the COALESCE() function is used to return the first non-null expression in a list of expressions. It works similarly to how it works in other SQL-based databases.

Here's the syntax:

COALESCE(expression1, expression2, ..., expressionN)

- expression1, expression2, ..., expressionN are the expressions or values to be evaluated.

Let's see an example:

Suppose we have a table called employees with columns id, name, salary, and bonus. Some employees might not have a bonus specified, and we want to display a default value of 0 for their bonus.

CREATE TABLE employees (

    id INT,

    name VARCHAR(100),

    salary DECIMAL(10, 2),

    bonus DECIMAL(10, 2)

);

INSERT INTO employees (id, name, salary, bonus) VALUES

(1, 'John', 50000.00, 2000.00),

(2, 'Alice', 60000.00, NULL),

(3, 'Bob', 55000.00, NULL);

Now, let's use COALESCE() to handle null values in the bonus column:

SELECT 

    id,

    name,

    salary,

    COALESCE(bonus, 0) AS bonus

FROM 

    employees;

This query will return:

| id | name  | salary   | bonus  |

|----|-------|----------|--------|

| 1  | John  | 50000.00 | 2000.00|

| 2  | Alice | 60000.00 | 0.00   |

| 3  | Bob   | 55000.00 | 0.00   |

As you can see, for employees without a bonus specified (like Alice and Bob), COALESCE() returns 0 as the default value. For John, who has a bonus specified, it returns the actual bonus value. This behavior is consistent with the usage of COALESCE() in other SQL databases.