# Differences between in and exists

Differences between in and exists
 
In performs hash join on the External table and the internal table, while exists performs loop on the External table. Each loop then queries the internal table.

 

The argument that exists is more efficient than in is always inaccurate.
If the two tables to be queried are of the same size, there is little difference between in and exists.

If one of the two tables is small and the other is a large table, exists is used for the large subquery table and in is used for the small subquery table:

Example: Table A (small table) and Table B (large table)
1:
Select * from a where CC in (select CC from B)
Low Efficiency: the CC column index of Table A is used;
Select * from a where exists (select CC from B where cc = A. CC)
High Efficiency: the CC column index of Table B is used.
Opposite
2:
Select * from B where CC in (select CC from)
High Efficiency: the CC column index of Table B is used;
Select * from B where exists (select CC from a where cc = B. CC)
Low Efficiency: the CC column index of Table A is used.

Correlated subqueries with in are redundant because the in clause provides the same functions as related operations in subqueries. For example:
Select staff_name from staff_member where staff_id in
(Select staff_id from staff_func where staff_member.staff_id = staff_func.staff_id );

It is not appropriate to specify the exists clause for a non-correlated subquery, because this will produce the flute card product. For example:
Select staff_name from staff_member where staff_id
Exists (select staff_id from staff_func );

Not in and not exists

If the query statement uses not in, all the internal and external tables are scanned, and no index is used;
However, subqueries of not extsts can still use table indexes.
Therefore, no matter which table is large, not exists is faster than not in.

Try not to use the not in clause. The minus clause is faster than the not in clause, although the minus clause requires two queries:
Select staff_name from staff_member where staff_id in (select staff_id from staff_member minus select staff_id from staff_func where func_id like '123 ');

Difference between in and "="

Select name from student where name in ('zhang ', 'wang', 'lil', 'zhao ');

And

Select name from student where name = 'zhang' or name = 'lil' or name = 'wang' or name = 'zhao'

The results are the same.