What is the difference between in and exists in mysql?

The differences between exists and in in mysql are: 1. in is a hash connection between the outer table and the inner table, and the inner table is queried first; 2. exists is a loop on the outer table, and then the inner table is queried ; 3. When the external table is large, it is faster to use in, and when the internal table is large, it is faster to use exists.

The difference between exists and in in mysql is:

#对B查询涉及id，使用索引，故B表效率高，可用大表 -->外小内大
select * from A where exists (select * from B where A.id=B.id);

#对A查询涉及id，使用索引，故A表效率高，可用大表 -->外大内小
select * from A where A.id in (select id from B);

(1) exists is to loop the appearance , each time the loop loops, the internal table (subquery) is queried, so because the query of the internal table uses an index (the internal table is highly efficient, so a large table can be used), and the external table needs to be traversed no matter how big it is, it is inevitable (try to Use a small table), so if the internal table is large, use exists to speed up efficiency;

(2) in is to hash the external table and the internal table, query the internal table first, and then match the internal table results with the external table , use indexes for the outer surface (the outer surface is highly efficient and can be used in large tables), and it is inevitable to query the inner table no matter how large it is. Therefore, using in for a large outer table can speed up the efficiency.

(3) If the two tables queried are of the same size, there is not much difference between using in and exists. If one of the two tables is smaller and the other is a large table, use exists for the larger subquery table and in for the smaller subquery table.

The above is the detailed content of What is the difference between in and exists in mysql?. For more information, please follow other related articles on the PHP Chinese website!