The difference between in and not in,exists vs. exists in SQL

1, in and exists

In 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, has always been considered exists than in the high efficiency of the argument is inaccurate. If the query of two table size is equal, then with in and exists difference is not small, if two tables in a smaller one larger, then the subquery table large with exists, sub-query table small with in;

Example: Table A (small table), table B (large table)

SELECT * from a Where CC in (select CC from B)-low efficiency, using the index of the CC column on the A-table; select * from a where exists (select cc from B where cc=a.cc)--high efficiency, using the index of the CC column on the B table.

On the contrary:

SELECT * from B where cc in (select CC from a)-high efficiency, use the index of the CC column on the B table select * from B where exists (select CC from a where C c=b.cc)-Low efficiency, using the index of the CC column on the A-table.

2, not in and not exists

Not in logic is not exactly the same as not exists, if you misuse not in, be careful of your program has a fatal bug, see the following example:

CREATE TABLE #t1 (C1 int,c2 int), CREATE TABLE #t2 (C1 int,c2 int), insert into #t1 values, insert into #t1 values (1,3); SERT into #t2 values, insert into #t2 values (1,null);　　SELECT * from #t1 where C2 not in (select C2 from #t2); --Execution Result: no select * from #t1 where NOT exists (select 1 from #t2 where #t2. c2= #t1. C2)-Results: 1 3

As you can see, there is a logic error in the not-in-expected result set. If you look at the execution plan for the two SELECT statements above, the latter uses Hash_aj, so try not to use not-in (which calls the subquery), but instead use not EXISTS (which invokes the associated subquery). If any one of the records returned in the subquery contains a null value, the query will not return any records. If the subquery field has a non-empty limit, then you can use not in, and you can connect it with Hasg_aj or merge_aj by prompting it.

If the query statement uses not-in, then a full table scan is performed on the inner surface, and no index is used, whereas the exists of not is still used for indexes on the table. So no matter which table is large, using not exists is faster than not.

3, IN and = Difference

Select name from student where name in (' Zhang ', ' Wang ', ' Zhao ');

And

Select name from student where Name= ' Zhang ' or name= ' Wang ' or name= ' Zhao '

The result is the same.

Original address: http://www.cnblogs.com/seasons1987/archive/2013/07/03/3169356.html

The difference between in and not in,exists vs. exists in SQL