in,exists和not in, not exists

[b]1. in和exists[/b]
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。

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

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子句不在乎返回什么，而是在乎是不是有结果集返回。

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

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

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

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

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

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

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

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

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

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