CODEWITHSUNDEEP
oracle-databasenullnotin
user5122076
user5122076

Reputation:

Not in condition does not consider null in Oracle

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.

Upvotes: 0

Views: 102

Answers (2)

Dart XKey
Dart XKey

Reputation: 54

Check like this

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

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

Upvotes: 0

Piotr Siekierski
Piotr Siekierski

Reputation: 494

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

Upvotes: 3

Related Questions