Db2 COALESCE

Summary: in this tutorial, you will learn about the Db2 COALESCE() function and how to use it to substitute NULL values.

Introduction to Db2 COALESCE() function

The basic syntax of the COALESCE() function is the following:

COALESCE(v1,v2,...);
Code language: SQL (Structured Query Language) (sql)

The COALESCE() function accepts a number of arguments and returns the first non-NULL argument. If all arguments are NULL, the COALESCE() function returns NULL.

Here is a simple example of using the COALESCE() function:

SELECT COALESCE(NULL, 1, 2) result FROM SYSIBM.SYSDUMMY1;
Code language: SQL (Structured Query Language) (sql)

The output is as follows:

RESULT ----------- 1
Code language: SQL (Structured Query Language) (sql)

Db2 COALESCE() function examples

We’ll use the books table from the sample database to demonstrate the COALESCE() function.

This query returns the book title and ISBN of all books from the books table:

SELECT title, isbn FROM books ORDER BY title;
Code language: SQL (Structured Query Language) (sql)
db2 coalesce function example

The ISBN column has NULL values. To substitute these NULL values by more meaningful values, you can use the COALESCE() function as shown in the following query:

SELECT title, COALESCE(isbn, 'N/A') isbn FROM books ORDER BY title;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

db2 coalesce function substitute null values

In this example, if the value in the isbn column is NULL, the COALESCE function will substitute it by the N/A string. Otherwise, it returns the value of the isbn column.

Db2 COALESCE() function and CASE expression

This COALESCE() function:

COALESCE(e1,e2,e3)
Code language: SQL (Structured Query Language) (sql)

has the same effect as the following searched CASE expression:

CASE WHEN e1 IS NOT NULL THEN e1 WHEN e2 IS NOT NULL THEN e2 ELSE e3 END
Code language: SQL (Structured Query Language) (sql)

For example, this query uses the CASE expression to return the same result set as the example above:

SELECT title, (CASE WHEN isbn IS NULL THEN 'N/A' ELSE isbn END) AS isbn FROM books ORDER BY title;
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use the Db2 COALESCE() function to substitute NULL values.

Was this tutorial helpful ?