sqloracle-databasenullwhere-clausenvl

Null values in where clause at in statement Oracle Sql

I know I can't simply say NULL values equals to some integer etc. But I wonder what is the output of NULL = 5 or something like that. I don't think it is false either because of the code below.

select * 
from hr.employees
where
   NOT (
      COMMISSION_PCT IN (.4,.3,.2,.25,.15,.35,.1)
   )

I guess when commission_pct is null it is neither true nor false. What is the good practice in these types of problems, dealing with nulls? Should I use something like below in every not null column for caution?

select * 
from hr.employees
where
   NOT (
      NVL(COMMISSION_PCT,.99) IN (.4,.3,.2,.25,.15,.35,.1)
   )
Solution

  • Add a comparison to NULL:

    SELECT * 
FROM   hr.employees
WHERE  COMMISSION_PCT NOT IN (.4,.3,.2,.25,.15,.35,.1)
OR     COMMISSION_PCT IS NULL;

    If you use NVL then Oracle will not use an index on the column (you would need a function-based index on NVL(commission_pct, 0.99) instead).