Oracle in and exists statements

Oracle in and exists statements in and exists in use a hash connection between the external table and the internal table, while exists uses a loop for the External table. Each loop then queries the internal table. The argument that exists is more efficient than in is always inaccurate. Www.2cto.com if the two tables to be queried are of the same size, there is little difference between in and exists. Full text: in and exists in use a hash connection between the external table and the internal table, while exists uses a 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, use exists for A large subquery table and in for A small subquery table, for example, Table A (small table) and Table B (large table) 1: select * from A where cc in (select cc from B) is inefficient and uses the index of the cc column in table; select * from A where exists (select cc from B where cc =. cc) high efficiency, using the cc column index on table B. 2 On the contrary: select * from B where cc in (select cc from A) is highly efficient and uses the index of the cc column on table B; select * from B where exists (select cc from A where cc = B. cc) www.2cto.com is inefficient and uses the index of the cc column in table. Not in and not exists if the query statement uses not in, the internal and external tables are scanned for the whole table, and no index is used. However, the not extsts subquery can still use the table index. Therefore, whether the table is large, not exists is faster than not in. Difference between in and = select name from student where name in ('zhang ', 'wang', 'lil', 'zhao '); the result is the same as that of select name from student where name = 'zhang' or name = 'lil' or name = 'wang' or name = 'zhao.