mysql查詢條件not in 和 in的區別及原因說明

Posted on by

先寫一個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

 / \\

 2 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

為什麼是對的？留給大傢想想吧~

以上為個人經驗，希望能給大傢一個參考，也希望大傢多多支持WalkonNet。如有錯誤或未考慮完全的地方，望不吝賜教。

推薦閱讀：