快捷導(dǎo)航

mysql查詢條件not in 和 in的區(qū)別及原因說(shuō)明

更新時(shí)間：2021年01月01日 14:38:53 作者：卡卡西sensi

這篇文章主要介紹了mysql查詢條件not in 和 in的區(qū)別及原因說(shuō)明，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧

先寫一個(gè)SQL

SELECT DISTINCT from_id
FROM cod
WHERE cod.from_id NOT IN (37, 56, 57)

今天在寫SQL的時(shí)候，發(fā)現(xiàn)這個(gè)查的結(jié)果不全，少了NULL值的情況，not in 的時(shí)候竟然把null也排除了

用 in 的時(shí)候卻沒(méi)有包含null

感覺(jué)是mysql設(shè)計(jì)的不合理

因?yàn)橐恢闭J(rèn)為in 和 not in 正好應(yīng)該互補(bǔ)才是，就像這樣查的應(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é)果正如猜測(cè)的那樣，少了個(gè)null

后來(lái)上網(wǎng)上查了下，有一個(gè)解釋挺合理的，即：

null與任何值比較都是false

比如from_id有(37, 56, 57,28,null)， not in (37, 56, 57)與28比較時(shí)是true，所以結(jié)果集中出現(xiàn)28，

null與not in (37, 56, 57)這個(gè)條件比較時(shí)，結(jié)果false，所以不出現(xiàn)在結(jié)果集中

補(bǔ)充：MySQL條件查詢IN和NOT IN左右兩側(cè)包含NULL值的處理方式

題目

給定一個(gè)表 tree，id 是樹(shù)節(jié)點(diǎn)的編號(hào)， p_id 是它父節(jié)點(diǎn)的 id 。

+----+------+

| id | p\_id |

+----+------+

| 1 | NULL |

| 2 | 1 |

| 3 | 1 |

| 4 | 2 |

| 5 | 2 |

+----+------+

樹(shù)中每個(gè)節(jié)點(diǎn)屬于以下三種類型之一：

葉子：如果這個(gè)節(jié)點(diǎn)沒(méi)有任何孩子節(jié)點(diǎn)。

根：如果這個(gè)節(jié)點(diǎn)是整棵樹(shù)的根，即沒(méi)有父節(jié)點(diǎn)。

內(nèi)部節(jié)點(diǎn)：如果這個(gè)節(jié)點(diǎn)既不是葉子節(jié)點(diǎn)也不是根節(jié)點(diǎn)。

寫一個(gè)查詢語(yǔ)句，輸出所有節(jié)點(diǎn)的編號(hào)和節(jié)點(diǎn)的類型，并將結(jié)果按照節(jié)點(diǎn)編號(hào)排序。上面樣例的結(jié)果為：

+----+------+

| id | TYPE |

+----+------+

| 1 | Root |

| 2 | INNER|

| 3 | Leaf |

| 4 | Leaf |

| 5 | Leaf |

+----+------+

解釋

節(jié)點(diǎn) ‘1' 是根節(jié)點(diǎn)，因?yàn)樗母腹?jié)點(diǎn)是 NULL ，同時(shí)它有孩子節(jié)點(diǎn) ‘2' 和 ‘3' 。

節(jié)點(diǎn) ‘2' 是內(nèi)部節(jié)點(diǎn)，因?yàn)樗懈腹?jié)點(diǎn) ‘1' ，也有孩子節(jié)點(diǎn) ‘4' 和 ‘5' 。

節(jié)點(diǎn) ‘3', ‘4' 和 ‘5' 都是葉子節(jié)點(diǎn)，因?yàn)樗鼈兌加懈腹?jié)點(diǎn)同時(shí)沒(méi)有孩子節(jié)點(diǎn)。

樣例中樹(shù)的形態(tài)如下：

 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不在父結(jié)點(diǎn)p_id列時(shí)，認(rèn)為是葉子結(jié)點(diǎn)，邏輯上沒(méi)有問(wèn)題！
 SELECT p_id
 FROM tree
 GROUP BY p_id
 ) THEN 'Leaf'
 ELSE 'Inner'
END
)TYPE
FROM tree

我覺(jué)得當(dāng)id不在父結(jié)點(diǎn)p_id列時(shí)，認(rèn)為是葉子結(jié)點(diǎn)，這在邏輯上完全沒(méi)有任何問(wèn)題，然而事情并沒(méi)有這么簡(jiǎn)單，查詢結(jié)果如下：從id=3開(kāi)始沒(méi)有查到我想要的結(jié)果！神奇吧!

于是又過(guò)了一晚上，終于解決了問(wèn)題，我先給出正確的做法：

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

為什么會(huì)這樣呢？

我們都知道

MySQL 中的 IN 運(yùn)算符用來(lái)判斷表達(dá)式的值是否位于給出的列表中；如果是，返回值為 1，否則返回值為 0。

NOT IN 的作用和 IN 恰好相反，NOT IN 用來(lái)判斷表達(dá)式的值是否不存在于給出的列表中；如果不是，返回值為 1，否則返回值為 0。

一般情況下我們都是這樣用的，結(jié)果也是我們想要的。但是下面的特殊情況我們卻經(jīng)常遇到！

（1）in和not in左右兩側(cè)都沒(méi)有NULL值的情況

【實(shí)例1】在 SQL 語(yǔ)句中使用 IN 和 NOT IN 運(yùn)算符：

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 的返回值正好相反。

但是忽略了一個(gè)NULL值問(wèn)題

對(duì)空值 NULL 的處理

當(dāng) IN 運(yùn)算符的兩側(cè)有一個(gè)為空值 NULL 時(shí)，如果找不到匹配項(xiàng)，則返回值為 NULL；如果找到了匹配項(xiàng)，則返回值為 1。

（2）NULL值在in左右兩側(cè)

請(qǐng)看下面的 SQL 語(yǔ)句如下：

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 運(yùn)算符的兩側(cè)有一個(gè)為空值 NULL 時(shí)，如果找不到匹配項(xiàng)，則返回值為 NULL；如果找到了匹配項(xiàng)，則返回值為 0。

請(qǐng)看下面的 SQL 語(yǔ)句如下：

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é)果，這就可以看出問(wèn)題

先來(lái)查詢下面SQL語(yǔ)句，慢慢發(fā)現(xiàn)問(wèn)題

SELECT p_id
FROM tree
GROUP BY p_id

上面查詢結(jié)果包含了NULL值

所以查詢下面SQL語(yǔ)句就查不到任何東西，這是因?yàn)镹OT IN返回了NULL

SELECT id 
FROM tree
WHERE id NOT IN (
 SELECT p_id
 FROM tree
 GROUP BY p_id
 )

所以要想查詢出來(lái)結(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