Shulou(Shulou.com)06/01 Report--

This article mainly introduces what is the difference between in and exists in mysql. It is very detailed and has a certain reference value. Friends who are interested must read it!

The differences between exists and in are as follows: in connects the outer table with the inner table by hash, querying the inner table first; exists does a loop loop on the exterior, and then queries the inner table after the loop; it is faster to use in when the appearance is large, and it is faster to use exists when the inner table is large.

The difference between exists and in

# query to B involves id and uses index, so table B is efficient, large table is available-- > outer small inner large select * from A where exists (select * from B where A.id=B.id); # query to A query involves id and uses index, so A table is efficient and can use large table-> outer large inner small select * from A where A.id in (select id from B)

(1) exists does a loop loop on the outside, and each loop cycle queries the inner table (subquery), so because of the index used in the query of the inner table (the inner table is efficient, so large tables can be used), and how much of the appearance needs to be traversed, it is inevitable (use small tables as much as possible), so the use of exists with large inner tables can speed up the efficiency.

(2) in connects the exterior and the inner table with hash, querying the inner table first, and then matching the results of the inner table with the appearance, using indexes on the outside (high efficiency on the outside, large tables are available), while most of the inner tables need to be queried, which is inevitable, so the use of in with large appearance can accelerate the efficiency.

(3) if the two tables of the query are of the same size, there is little difference between using in and exists. If one of the two tables is small and the other is large, the large subquery table uses exists and the small subquery table uses in.

The above is all the content of what is the difference between in and exists in mysql. Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.