DOES NULL VALUE WITH NOT IN OPERATOR RETURN ROWS?
Create a test table
SQL> create table test (name varchar2(10),num number(10));
Table created.
Insert some rows into it
SQL> insert all into test values('xyz',1)
2 into test values('abc',2)
3 into test values('mno',3)
4 into test values('nhc',4)
5 select * from dual;
4 rows created.
SQL> commit;
Commit complete.
SQL> insert all into test values('xyz',5)
2 into test values('abc',6)
3 into test values('mno',7)
4 into test values('nhc',8)
5 into test values('tnc',9)
6 into test values('jmk',10)
7 select * from dual;
6 rows created.
SQL> commit;
Commit complete.
Select all the rows from table
SQL> select * from test;
NAME NUM
---------- ----------
xyz 1
abc 2
mno 3
nhc 4
xyz 5
abc 6
mno 7
nhc 8
tnc 9
jmk 10
Select rows with either ‘in’ or ‘exists’ clause
SQL> select * from test where num in (1,2);
NAME NUM
---------- ----------
xyz 1
abc 2
Select the same with ‘not in’ clause
select * from test where num not in (1,2);
NAME NUM
---------- ----------
mno 3
nhc 4
xyz 5
abc 6
mno 7
nhc 8
tnc 9
jmk 10
8 rows selected.
Add null to the values and see the results. You dont see any output with zero rows selected. Why is it? Why the other two values 1 and 2 rows are not returned? Here as we dont have null values in the num column, the boolean expression should not return true or false. So oracle does not return any rows here
SQL> select * from test where num not in (1,2,null);
no rows selected
Do the same with ‘in’ clause adding a null value. You can see the same results as earlier
SQL> select * from test where num in (1,2,null);
NAME NUM
---------- ----------
xyz 1
abc 2