Oracle's use of null in not in

Oracle's use of null in not in

In the past, I also summarized some Oracle NULL problems. It happened that I saw another point that I didn't find before when I was reading the book, that is the question of using null in not in.

SQL> select * from dept;

DEPTNO DNAME LOC
-------------------------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

SQL> select deptno
2 from dept
3 where deptno in (10, 50, null );

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

// When we see that in is used, even if there is null, it is normal. Let's take a look at not in.

SQL> select deptno
2 from dept
3 where deptno not in (10, 50 );

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

// It seems to be in line with our expectation.

SQL> select deptno
2 from dept
3 where deptno not in (10, 50, null );

No rows selected

// Why does the data 20, 30, and 40 before null not be displayed?

 

The IN and not in operations are essentially OR operations. Therefore, the method for processing null in the OR logic is different and the results are different.

Next we will analyze the preceding three statements

 

SQL> select deptno
2 from dept
3 where deptno in (10, 50, null );

Here it can be equivalent to where deptno = 10 or deptno = 50 or deptno = null. Because it is an or connected, if one of the conditions is TRUE, TRUE is fed. So the record with deptno 10 is displayed.

 

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 record deptno = 20 as an example.

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

Not (false or null)

Not null

Null

(I used to know that it was not true when the where condition returned false. Now it seems that it is not true when NULL is returned. Here 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 );

This is equivalent to where not (deptno = 10 or deptno = 50). We still use deptno = 20 as an example.

Not (20 = 10 or 20 = 50)

Not (false or false)

Not false

True

Note: false or null = NULL, and true or null = TRUE.