今日同事忽然跑來問我，說資料庫發生一個很奇怪的事。
她在確認資料表A中有多少筆也存在資料表B中，因此就先用Select Count(*) From 資料表A Where id in (Select id From 資料表B)算出筆數。然後她再用 NOT IN 的方式Count出資料表A中不存在資料表B的筆數。兩個筆數相加應該要等於資料表A的總筆數。然而她卻發現NOT IN算出來的筆數是錯的，因此跑來問我。
我聽到關鍵字 NOT IN 跟 筆數錯誤 後心中就有個底了，後來果然被我猜中原因，那就是 NULL，因此寫這一篇文筆記一下。
如下圖，我建了Tb1跟Tb2兩張資料表，Tb1有兩個欄位是id跟name，Tb2也有兩個欄位是id跟Phone。我再兩張資料表各塞入了兩筆資料，但Tab2中的其中一筆id我故意塞入NULL值。
首先我先Select出Tb1中id存在於Tb2，下圖顯示有一筆資料id為A123456789，這是正確的。
接下來我們要Select出Tb1中id不存在於Tb2中的資料，所以理所當然直覺就用NOT IN來做。而我們直覺應該會是另一筆id是B123456789會被Select出來吧 ! 但由下圖顯示可以發現居然一筆也沒有。
換一個寫法改用not exists會發現正確取出B123456789資料了，Why?
其實問題就是出在Tb2中id有NULL值，NULL值是無法被比對的，它既不是白也不是黑，就是處於灰色地帶。而當你知道NOT IN實際運作方式後就知道為何會Select出錯誤資料了。
當一句Where id not in('A123456789','B123456789')執行時其實就是跟 Where id<>'A123456789' and id<>'B123456789' 是一樣的。而用and就是要所有條件都是True才會是True，只要有一個條件不成立就會是False。因此當比對的欄位有NULL時(例 : Where id<>'A123456789' and id<>NULL)，而NULL是不能比較的，因此id<>NULL會判斷是False。一但有一條件是回傳False，那整個Where條件式就是False了，因此這一筆資料就會被認為是不符合的。
既然知道是NULL造成，那就好辦。只要您知道比對欄位中會有NULL值，那就先將NULL直轉為空白或是直接排除NULL資料，如下兩張圖所示，我們就可以正確Select出id為B123456789這一筆資料。
然而你認為只有NOT IN會受資料有NULL的影響嗎 ? NO ! 其實只要有對欄位做比較都會受影響，如下圖所示我只是要取出id不為A123456789的資料而已，但回傳結果卻是一筆也沒有。
如下圖，改個寫法就能取出id不為A123456789的資料了。
以前常常聽一些大師們說欄位盡量設成NOT NULL，但我不懂其影響層面。吃一次虧就會知道當時老師們的用意了。
這一篇對岸的文寫得更詳盡，想多深入了解的可以去看看http://blog.csdn.net/tianlesoftware/article/details/6817100
我是ROCK
rockchang@mails.fju.edu.tw
