在sql语句中替换Not In 的方法

在sql语句中替换Not In 的方法

 

前言：

今天在写一条sql查询语句，其需要从一个表A中返回所有A不再表B中的结果集，当然，这种实现最方便的方法就是用NOT IN。

如：select a.* from a where a.id not in (select id from b where…..)

我们大家都知道很多sql方面的文章都建议大家尽量不要使用NOT IN的方法，因为这种方法的效率不高。那有没有替代的办法呢？（声明因为当时的情况要求不能使用储存过程，所以只有写sql语句）和同事实验了一下，结果用以下方法实现了。

 

目的：

替换NOT IN 方法。

说明：

在单条SQL语句中，不使用储存过程，不使用临时表。使用存储过程和临时表不再本文的讨论范围中。

实现：

例：

表aa：结构

id                                       value   ……

1                                        a

2                                            b

3                                            c

4                                            d

5                                            e

6                                            f

---------------------------------------------------------------------------------------------

 

表bb:结构

id    ……

2

4

6

 

现在我要取表aa里的所有字段，条件是aa的id值不在bb的id值当中（not in）。也就是应该返回所有id为奇数的字段

 

使用NOT IN的SQL:

select * from aa where id not in(select id from bb)

就一条语句，简单明了，可惜效率不高，而且公司规范要求尽量不用NOT IN，害我费了好大事crying……

 

改造后的SQL:

select cc.id,cc.value from (select aa.*,bb.id as tempcolum from aa left join bb on aa.id=bb.id) as cc where cc.tempcolum is null

 

解释一下。在开始时候我最早想用内联表的方式，可是无论如何也每找到一个好的办法，干脆就是实现不了。（大家有好办法指教先。）

后来自己考虑了一下NOT IN的逻辑，A NOT IN B就是说A是主体，B起到的之不过是一个判断作用，我们可以先把所有符合条件的A记录全部查询出来而不管他是否属于B，然后再从这里剔除值同时属于B的部分。

Select aa.* from aa

但是仅仅这样是不够的，我们无法利用这个返回的结果集判断是否属于B并排除它，为此，我想到构造一个临时的列，这个列的值应该是在A的结果集范围内，所有在B中的值。而这个结果集的主体应该是所有满足先决条件的A，然后加上满足条件的B，而不满足条件的B值则不再考虑范围内，所以用了left join。

这一段是关键，不知道我阐述清楚了没有，没明白的继续看

于是就出来这一句。

select aa.*,bb.id as tempcolum from aa left join bb on aa.id=bb.id

没看明白上面的看结果集就明白了

id  value        tempcolum

---------------------------------------------

1     a                NULL

2     b                2

3     c                NULL

4     d                4

5     e                NULL

6     f                6

 

看到这个结果集我想大家都明白我的意思了吧。对了，我们就是要对这个结果集进行二次操作。

相信大家都看到了，生成的这个结果集包含了所有符合条件的表aa字段和bb的id，如果aa中的值在bb中，则tempcolum的值就不会为null，如果不在就是null，这样我们只需要从这个结果集里查询所有tempcolum值为null的就可以满足我们的要求了

所以最终的sql出来了

select cc.id,cc.value from (select aa.*,bb.id as tempcolum from aa left join bb on aa.id=bb.id) as cc where cc.tempcolum is null

我们只需要id和value两个字段，其他的就不要了。

结果

id  value

-------------------------

1     a        

3     c        

5           e        

 

ok，实现了，希望对大家有帮助。

声明：

这个方法的效率我并没有测试，大家感兴趣的可以自己测一下，呵呵，看看是不是比NOT IN的方法好，如果不好的话，还不如用NOT IN呢。同时感谢和我一起实验的同事。

最后说一句，文笔不好，表述不清楚的地方请见谅.

/*科目信息 */
drop table subinfo
create table subinfo(
 Objid int identity(1,1) not null,
 Objname varchar(20) null,
 Objexplain varchar(50) null
)

insert into subinfo(Objname,Objexplain) values ('数学','计算，微机分')
insert into subinfo(Objname,Objexplain) values ('语文','文言文')
insert into subinfo(Objname,Objexplain) values ('地理','世界地理')
insert into subinfo(Objname,Objexplain) values ('历史','中国历史')

/*班级表 */
drop table classinfo
create table classinfo(
 Objid int identity(1,1) not null,
 Objname varchar(20) null,
 Objexplain varchar(255) null,
 Teacherid varchar(20) null,
)

insert into classinfo(Objname,Objexplain,Teacherid) values ('121','理科班','李老师')
insert into classinfo(Objname,Objexplain,Teacherid) values ('122','文科班','王老师')
insert into classinfo(Objname,Objexplain,Teacherid) values ('123','理科班','高老师')
insert into classinfo(Objname,Objexplain,Teacherid) values ('124','文科班','龙老师')
insert into classinfo(Objname,Objexplain,Teacherid) values ('124','文科班',NULL)

