DB2 Tutorial

  • Home
  • Start Here
  • Basics
  • Views
  • Triggers
  • Indexes
  • Functions
    • Aggregate Functions
    • Date Functions
    • String Functions
    • Window Functions
Home » Db2 Basics » Db2 COALESCE

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 ?
  • YesNo
Previous Db2 CASE Expression
Next Db2 CAST

Getting Started

  • What is Db2
  • Installing Db2 Database Server
  • Db2 Sample Database
  • Creating a Db2 Sample Database
  • Connecting to a Db2 Database
  • Interacting with Db2 using SQL Developer

Data Manipulation

  • SELECT
  • ORDER BY
  • WHERE
  • SELECT DISTINCT
  • AND
  • OR
  • BETWEEN
  • LIKE
  • IN
  • LIMIT
  • FETCH
  • Join
  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • FULL OUTER JOIN
  • Self-Join
  • CROSS JOIN
  • GROUP BY
  • Subquery
  • HAVING
  • UNION
  • INTERSECT
  • EXCEPT
  • Common Table Expression or CTE
  • INSERT
  • INSERT Multiple Rows
  • INSERT INTO SELECT
  • UPDATE
  • DELETE

Managing Database Objects

  • CREATE TABLE
  • Identity Columns
  • ALTER COLUMN
  • ADD COLUMN
  • DROP COLUMN
  • DROP TABLE
  • TRUNCATE TABLE
  • RENAME TABLE

Db2 Constraints

  • Primary Key
  • Foreign Key
  • DEFAULT
  • NOT NULL
  • CHECK
  • UNIQUE

Db2 Data Types

  • Integer
  • Decimal
  • VARCHAR
  • CHAR
  • DATE
  • TIME
  • TIMESTAMP

Useful Functions & Expressions

  • CAST
  • CASE Expression
  • COALESCE

About db2tutorial.com

The db2tutorial.com website provides you with a comprehensive IBM DB2 tutorial with many practical examples and hands-on sessions.

Recent Tutorials

  • Db2 Functions
  • Db2 RANK
  • Db2 ROW_NUMBER
  • Db2 Window Functions
  • What is Db2

Site Links

  • Home
  • About Us
  • Contact Us
  • Privacy Policy
Copyright © © 2021 by www.db2tutorial.com. All Rights Reserved.