SQL Tech Insider -10 in and exists performance comparison

In and Existsin is the appearance and the inner table as a hash connection, and exists is the external loop loop, each loop loop and then query the internal table. The assertion that exists is more efficient than in is inaccurate. 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, one is a large table, then the subquery table is large with exists, and the subquery table is small in: For example: Table A (small table), table B (large table) 1:select * from A where CC in (select CC from B) is inefficient, The index of the CC column on table A is used; the select * from A where exists (select cc from B where cc=a.cc) is 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 where exists (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 not-in to perform a full-table scan of the outer surface, the index is not used, and the index on the table is still used by not Extsts's subquery. So no matter the table is large, using not exists is faster than not. The difference between in and = select name from student where name in (' Zhang ', ' Wang ', ' Li ', ' Zhao '); with select name from student where Name= ' Zhang ' Or name= ' li ' or name= ' Wang ' or name= ' Zhao ' results are the same.

　　

SQL Tech Insider -10 in and exists performance comparison