Practical SQL Handbook, The: Using SQL Variants, Fourth Edition

Functions That Work with NULLs

Most systems provide a function that allows you to substitute a display value for NULLs on the fly; the ANSI version is called COALESCE. In this example, the function has two arguments: the name of the column and the value to display for any NULLs found in that column.

SQL

select type, coalesce( type, 'Who knows?') as NewType, count(*) as Count
from titles
group by type
type         NewType            Count
============ ============ ===========
popular_comp popular_comp           3
business     business               4
psychology   psychology             5
mod_cook     mod_cook               2
trad_cook    trad_cook              3
(NULL)       Who knows?             1
[6 rows]

There is more on COALESCE in “Changing Null Displays” in Chapter 11.

SQL VARIANTS On Oracle and Informix, the function is called NVL. Oracle SQL> ...