/*班级科目表 */
drop table classub
create table classub(
 Classid int not null,
 Subjectid int null,
 Teacherid varchar(20) null
)

insert into classub(Classid,Subjectid,Teacherid) values (2,1,'张老师')
insert into classub(Classid,Subjectid,Teacherid) values (3,2,'蒋老师')
insert into classub(Classid,Subjectid,Teacherid)values (1,3,'李老师')
insert into classub(Classid,Subjectid,Teacherid) values (2,4,'高老师')
insert into classub(Classid,Subjectid,Teacherid) values (4,1,'龙老师')
insert into classub(Classid,Subjectid,Teacherid) values (1,2,'王老师')

/*教师表 */
drop table teainfo
create table teainfo(

 Username varchar(20) null,
 Objjc varchar(50) null,
 Sex varchar(2) null
)

insert into teainfo(Username,Objjc,Sex) values ('李老师','语言老师，121 班主任','女')
insert into teainfo(Username,Objjc,Sex)values ('王老师','化学老师，122 班主任','男')
insert into teainfo(Username,Objjc,Sex) values ('高老师','数学老师，123 班主任','女')
insert into teainfo(Username,Objjc,Sex) values ('龙老师','物理老师，124 班主任','男')
insert into teainfo(Username,Objjc,Sex) values ('唐老师','历史老师','男')
insert into teainfo(Username,Objjc,Sex) values ('蒋老师','地理老师','女')
insert into teainfo(Username,Objjc,Sex) values ('罗老师','地理老师','女')
insert into teainfo(Username,Objjc,Sex) values ('张老师','地理老师','女')

 

/*学生表 */
drop table stuinfo
create table stuinfo(

 Username varchar(20) null,
 Objjc varchar(50) null,
 Sex varchar(2) null,
 Classid varchar(20) null
)

insert into stuinfo(Username,Objjc,Sex,Classid) values ('李明','文科','女','123')
insert into stuinfo(Username,Objjc,Sex,Classid)values ('王二','理科','男','121')
insert into stuinfo(Username,Objjc,Sex,Classid) values ('老三','文科','女','126')
insert into stuinfo(Username,Objjc,Sex,Classid) values ('李明','理科','男','124')
insert into stuinfo(Username,Objjc,Sex,Classid) values ('唐三','文科','男','125')
insert into stuinfo(Username,Objjc,Sex,Classid) values ('李四','理科','女','122')
insert into stuinfo(Username,Objjc,Sex,Classid) values ('张三','理科','男','122')

 

/*显示班级信息及班主任*/
select a.Objname as 学生班级,a.Objexplain as 学生科目,a.Teacherid as 任教老师,b.Objjc as 老师名称,b.Sex as 老师性别
from classinfo a
join teainfo b on a.Teacherid=b.Username

/*显示所有班级的科目和任课老师*/
select a.Objname as 科目 ,b.Teacherid as 任课老师 from subinfo a join classub b on a.Objid=b.Subjectid

/*显示所有教师信息*/
select Username as 名字,Objjc as 名称,Sex as 性别 From teainfo

/*显示所有学生信息和所在班级信息*/
select a.Username as 学生姓名,a.Objjc as 学生科目,a.Sex as 学生性别,b.Objname as 所在班级,b.Objexplain as 科目,b.Teacherid as 教师
from stuinfo a
join classinfo b on a.Classid=b.Objname

/*显示是班主任的教师*/
select ISNULL(Teacherid,0) AS 班主任 from classinfo where Teacherid<>'0'
select Teacherid AS 班主任 from classinfo where Teacherid<>'NULL'

/*显示是任课老师的教师*/
select Username from teainfo where Username not in (select isnull(Teacherid,0) from classinfo) and Username in (select Teacherid from classub)
/*没有用到NOT IN*/
select a.UserName,b.Teacherid,c.Teacherid from teainfo a left join classinfo b on b.Teacherid= a.Username
left join  classub c on  a.Username=c.Teacherid
where b.Teacherid is NULL and c.Teacherid is not NULL

 

/*显示出不是任课老师也不是班主任的老师*/
select Username from teainfo where Username not in (select Teacherid from classinfo) and Username not in (select Teacherid from classub)

 

/*求共有几个班级,几位教师,几位学生*/
select 班级=(select count(*) from  classinfo), 教师=(select count(*) from teainfo),学生=(select count(*) from stuinfo)

/*显示班级和班级里面的科目数量*/

/*显示班级里面的学生数量*/

select 班级=Classid,学生数量=sum(case 
   when Classid=Classid  then 1
   else 0
   end
  )
from stuinfo group by Classid