in,exists和not in, not exists

1. in和exists
IN 操作符允许我们在 WHERE 子句中规定多个值。
SQL IN 语法：

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...)

in 是把外表和内表作hash 连接，而exists是对外表（主表）作loop循环，每次loop循环再对内表进行查询。认为exists比in效率高的说法是不准确的，要看条件。
如果查询的两个表大小相当，那么用in和exists差别不大。 
如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in。

1.1 exist原理
在SQL中使用exists表示判断子查询是否存在记录，它返回的是true或false字段。如果有记录返回，则返回值，否则返回为false。完全不用关系返回的结果具体是什么。
exists做为where 条件时，是先对where 前的主查询询进行查询，然后用主查询的结果一个一个的代入exists的查询进行判断，如果为真则输出当前这一条主查询的结果，否则不输出。

exists : 强调的是是否返回结果集，不要求知道返回什么, 比如：

select name from student where sex = 'm' and mark exists(select 1 from grade where ...) 

,只要exists引导的子句有结果集返回，那么exists这个条件就算成立了,大家注意返回的字段始终为1，如果改成“select 2 from grade where ...”，那么返回的字段就是2，这个数字没有意义。所以exists子句不在乎返回什么，而是在乎是不是有结果集返回。

在查询时，一般情况下，子查询会分成两种情况：
情况A: 子查询与外表的字段有关系时

select 字段1 , 字段2 from 表1 where exists (select 字段1 , 字段2 from 表2 where 表2.字段2 = 表1.字段2)

这时候，此SQL语句相当于一个关联查询。
它先执行表1的查询，然后把表1中的每一条记录放到表2的条件中去查询，如果存在，则显示此条记录。

情况B：子查询与外表的字段没有任何关联的情况。

Select 字段1 , 字段2 from 表1 where exists ( select * from 表2 where 表2.字段 = ‘ 条件‘)

在这种情况下，只要子查询的条件成立，就会查询出表1中的所有记录，反之，如果子查询中没有查询到记录，则表1不会查询出任何的记录。

参考：
http://blog.csdn.net/hollboy/article/details/8056292
http://www.cnblogs.com/mytechblog/articles/2105785.html

2. not in 和not exists
如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引；而not exists 的子查询依然能用到表上的索引。所以无论哪个表大，用not exists都比not in要快。

注意：not in 逻辑上不完全等同于not exists，如果你误用了not in，小心你的程序存在致命的BUG。尽量不要使用not in(它会调用子查询)，而尽量使用not exists(它会调用关联子查询)。如果子查询中返回的任意一条记录含有空值，则查询将不返回任何记录。

3. in和or
or的效率为O(n)，而in的效率为O(logn).
在给in和or的效率下定义的时候，应该再加上一个条件，就是所在的列是否有索引或者是否是主键。
(1)如果有索引或者主键性能没啥差别，
(2)如果没有索引，in比or的效率甚至要高几个数量级！