先寫一個SQL
SELECT DISTINCT from_id
FROM cod
WHERE cod.from_id NOT IN (37, 56, 57)
今天在寫SQL的時候，發現這個查的結果不全，少了NULL值的情況，not in 的時候竟然把null也排除了
用 in 的時候卻沒有包含null
感覺是mysql設計的不合理
因為一直認為in 和 not in 正好應該互補才是，就像這樣查的應該是全部的一樣：
SELECT DISTINCT from_id
FROM cod
WHERE cod.from_id NOT IN (37, 56, 57) or cod.from_id IN (37, 56, 57)
結果正如猜測的那樣，少了個null
后來上網上查了下，有一個解釋挺合理的，即：
null與任何值比較都是false
比如from_id有(37, 56, 57,28,null)， not in (37, 56, 57)與28比較時是true，所以結果集中出現28，
null與not in (37, 56, 57)這個條件比較時，結果false，所以不出現在結果集中
補充：MySQL條件查詢IN和NOT IN左右兩側包含NULL值的處理方式
題目
給定一個表 tree，id 是樹節點的編號， p_id 是它父節點的 id 。
+----+------+
| id | p\_id |
+----+------+
| 1 | NULL |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 2 |
+----+------+
樹中每個節點屬于以下三種類型之一：
葉子：如果這個節點沒有任何孩子節點。
根：如果這個節點是整棵樹的根，即沒有父節點。
內部節點：如果這個節點既不是葉子節點也不是根節點。
寫一個查詢語句，輸出所有節點的編號和節點的類型，并將結果按照節點編號排序。上面樣例的結果為：
+----+------+
| id | TYPE |
+----+------+
| 1 | Root |
| 2 | INNER|
| 3 | Leaf |
| 4 | Leaf |
| 5 | Leaf |
+----+------+
解釋
節點 ‘1' 是根節點，因為它的父節點是 NULL ，同時它有孩子節點 ‘2' 和 ‘3' 。
節點 ‘2' 是內部節點，因為它有父節點 ‘1' ，也有孩子節點 ‘4' 和 ‘5' 。
節點 ‘3', ‘4' 和 ‘5' 都是葉子節點，因為它們都有父節點同時沒有孩子節點。
樣例中樹的形態如下：
首先先建表
1.建表
CREATE TABLE tree(
id INT ,
p_id INT
)
下面是我的做法：
SELECT id,(
CASE
WHEN tree.p_id IS NULL THEN 'Root'
WHEN tree.id NOT IN ( -- id不在父結點p_id列時，認為是葉子結點，邏輯上沒有問題！
SELECT p_id
FROM tree
GROUP BY p_id
) THEN 'Leaf'
ELSE 'Inner'
END
)TYPE
FROM tree
我覺得當id不在父結點p_id列時，認為是葉子結點，這在邏輯上完全沒有任何問題，然而事情并沒有這么簡單，查詢結果如下：從id=3開始沒有查到我想要的結果！神奇吧!
于是又過了一晚上，終于解決了問題，我先給出正確的做法：
SELECT id,(
CASE
WHEN tree.p_id IS NULL THEN 'Root'
WHEN tree.id NOT IN (
SELECT p_id
FROM tree
WHERE p_id IS NOT NULL -- 添加了一句SQL
GROUP BY p_id
) THEN 'Leaf'
ELSE 'Inner'
END
)TYPE
FROM tree
為什么會這樣呢？
我們都知道
MySQL 中的 IN 運算符用來判斷表達式的值是否位于給出的列表中；如果是，返回值為 1，否則返回值為 0。
NOT IN 的作用和 IN 恰好相反，NOT IN 用來判斷表達式的值是否不存在于給出的列表中；如果不是，返回值為 1，否則返回值為 0。
一般情況下我們都是這樣用的，結果也是我們想要的。但是下面的特殊情況我們卻經常遇到！
（1）in和not in左右兩側都沒有NULL值的情況
【實例1】在 SQL 語句中使用 IN 和 NOT IN 運算符：
mysql> SELECT 2 IN (1,3,5,'thks'),'thks' IN (1,3,5, 'thks');
+---------------------+---------------------------+
| 2 IN (1,3,5,'thks') | 'thks' IN (1,3,5, 'thks') |
+---------------------+---------------------------+
| 0 | 1 |
+---------------------+---------------------------+
1 row in set, 2 warnings (0.00 sec)
mysql> SELECT 2 NOT IN (1,3,5,'thks'),'thks' NOT IN (1,3,5, 'thks');
+-------------------------+-------------------------------+
| 2 NOT IN (1,3,5,'thks') | 'thks' NOT IN (1,3,5, 'thks') |
+-------------------------+-------------------------------+
| 1 | 0 |
+-------------------------+-------------------------------+
1 row in set, 2 warnings (0.00 sec)
由結果可以看到，IN 和 NOT IN 的返回值正好相反。
但是忽略了一個NULL值問題
對空值 NULL 的處理
當 IN 運算符的兩側有一個為空值 NULL 時，如果找不到匹配項，則返回值為 NULL；如果找到了匹配項，則返回值為 1。
（2）NULL值在in左右兩側
請看下面的 SQL 語句如下：
mysql> SELECT NULL IN (1,3,5,'thks'),10 IN (1,3,NULL,'thks');
+------------------------+-------------------------+
| NULL IN (1,3,5,'thks') | 10 IN (1,3,NULL,'thks') |
+------------------------+-------------------------+
| NULL | NULL |
+------------------------+-------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> SELECT NULL IN (1,3,5,'thks'),10 IN (1,10,NULL,'thks');
+------------------------+--------------------------+
| NULL IN (1,3,5,'thks') | 10 IN (1,10,NULL,'thks') |
+------------------------+--------------------------+
| NULL | 1 |
+------------------------+--------------------------+
1 row in set (0.00 sec)
（3）NULL在NOT IN 的其中一側
NOT IN 恰好相反，當 NOT IN 運算符的兩側有一個為空值 NULL 時，如果找不到匹配項，則返回值為 NULL；如果找到了匹配項，則返回值為 0。
請看下面的 SQL 語句如下：
mysql> SELECT NULL NOT IN (1,3,5,'thks'),10 NOT IN (1,0,NULL,'thks');
+----------------------------+-----------------------------+
| NULL NOT IN (1,3,5,'thks') | 10 NOT IN (1,0,NULL,'thks') |
+----------------------------+-----------------------------+
| NULL | NULL |
+----------------------------+-----------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> SELECT NULL NOT IN (1,3,5,'thks'),10 NOT IN (1,10,NULL,'thks');
+----------------------------+------------------------------+
| NULL NOT IN (1,3,5,'thks') | 10 NOT IN (1,10,NULL,'thks') |
+----------------------------+------------------------------+
| NULL | 0 |
+----------------------------+------------------------------+
1 row in set (0.00 sec)
根據（3）NULL在NOT IN 的其中一側的結果，這就可以看出問題
先來查詢下面SQL語句，慢慢發現問題
SELECT p_id
FROM tree
GROUP BY p_id
上面查詢結果包含了NULL值
所以查詢下面SQL語句就查不到任何東西，這是因為NOT IN返回了NULL
SELECT id
FROM tree
WHERE id NOT IN (
SELECT p_id
FROM tree
GROUP BY p_id
)
所以要想查詢出來結果就要先把NULL值給處理掉！好了，Bug搞定！
這題還有另外一種做法：
SELECT id,(
CASE
WHEN tree.p_id IS NULL THEN 'Root'
WHEN tree.id IN (
SELECT p_id
FROM tree
GROUP BY p_id
) THEN 'Inner'
ELSE 'Leaf'
END
)TYPE
FROM tree
為什么是對的？留給大家想想吧~
以上為個人經驗，希望能給大家一個參考，也希望大家多多支持腳本之家。如有錯誤或未考慮完全的地方，望不吝賜教。
您可能感興趣的文章:- MySQL全面瓦解之查詢的過濾條件詳解
- mysql條件查詢and or使用方法及優先級實例分析
- 詳解Mysql查詢條件中字符串尾部有空格也能匹配上的問題
- MySQL查詢條件常見用法詳解
- MySQL查詢條件中in會用到索引嗎
- mysql 帶多個條件的查詢方式
