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