According to the relationship with the outer layer:

SELECT * FROM s 1 WHERE key 1 IN(SELECT common _ field FROM S2 WHERE key 3 = ' a ')；

SELECT * FROM s 1 WHERE key 1

Select common_field from s2, where key3 = 'a'

These two tables are S 1S2, which are unrelated queries.

There are too many result sets of inner-layer query, which may not fit in memory and in (too many) is too inefficient.

Therefore, the results of the inner-level query will be put into the temporary table, and this process will be repeated (slimming), which is called materialization.

Select s 1. * from s1semijoin s2ons1.key1= s2.common _ field where key3 =' a.

This statement cannot be carried out, it is just a sign.

Semi-join: only care whether there are matching records in s2 table, not how many records match.

How to eliminate duplicate values by performing semi-join:

If the IN subquery does not meet the conditions for converting into semi-join, and it cannot be converted into materialized table or the cost of converting into materialized table is too high, it will be converted into an EXISTS query.