MySQL exists and in efficiency comparison

The in statement applies to cases where a table is larger than table B

SELECT * from a Where a_id in (select a_id from B);

The EXISTS statement applies to cases where B is larger than table A.

SELECT * from a WHERE EXISTS (select a_id from b where a.id = b.a_id);

Reason: (Forward)

SELECT * FROM A
where ID in (select ID from B)

The above query uses the in statement, in () executes only once, it detects all the ID fields in table B and caches them. After that, check that the ID of table A is equal to the ID in table B, and if it is equal, add the records of table A to the result set until all records of table A are traversed.
Its query process is similar to the following procedure

List resultset=[];
Array a= (SELECT * from A);
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;

As you can see, it is not appropriate to use in () when the table B data is large, because it iterates through the B-table data all at once.
such as: A table has 10,000 records, B table has 1 million records, then it is possible to traverse 10000*1000000 times, the efficiency is very poor.
Again such as: A table has 10,000 records, b table has 100 records, then it is possible to traverse 10000*100 times, the number of traversal greatly reduced, efficiency greatly improved.

Conclusion: In () The case of B-table is smaller than the data of table A

Select A.* from a A
where exists (select 1 from b b where a.id=b.id)

The above query uses the EXISTS statement, exists () executes a.length times, and it does not cache the exists () result set because the contents of the exists () result set are not important, it is important whether there is a record in the result set, and if so, returns True if none returns False .
Its query process is similar to the following procedure

List resultset=[];
Array a= (SELECT * from A)

for (int i=0;i<a.length;i++) {
if (exists (a[i].id) {//execute select 1 from b where b.id=a.id if there is a record returned
Resultset.add (A[i]);
}
}
return resultSet;

When the B table is larger than the A-table data, it is appropriate to use exists (), because it does not have that traversal operation and only needs to execute the query again.
such as: A table has 10,000 records, B table has 1 million records, then exists () will perform 10,000 times to determine whether the ID in table A is equal to the ID in table B.
such as: A table has 10,000 records, b table has 100 million records, then exists () or execute 10,000 times, because it only executes a.length times, the more the B table data, the more suitable for exists () to play the effect.
Again such as: A table has 10,000 records, b table has 100 records, then exists () or execute 10,000 times, it is better to use in () to traverse 10000*100 times, because in () is in memory traversal comparison, and exists () need to query the database, We all know that querying a database consumes more performance and memory is faster.

Conclusion: Exists () is suitable for the case of B-table larger than a-table data

When the A-table data is as large as the B-table data, in and exists efficiency is similar, can choose one to use.

MySQL exists and in efficiency comparison