Transforming Nulls into Real Values - SQL

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.

** If you want the Full Table detail. Refer the SQL Table in Label List. Or Click here to View the Table