Differences and usage scenarios for exists and in MySQL

How to use exists and in:

SELECT * from a where exists (select * from B where a.id=b.id), select * from a where a.id in (select ID from B);

1, exists is to do Loop loop, each loop loop and then query the internal table (sub-query), then is the index of internal query use, and the appearance of most need to traverse, unavoidable, so the table large use exists, can speed up efficiency;

2, in is the appearance and inside the table to do hash connection, first query the inner table, and then the inner table results and the appearance of matching, the appearance of the use of index, and most of the inner table need to query, unavoidable, so the appearance of large use in, can speed up efficiency.

3, if not in, it is both inside and outside the table are full table scan, no reason, low efficiency, can consider using not exists, can also use A left join B on a.id=b.id where b.id is null to optimize.

In addition, the newly encountered pit, MySQL version issue:

MySQL version of the problem: 5.6.5 optimized subqueries, the introduction of materialized subqueries (for WHERE clause subquery), subquery materialized sub-query results into a temporary table, to ensure that the subquery is executed only once, the table does not record duplicate data and hash index lookup;

The previous version would turn the non-correlated subquery into a correlated subquery, resulting in inefficiency (especially if the subquery is a small table, the appearance is a large table, the efficiency slows down a lot).

　　Correlated subquery: The subquery relies on the return value of the outer connection;

Non-correlated subquery: The subquery does not depend on the return value of the outer connection;

Originally the table is small, with in, but it is said that the version before 5.6 will change the non-correlated sub-query to the relevant sub-query, is to change in the statement into the exists, the results of ultra-low efficiency.

Differences and usage scenarios for exists and in MySQL