CODEWITHSUNDEEP
sqloracle-databasenullwhere-clausenvl
süleyman
süleyman

Reputation: 93

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)
   )

Upvotes: 1

Views: 1653

Answers (2)

MT0
MT0

Reputation: 168232

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).

Upvotes: 4

Ankit Bajpai
Ankit Bajpai

Reputation: 13519

As you already said "I can't simply say NULL values equals to some integer". So NULL = 5 would be resulted as null and would return no rows.

Interestingly, If there is any null value in the values getting compared, The result would still be null. For eg -

WHERE COMMISSION_PCT IN (4, 3, 2, 25, 15, 35, 1, NULL);

This will compare the value of COMMISSION_PCT with null and would be turned to false eventually, and no rows would return.

Its always better to use NVL function with nullable columns. Like you said -

NVL(COMMISSION_PCT,.99) IN (.4,.3,.2,.25,.15,.35,.1)

Upvotes: 0

Related Questions