An issue where Oracle uses NULL in not in

Http://www.linuxidc.com/Linux/2012-07/66212.htm

I used to summarize some of the questions about NULL in Oracle, and I happened to see another one in the process of reading today that we didn't notice before, which is the problem of using NULL in not.

Sql> select * FROM dept;

DEPTNO dname LOC
---------- -------------- -------------
Ten ACCOUNTING NEW YORK
DALLAS
SALES CHICAGO
OPERATIONS BOSTON

Sql> Select Deptno
2 from Dept
3 where Deptno in (10,50,null);

DEPTNO
----------
10

It is normal to see the use of in, even if there is null.

Sql> Select Deptno
2 from Dept
3 where Deptno not in (10,50);

DEPTNO
----------
20
30
40

It seems to fit our expectations.

Sql> Select Deptno
2 from Dept
3 where Deptno not in (10,50,null);

No rows selected

Why did you add a null before the 20, 30, 403 data is not displayed

In and not in are essentially or operations, so that when you calculate a logical OR, you deal with NULL in different ways, resulting in a different result.

Let's analyze the previous three statements

Sql> Select Deptno
2 from Dept
3 where Deptno in (10,50,null);

Here can be equivalent to where deptno=10 or deptno=50 or deptno=null, because is or connected, so long as one condition is true, the whole is fed to true. So Deptno's record for 10 shows up.

Sql> Select Deptno
2 from Dept
3 where Deptno not in (10,50,null);

Here is equivalent to where not (deptno=10 or deptno=50 or deptno=null), take the deptno=20 record for example.

Not (20=10 or 20=50 or 20=null)

Not (false or false or null)

NOT NULL

Null

(previously only known when the Where condition returns false is not true, now it seems to return NULL when it is not true, the following is a small experiment to prove this conjecture)

#####################

Sql> SELECT * FROM dept
2 where 1=null;

No rows selected

#####################

Sql> Select Deptno
2 from Dept
3 where Deptno not in (10,50);

Here is equivalent to where not (deptno=10 or deptno=50), still take deptno=20 for example.

Not (20=10 or 20=50)

Not (false or false)

Not false

True

Note: FALSE or null=null, while TRUE or null=true.

An issue where Oracle uses NULL in not in