Oracle exists and not exist

Let's take a look at the following example: Two system tables in Oracle. emp, dept. example:

1: not exists (not in)

Not exists:

This statement returns the result set that the select * from scott. dept d where e. deptno = d. deptno and d. deptno = 10 conditions meet. That is,

The returned result set does not have a record of d. deptno = 10, that is, the emp table does not have a record of d. deptno = 10 in the dept table.

SQL> select empno, ename, deptno from scott. emp e where not exists (select * from scott. dept d where e. deptno = d. deptno and d. deptno = 10 );

EMPNO ENAME DEPTNO
---------------------
7369 SMITH 20
7499 ALLEN 30
7521 WARD 30
7566 JONES 20
7654 MARTIN 30
7698 BLAKE 30
7788 SCOTT 20
7844 TURNER 30
7876 ADAMS 20
7900 JAMES 30
7902 FORD 20

11 rows selected

Not in:

The deptno field must be specified in the first where condition, and the deptno field must be specified in the second SQL statement ).

SQL> select empno, ename, deptno from scott. emp e where deptno not in (select deptno from scott. dept d where e. deptno = d. deptno and d. deptno = 10 );

EMPNO ENAME DEPTNO
---------------------
7369 SMITH 20
7499 ALLEN 30
7521 WARD 30
7566 JONES 20
7654 MARTIN 30
7698 BLAKE 30
7788 SCOTT 20
7844 TURNER 30
7876 ADAMS 20
7900 JAMES 30
7902 FORD 20

11 rows selected

2: exists (in)

Exists:

This statement returns the record result set that the select * from scott. dept d where e. deptno = d. deptno and d. deptno = 10 conditions meet.

That is to say, only records with d. deptno = 10 exist in the returned result set, that is, only records with d. deptno = 10 exist in the dept table in the emp table.

SQL> select empno, ename, deptno from scott. emp e where exists (select * from scott. dept d where e. deptno = d. deptno and d. deptno = 10 );

EMPNO ENAME DEPTNO
---------------------
7782 CLARK 10
7839 KING 10
7934 MILLER 10

In:

The deptno field must be specified in the first where condition, and the deptno field must be specified in the second SQL statement ).

SQL> select empno, ename, deptno from scott. emp e where deptno in (select deptno from scott. dept d where e. deptno = d. deptno and d. deptno = 10 );

EMPNO ENAME DEPTNO
---------------------
7782 CLARK 10
7839 KING 10
7934 MILLER 10

In oracle, exists (in) and not exists (not in) are key sub-statements used to determine whether or not records exist and do not exist in the table.

Note: not in is not logically equivalent to not exists. If you misuse not in, be careful that your program has a fatal BUG: Therefore, try not to use not in (it will call the subquery), and try to use not exists (it will call the associated subquery ).