1

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)
   )
süleyman
  • 93
  • 10

2 Answers2

4

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

MT0
  • 143,790
  • 11
  • 59
  • 117
0

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)
Ankit Bajpai
  • 13,128
  • 4
  • 25
  • 40
  • So in practice, pros uses NVL, to like every nullable columns in where clause? – süleyman Aug 27 '21 at 08:55
  • It is preferable to use it until you have an index on any of them. If you do have an index use MT0's method. – Ankit Bajpai Aug 27 '21 at 12:08
  • @süleyman not necessarily. Any reply to your statement "pros uses NVL" boarders on the philosophical. I tend to prefer the longer but more explicit `column in ( ... ) or column is null` to using the `nvl` function whether I have an index or not. When I want to do something with a NULL value I want that to be very clear that I am doing so. I do not want to hide it behind a a function call. But still that is a preference not a hard cast rule. – Belayer Aug 30 '21 at 22:18