多個欄位的in 和 not in 及其替代寫法（exists，not exists）

首先聲明：資料庫高手請走開，您看了礙眼啊。<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

有些時候進行查詢的時候啊，一個欄位的in、 not in根本不能滿足要求，非常需要類似多個欄位的in、not in，但是很多資料庫不支援多個欄位的in、 not in （DB2是支援的）,估計也讓不少朋友們鬱悶吧!不過沒關係，我這不寫文章了嘛，呵呵。

我用過的資料庫有Access，SQL Server，DB2，見笑見笑啊。其實也就SQL Server，DB2這兩個還像點樣點兒，呵呵……，言歸正傳啊

首先聲明DB2是支援多個欄位 in、 not in的

 

先說基本情況：

資料庫：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><?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-com:office:smarttags" />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”。原因自己想啊。

 

 

ok，說完了，下面就幾個方面比較一下吧（雖然意義不是很大，呵呵）

寫法上：

當然是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吧。