Compare IN & amp; exists in Oracle SQL

Compared with IN & EXISTS in Oracle SQL, IN and exists are sometimes used to retrieve data in Oracle SQL. What is the difference between them? 1 Performance Comparison: for example, the execution process of Select * from T1 where x in (select y from T2) is equivalent:
Select * from t1, (select distinct y from t2) t2 where t1.x = t2.y; select * from t1 where exists (select null from t2 where y = x) relative to www.2cto.com) the execution process is equivalent to: for x in (select * from t1) loop if (exists (select null from t2 where y = x. x) When then output the record end ifend loop table T1 inevitably needs to be completely scanned, what are THE applicable situations? Taking the subquery (select y from T2) as the consideration direction, if the subquery result set 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, so exists is more suitable for use here
Use in when the result set of the corresponding subquery is small. the meaning of www.2cto.com 2 is compared in the standard scott/tiger user's empno ename job mgr hiredate sal comm DEPTNO1 7499 allen salesman 7698 1981/02/20 1600.00 300.00 302 7521 ward salesman 7698 1981/02/22 1250.00 500.00 303 7566 JONES MANAGER 7839 1981/04/02 2975.00 204 7654 martin salesman 7698 1981/09/28 1250.00 1400.00 305 7698 blake manager 7839 1981/05/01 2850.00 306 7782 clark manager 7839 1981/ 06/09 2450.00 107 7788 scott analyst 7566 1987/04/19 3000.00 208 7839 king president 1981/11/17 5000.00 109 7844 turner salesman 7698 1981/09/08 1500.00 0.00 3010 7876 adams clerk 7788 1987/05/23 1100.00 2011 7900 james clerk 7698 ANALYST 950.00 3012 7902 FORD ANALYST 7566 1981/12/03 3000.00 2013 7934 miller clerk 7782 1982/01/23 1300.00 10 Execute SQL> select count (*) from emp where empno not in (se Lect mgr from emp); COUNT (*) ---------- 0 www.2cto.com SQL> select count (*) from emp T1 2 where not exists (select null from emp T2 where t2.mgr = t1.empno ); -- Here, null is not used in subqueries, but it indicates that all values are 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 selectedselect * 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.