How to handle NULLs in operations and comparisons in SQL?

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

You want to evaluate the values returned by a nullable column as you would real values because NULL is never equal to or not equal to any value, not even itself. For instance, you want to find all staff in the Staff Table whose commission is less than the commission of staff WARD. staff with a NULL commission should be included as well.

Use a function such as COALESCE to transform the NULL value into a real value that
can be used in standard evaluation:

SELECT ename,commission,COALESCE(commission,0)
FROM Staff
WHERE COALESCE(commission,0) < ( SELECT commission
FROM Staff WHERE ename = 'WARD' )

Output

ename commission COALESCE(commission,0)
ALI 0
ALLEN 300 300
JONES 0
BLAKE 0
CLARK 0
RAHUL 0
RAJ 0
TURNER 0 0
ADAM 0
JAMES 0
ARHAM 0
MILLER 0

Discussion

The COALESCE function will return the first non-NULL value from the list of values
passed to it. When a NULL value is encountered, it is replaced by zero, which is then
compared with WARD’s commission. This can be seen by putting the COALESCE
function in the SELECT list above.