Good morning.
According to Oracle documentation:
https://docs.oracle.com/database/121/SQLRF/conditions014.htm#SQLRF52169 “If any item in the list following a NOT IN operation evaluates to null, then all rows evaluate to FALSE or UNKNOWN, and no rows are returned.”
So I have done some tests:
SQL> set serveroutput on
SQL> set echo on
SQL>
SQL> select 'true'
2 from dual
3 where (1) not in (2,null);
no rows selected
That looks correct.
SQL>
SQL> select 'true'
2 from dual
3 where (1) not in (null, 2);
no rows selected
That looks correct also.
SQL>
SQL>
SQL> select 'true'
2 from dual
3 where (1,2) not in ((2,null));
'TRU
----
true
Is it correct or am I missing something?
SQL>
SQL> select 'true'
2 from dual
3 where (1,2) not in ((null,2));
no rows selected
That looks correct.
So it looks like that it works like it is described in documentation only if we are checking one field
using NOT IN, while for more than one it works as expected only if NULL value appears first on the list.
Best Regards.
Piotr
Think of null in terms of an "unknown" value. Hence
select 'true'
from dual
where (1) not in (2,null);
*might* be true, it might be false. We don't know because the 'null' could be 1 or 2. So we can't give you a conclusive answer, because it is indeterminate.
But in this instance
select 'true'
from dual
where (1,2) not in ((2,null));
the 1!=2 means that no matter what the unknown value equates to, it can never be a match, so the NOT IN is valid
whereas
select 'true'
from dual
where (1,2) not in ((null,2));
*might* be true, it might be false. We don't know because the 'null' could be 1 or 2