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:
1 | COALESCE(v1,v2,...); |
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:
1 2 3 4 | SELECT COALESCE(NULL, 1, 2) result FROM SYSIBM.SYSDUMMY1; |
The output is as follows:
1 2 3 | RESULT ----------- 1 |
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:
1 2 3 4 5 6 | SELECT title, isbn FROM books ORDER BY title; |
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:
1 2 3 4 5 6 | SELECT title, COALESCE(isbn, 'N/A') isbn FROM books ORDER BY title; |
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:
1 | COALESCE(e1,e2,e3) |
has the same effect as the following searched CASE expression:
1 2 3 4 5 | CASE WHEN e1 IS NOT NULL THEN e1 WHEN e2 IS NOT NULL THEN e2 ELSE e3 END |
For example, this query uses the CASE expression to return the same result set as the example above:
1 2 3 4 5 6 7 8 9 10 | SELECT title, (CASE WHEN isbn IS NULL THEN 'N/A' ELSE isbn END) AS isbn FROM books ORDER BY title; |
In this tutorial, you have learned how to use the Db2 COALESCE() function to substitute NULL values.