Db2 – How to provide the key value in a join when a match is not found

db2join;

My DB2 database has the following tables:

CustomerLog

customer  version  userid  description
--------  -------  ------  ----------------------------
Orange    101      2       New customer
Orange    102      2       Bespoke widgets added
Orange    103      4       Fix errors after review
Orange    104      3       Priority bugfix
Peach     101      1       New customer
Peach     102      3       Custom screens for Peach
...

Users

userid  name
------  -------------------
1       Andrew
2       Belinda
3       Charles

With the select statement:

select c.customer, c.version, u.name, c.description
from customerlog c, users u
where c.customer='Orange'
and c.userid = u.userid

I get the following result:

customer  version  name        description
--------  -------  ----------  ----------------------------
Orange    101      Belinda     New customer
Orange    102      Belinda     Bespoke widgets added
Orange    104      Charles     Priority bugfix

If I instead used a left join, I could get the following row selected as well:

Orange    103      null        Fix errors after review

… but is there a way to get the userid in place of the null? ie:

Orange    103      4           Fix errors after review

Best Answer

Use COALESCE

Purpose

COALESCE returns the first non-null expr in the expression list. You must specify at least two expressions. If all occurrences of expr evaluate to null, then the function returns null.

select c.customer, c.version, coalesce(u.name, to_char(c.userid)) as user c.description
from customerlog c
     left join users u on u.userid = c.userid
where c.customer='Orange'
Related Question