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)

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:

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.