In the MySQL in statement is the appearance and the inner table as a hash connection, and the EXISTS statement is the external loop loop, each loop loop and then query the internal table. It is not accurate to say that exists is more efficient than the in statement. This is to distinguish the environment.
If the two table size of the query is equal, then the in and exists are not very different . if one of the two tables is smaller and one is a large table, then the subquery table is large with exists, and the subquery table is small in:Example: Table A (small table), table B (large table) 1:SELECT * from A where CC in (select CC from B) is inefficient and uses the index of the CC column on table A; SELECT * FROM A whereexists (select cc from B where cc=a.cc) is highly efficient and uses the index of the CC column on table B. the opposite 2:SELECT * from B where cc in (select CC from A) is highly efficient and uses the index of the CC column on table B; SELECT * FROM B whereexists (select cc from A where cc=b.cc) is inefficient and uses the index of the CC column on table A. not in and not exists if the query statement uses Notin, then the internal surface is scanned in full table, the index is not used, and the sub-query of not extsts can still use the index on the table.
so no matter the table is big, using not exists is faster than Notin . difference between in and =select name from Student wherename in (' Zhang ', ' Wang ', ' Li ', ' Zhao ');with theselect name from Student wherename= ' Zhang ' or name= ' li ' or name= ' Wang ' orname= ' Zhao 'The result is the same.
MySQL in and exists efficiency
