Not in condition does not consider null in Oracle

Question

I have result set as follows

reg_no    name    grade
--------- ------- ------
1112      Muthu   A
1113      Vikki   B
1114      Murali  C
1115      Shankar null

I have checked the condition with not in

select * from grades where grade not in ('B','C')

But when executing query i have got only one record that have A grade. I didn't get the 4th row. Am stuck with this. How to check this.

score 3 · Accepted Answer · answered Feb 01 '16 at 11:10

3

Only one proper way to check null existence is IS NULL and IS NOT NULL

select * from grades where grade not in ('B','C') or grade is null

answered Feb 01 '16 at 11:10

Piotr Siekierski

Thank you piotr. But i have doubt on this. Why we need to check null and it doesn't return the result – Feb 01 '16 at 11:16
You cannot compare null with other value, it produces null as result. https://en.wikipedia.org/wiki/Null_%28SQL%29 – Piotr Siekierski Feb 01 '16 at 11:23
Thanks Piotr. It's very useful – Feb 01 '16 at 12:41

score 0 · Answer 2 · answered Feb 02 '16 at 03:30

0

Check like this

select * from grades where nvl(grade,'?') not in ('B','C');

But this appropriate cannot use index on grafes,if exists.

answered Feb 02 '16 at 03:30

Dart XKey

2 Answers2