How to transform nulls into real values?

SQL
#1

To replace null values in a database with non-null values, you can use the COALESCE function. This function takes one or more values as arguments and returns the first non-null value in the list. It can be used to replace null values with a default value of your choosing.

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 can be used to handle null values in a database. It takes one or more values as arguments and returns the first non-null value in the list. If all values are null, it returns zero. The CASE statement can also be used for this purpose, and it works across all DBMSs. It is generally recommended to use built-in functions when dealing with null values in a database.

SELECT CASE 
WHEN commission NOT null then comm
else 0
end
FROM Staff

When handling null values in a database, the COALESCE function is often easier and more concise to use than the CASE statement.