Recipe3.12.Using NULLs in Operations and Comparisons

Recipe 3.12. Using NULLs in Operations and Comparisons

Problem

NULL is never equal to or not equal to any value, not even itself, but you want to evaluate values returned by a nullable column like you would evaluate real values. For example, you want to find all employees in EMP whose commission (COMM) is less than the commission of employee "WARD". Employees with a NULL commission should be included as well.

Solution

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

 1 select ename,comm 2   from emp 3  where coalesce(comm,0) < ( select comm 4                                  from emp 5                                 where ename = 'WARD' ) 

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:

  select ename,comm,coalesce(comm,0)   from emp  where coalesce(comm,0) < ( select comm                                  from emp                                 where ename = 'WARD' )  ENAME            COMM COALESCE(COMM,0)  ---------- ---------- ----------------  SMITH                                0  ALLEN             300              300  JONES                                0  BLAKE                                0  CLARK                                0  SCOTT                                0  KING                                 0  TURNER              0                0  ADAMS                                0  JAMES                                0  FORD                                 0  MILLER                               0