Differences between Oracle in and exists statements

Select * from
Where ID in (select ID from B)

The preceding query uses the in statement. In () is executed only once. It finds all Id fields in Table B and caches them. then, check whether the IDs of Table A are equal to those of Table B. If they are equal, add the records of Table A to the result set until all the records of Table A are traversed.
Its query process is similar to the following process:

List resultset = [];
Array A = (select * From );
Array B = (select ID from B );

For (INT I = 0; I <A. length; I ++ ){
For (Int J = 0; j <B. length; j ++ ){
If (A [I]. ID = B [J]. ID ){
Resultset. Add (A [I]);
Break;
}
}
}
Return resultset;

It can be seen that in () is not suitable for large data volumes in Table B because it traverses all data in Table B once.
For example, if Table A has 10000 records and table B has 1000000 records, it is possible to traverse a maximum of 10000*1000000 times, resulting in poor efficiency.
For example, if Table A has 10000 records and table B has 100 records, it is possible to traverse a maximum of 10000*100 times, greatly reducing the number of traversal times and greatly improving the efficiency.

Conclusion: In () is suitable for the case where Table B has less data than table.

Select a. * From
Where exists (select 1 from B where a. ID = B. ID)

The above query uses the exists statement, and exists () will execute. length, it does not cache exists () result set, because exists () result set content is not important, it is important to check whether there is a record in the result set, if yes, return true, if not, false is returned.
Its query process is similar to the following process:

List resultset = [];
Array A = (select * From)

For (INT I = 0; I <A. length; I ++ ){
If (exists (A [I]. ID) {// execute select 1 from B where B. ID = A. ID to check whether a record is returned
Resultset. Add (A [I]);
}
}
Return resultset;

When table B is larger than table A, exists () is suitable because it does not perform the traversal operation. You only need to perform another query.
For example, if Table A has 10000 records and table B has 1000000 records, exists () will execute 10000 times to determine whether the IDs in Table A are equal to those in table B.
For example, if Table A has 10000 records and table B has 100000000 records, exists () still executes 10000 times because it only executes. length times. The more data in Table B, the more suitable exists.
For another example, if Table A has 10000 records and table B has 100 records, it is better to use in () to traverse 10000*10000 times if exists () is executed 100 times, because in () is compared in memory traversal, and exists () needs to query the database, we all know that the query database consumes a higher performance, and the memory is relatively fast.

Conclusion: exists () is applicable when table B is larger than table.

When the data in Table A is as big as that in Table B, the in and exists efficiency is similar. You can choose one of them.