Null values in where clause at in statement Oracle Sql

Question

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

Yes, when commission_pct is null then a comparison with it is neither true nor false (it's null). — William Robertson
– William Robertson, Commented Aug 27, 2021 at 16:14

MT0 · Accepted Answer · 2021-08-27 08:59:47Z

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

answered Aug 27, 2021 at 8:59

MT0

173k12 gold badges70 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

süleyman Over a year ago

Thank you for your answer. I wonder if programmers uses is null to all nullable columns no matter what

Ankit Bajpai · Accepted Answer · 2021-08-27 08:50:17Z

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)

answered Aug 27, 2021 at 8:50

Ankit Bajpai

13.6k4 gold badges27 silver badges43 bronze badges

3 Comments

süleyman Over a year ago

So in practice, pros uses NVL, to like every nullable columns in where clause?

Ankit Bajpai Over a year ago

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.

Belayer Over a year ago

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

Collectives™ on Stack Overflow

Null values in where clause at in statement Oracle Sql

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related