How to transform nulls into real values?

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

Use the function COALESCE to substitute real values for nulls:

Staff Table

empno ename job mgr_no hiredate sal commission deptno
7369 ALI CLERK 7902 1980-12-17 800.00 NULL 20
7499 ALLEN SALESMAN 7698 1981-02-20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981-02-22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981-04-02 2975.00 NULL 20
7654 MARTIN SALESMAN 7698 1981-09-28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981-05-01 2850.00 NULL 30
7782 CLARK MANAGER 7839 1981-06-09 2450.00 NULL 10
7788 RAHUL ANALYST 7566 1982-12-09 3000.00 NULL 20
7839 RAJ PRESIDENT NULL 1981-11-17 5000.00 NULL 10
7844 TURNER SALESMAN 7698 1981-09-08 1500.00 0.00 30
7876 ADAM CLERK 7788 1983-01-12 1100.00 NULL 20
7900 JAMES CLERK 7698 1981-12-03 950.00 NULL 30
7902 ARHAM ANALYST 7566 1981-12-03 3000.00 NULL 20
7934 MILLER CLERK 7782 1982-01-23 1300.00 NULL 10

Example

SELECT coalesce(commission,50)
FROM Staff

Output

commission
0
300
500
0
1400
0
0
0
0
0
0
0
0
0

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 COMMISSION is returned whenever COMMISSION is not null. Otherwise, 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 commission NOT null then comm
else 0
end
FROM Staff

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