Not exists case

Today, a developer asks a question about not exists. Let's make an experiment.

Lab 1

SQL> select * from emp e1 where not exists (select 1 from emp e2 where e1.mgr = e2.empno );


EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------------------------------------------------------------------------------------
7839 king president 17-NOV-81 5000 10

Lab 2

SQL> select * from emp e1 where not exists (select 1 from emp e2 where e1.empno = e2.mgr );


EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------------------------------------------------------------------------------------
7369 smith clerk 7902 17-DEC-80 800 20
7499 allen salesman 7698 20-FEB-81 1600 300 30
7521 ward salesman 7698 22-FEB-81 1250 500 30
7654 martin salesman 7698 28-SEP-81 1250 1400 30
7844 turner salesman 7698 08-SEP-81 1500 0 30
7876 adams clerk 7788 23-MAY-87 1100 20
7900 james clerk 7698 03-DEC-81 950 30
7934 miller clerk 7782 23-JAN-82 1300 10


8 rows selected.

1 and 2 SQL statements are roughly the same, but the results are completely different.

Experiment 1 shows how many people have no manager

Lab 2: How many people have manager