Description of the relationship between Oracle Null, in, and exists (not in cannot find the result)

My colleague said that the query encountered a strange problem. Two tables did not return results after performing the not in operation. Normally, there should be returned results.

I. Problem Reproduction

In general, the reproduction of the problem is good news, and we are most afraid that the problem cannot be reproduced.

 

SQL> connscott/tiger;

Connected.

SQL> descemp

Name Null? Type

------------------------------------------------------------------

Empno not nullnumber (4)

ENAME VARCHAR2 (10)

JOB VARCHAR2 (9)

Mgr number (4)

HIREDATE DATE

Sal number (7,2)

Comm number (7, 2)

Deptno number (2)

 

Copy an emp table:

SQL> createtable emp1 as select * from emp;

Table created.

 

We insert some values to the emp table:

SQL> insertinto emp (empno, ename) values (8888, 'Dave ');

1 row created.

SQL> commit;

Commitcomplete.

Here, only empno and ename are inserted, and others are empty.

Perform the following operations on two tables: 

SQL> selectempno, ename from emp where job not in (select job from emp1 );

No rowsselected

-- No result set is returned. Under normal circumstances, we should reverse the insert dave.

 

SQL> selectempno, ename from emp where job in (select job from emp1 );

 

EMPNO ENAME

--------------------

7934 MILLER

7900 JAMES

7876 ADAMS

7369 SMITH

7844 TURNER

7654 MARTIN

7521 WARD

7499 ALLEN

7782 CLARK

7698 BLAKE

7566 JONES

 

EMPNO ENAME

--------------------

7902 FORD

7788 SCOTT

7839 KING

 

14 rowsselected.

Change to exists for testing:

SQL> select empno, ename from emp A where not exists (SELECT * FROM emp1 Bwhere B. job = A. job );

 

EMPNO ENAME

--------------------

8888 Dave

 

SQL> select empno, ename from emp A where exists (SELECT * FROM emp1 B where B. job = A. job );

 

EMPNO ENAME

--------------------

7934 MILLER

7900 JAMES

7876 ADAMS

7369 SMITH

7844 TURNER

7654 MARTIN

7521 WARD

7499 ALLEN

7782 CLARK

7698 BLAKE

7566 JONES

 

EMPNO ENAME

--------------------

7902 FORD

7788 SCOTT

7839 KING

 

14 rowsselected.

Using exists is not affected by null. 

1 2 Next Page