INEXISTS comparison in OracleSQL

What is the difference between in and exists when reading data in OracleSQL? 1. Performance Comparison: Select * fromT1wherexin (selec

What is the difference between in and exists when retrieving data in Oracle SQL? 1 Performance Comparison: Select * from T1 where x in (selec

What is the difference between in and exists when retrieving data in Oracle SQL?

1. Performance Comparison
For example, Select * from T1 where x in (select y from T2)
The execution process is equivalent:
Select *
From t1, (select distinct y from t2) t2
Where t1.x = t2.y;

Relative

Select * from t1 where exists (select null from t2 where y = x)
The execution process is equivalent:
For x in (select * from t1)
Loop
If (exists (select null from t2 where y = x. x)
Then
OUTPUT THE RECORD
End if
End loop
Table T1 is inevitably completely scanned.

What are the applicable scenarios?
Take the subquery (select y from T2) as the direction of consideration
If the result set of a subquery is large, it takes a lot of time, but T1 is relatively small to execute (select null from t2 where y = x. x) very fast, then exists is more suitable for this
Use in when the result set of the corresponding subquery is small.

2. Comparison in meaning
Under standard scott/tiger users

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

17499ALLENSALESMAN76981981/02/201600 .00300.0030

27521WARDSALESMAN76981981/02/221250 .00500.0030

37566JONESMANAGER78391981/04/022975 .0020

47654MARTINSALESMAN76981981/09/281250 .001400.0030

57698BLAKEMANAGER78391981/05/012850 .0030

67782 clarkmanager78391981/06/092450 .0010

77788SCOTTANALYST75661987/04/193000 .0020

87839 KINGPRESIDENT 1981/11/175000.0010

97844TURNERSALESMAN76981981/09/081500 .000.0030

107876ADAMSCLERK77881987/05/231100 .0020

117900JAMESCLERK76981981/12/03950 .0030

2017902fordanalyst75661981/12/033000 .0020

137934MILLERCLERK77821982/01/231300 .00 10

Run
SQL> select count (*) from emp where empno not in (select mgr from emp );
COUNT (*)
----------
0
SQL> select count (*) from emp T1
2 where not exists (select null from emp T2 where t2.mgr = t1.empno); -- it does not have any special function to retrieve null from the subquery, but it only indicates that everything is the same.
COUNT (*)
----------
8
The results are obviously different. The problem lies in the data with MGR = null. No result of X not in (null) is valid.
Use a small example to test:
Select * from dual where dummy not in (NULL) -- no rows selected
Select * from dual where NOT (dummy not in (NULL) -- no rows selected
These two SQL statements may retrieve data, but they do not. In SQL, the logical expression value can have three results (true false null), and null is equivalent to false.