Recipe1.12.Transforming Nulls into Real Values

Recipe 1.12. Transforming Nulls into Real Values

Problem

You have rows that contain nulls and would like to return non-null values in place of those nulls.

Solution

Use the function COALESCE to substitute real values for nulls:

 1 select coalesce(comm,0) 2   from emp 

Discussion

The COALESCE function takes one or more values as arguments. The function returns the first non-null value in the list. In the solution, the value of COMM is returned whenever COMM is not null. Otherwise, a zero is returned.

When working with nulls, it's best to take advantage of the built-in functionality provided by your DBMS; in many cases you'll find several functions work equally as well for this task. COALESCE happens to work for all DBMSs. Additionally, CASE can be used for all DBMSs as well:

 select case        when comm is null then 0        else comm        end   from emp 

While you can use CASE to translate nulls into values, you can see that it's much easier and more succinct to use COALESCE.