在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
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
