Differences between in and exist not-in and not-exist in SQL statements

In and exists
In is the appearance and the inner table as a hash connection, and exists is the external loop loop, each loop loop and then query the internal table. The assertion that exists is more efficient than in is inaccurate.
If the two table size of the query is equal, then the in and exists are not very different.

Full:
In and exists
In is the appearance and the inner table as a hash connection, and exists is the external loop loop, each loop loop and then query the internal table. The assertion that exists is more efficient than in is inaccurate.
If the two table size of the query is equal, then the in and exists are not very different.
If one of the two tables is smaller and one is a large table, then the subquery table is large with exists, and the subquery table is small in:
Example: Table A (small table), table B (large table) 1:select * from A where CC in (select CC from B)
Inefficient, using the index of the CC column on table A; select * from A where exists (select cc from B where cc=a.cc)
High efficiency, using the index of the CC column on the B table.
Opposite 2:select * from B where CC in (select CC from A)
High efficiency, using the index of CC column on B table; select * from B where exists (select cc from A where cc=b.cc)
Inefficient, using the index of the CC column on table A.
Not-in and not-exists if the query statement uses not-in to perform a full-table scan of the outer surface, the index is not used, and the index on the table is still used by not Extsts's subquery. So no matter the table is large, using not exists is faster than not.
difference between in and =
Select name from student where name in (' Zhang ', ' Wang ', ' Li ', ' Zhao ');
And
Select name from student where Name= ' Zhang ' or name= ' li ' or name= ' Wang ' or name= ' Zhao '
The result is the same.

Not is logically not exactly equivalent to not exists, if you misuse not in, be careful that your program has a fatal bug:

Take a look at the following example:
CREATE TABLE T1 (C1 number,c2 number);
CREATE TABLE T2 (C1 number,c2 number);

INSERT into T1 values;
INSERT into T1 values (1,3);
INSERT into T2 values;
INSERT into T2 values (1,null);

SELECT * from t1 where C2 not in (select C2 from T2);
No rows found
SELECT * from T1 where NOT exists (select 1 from T2 where T1.C2=T2.C2);
C1 C2
1 3

As you can see, there is a logic error in the not-in-expected result set. If you look at the execution plan for the two SELECT statements above, it will be different. The latter used Hash_aj.
Therefore, try not to use not-in (which calls the subquery) and try to use not exists (which invokes the associated subquery). If any one of the records returned in the subquery contains null values, the query will not return any records, as shown in the example above.
Unless the subquery field has a non-empty limit, you can use not in, and you can also use Hasg_aj or MERGE_AJ connections by prompting for it.

Differences between in and exist not-in and not-exist in SQL statements