在sql語句中替換Not In 的方法

前言：

今天在寫一條sql查詢語句，其需要從一個表A中返回所有A不再表B中的結果集，當然，這種實現最方便的方法就是用NOT IN。

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

[@more@]

我們大家都知道很多sql方面的文章都建議大家儘量不要使用NOT IN的方法，因為這種方法的效率不高。那有沒有替代的辦法呢？（宣告因為當時的情況要求不能使用儲存過程，所以只有寫sql語句）和同事實驗了一下，結果用以下方法實現了。

目的：

替換NOT IN 方法。

說明：

在單條SQL語句中，不使用儲存過程，不使用臨時表。使用儲存過程和臨時表不再本文的討論範圍中。

實現：

例：

表aa：結構

1 a

2 b

3 c

4 d

5 e

6 f

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

表bb:結構

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