多个字段的in 和 not in 及其替代写法（exists，not exists）

数据库知识参考：http://dev.mysql.com/doc/refman/5.1/zh/sql-syntax.html#insert

转自：http://blog.csdn.net/moklee/article/details/3730282

首先声明：数据库高手请走开，您看了碍眼啊！

有些时候进行查询的时候啊，一个字段的in、 not in根本不能满足要求，非常需要类似多个字段的in、not in，但是很多数据库不支持多个字段的in、 not in （DB2是支持的）,估计也让不少朋友们郁闷吧!不过没关系，我这不写文章了嘛，呵呵！

我用过的数据库有Access，SQL Server，DB2，见笑见笑啊！其实也就SQL Server，DB2这两个还像点样点儿，呵呵……，言归正传啊

首先声明DB2是支持多个字段 in、 not in的

 

先说基本情况：

数据库：DB2 8.2，SQL Server 2005 Express

表a  有字段：aaa，bbb，还可能有其他字段。记录条数：3764

表b  有字段：aaa，bbb，还可能有其他字段。记录条数：4127

够明显了吧，就是表a的字段aaa跟表b的字段aaa有对应关系，就是表a字段的bbb跟表b的字段bbb有对应关系。

但是只有aaa，bbb两个字段都同时对应上了才算是真的对应上了。（也不知道我说的清不清楚，理解万岁啊）

 

好了，开始正文：

 

1.       先说“in”。

从表b里查询出满足条件“select aaa,bbb from a”的记录：

如下语句就是我们想要的结果：

select * from b where (aaa,bbb)  in ( select aaa,bbb from a );

不过很可惜，上面的语句只能再DB2上执行，SQL Server不行。（其他数据库没有试过，不知道啊！）

还好可以用下面的语句来代替

select * from b where  exists ( select * from a where a.aaa=b.aaa and a.bbb=b.bbb);

 

当然你可能会说我的条件是“select aaa,bbb from a where 表a某字段1='...' and 表a某字段2>1111” 什么等等，我就权且用“查询条件A”代表了

即：查询条件A = 表a某字段1='...' and 表a某字段2>1111

那语句就该这么写了

select * from b where (aaa,bbb)  in ( select aaa,bbb from a where 查询条件A);

select * from b where  exists ( select * from a where a.aaa=b.aaa and a.bbb=b.bbb and 查询条件A);

用exists时，最好把“查询条件A”中的“表a某字段1”之类写为“a.表a某字段1”。原因自己想啊。

 

2.       再说“not in”。基本和“in”一样，我就直接复制过来了，偷个懒啊

从表b里查询出不在结果集“select aaa,bbb from a”中的记录：

如下语句就是我们想要的结果：

select * from b where (aaa,bbb) not in ( select aaa,bbb from a );

不过很可惜，上面的语句只能再DB2上执行，SQL Server不行。（其他数据库没有试过，不知道啊！）

还好可以用下面的语句来代替

select * from b where not exists ( select * from a where a.aaa=b.aaa and a.bbb=b.bbb);

 

当然你可能会说我的条件是“select aaa,bbb from a where 表a某字段1='...' and 表a某字段2>1111” 什么等等，我就权且用“查询条件A”代表了

即：查询条件A = 表a某字段1='...' and 表a某字段2>1111

那语句就该这么写了

select * from b where (aaa,bbb) not in ( select aaa,bbb from a where 查询条件A);

select * from b where not exists ( select * from a where a.aaa=b.aaa and a.bbb=b.bbb and 查询条件A);

用not exists时，最好把“查询条件A”中的“表a某字段1”之类写为“a.表a某字段1”。原因自己想啊。

  

ok，说完了，下面就几个方面比较一下吧（虽然意义不是很大，呵呵）

写法上：

当然是in、not in最直观了（地球人都知道）。

 

 

再说效率问题(仅限DB2，原因不用说了吧)：

in效率比exists高

not exists效率比not in高

具体执行时间如下

in      0.01 secs

exists  0.03 secs

not in      8.62 secs

not exists  0.03 secs

 

总结：

       多字段in、not in在db2数据中可以执行，SQL Server不行。（其他数据库没有试过，不知道！）

       exists、not exists在db2，SQL Server均可执行。（其他数据库没有试过，不知道！）

       而且总体上用exists，not exists 效率都很高，建议大家还是用好exists，not exists吧！