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

先说基本情况：

数据库：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”。原因自己想啊。

  

写法上：

当然是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吧！

 

 

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

转载于:https://www.cnblogs.com/linyongqin/articles/4424212.html