[今日技術學習]sqlserver中not in 不好使得時候咋辦？

當使用大資料量進行 not in 比對的時候 sqlserver是不會給你資料結果

如：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