NOT IN的奇怪行为

Question

我有一张桌子：

select count(distinct clean_deep_link) from tbl_1;
+---------------------------------+
| count(distinct clean_deep_link) |
+---------------------------------+
|                          121211 |
+---------------------------------+

我有这样的疑问：

select count(1) from tbl_1 where clean_deep_link IN
  (select clean_deep_link from tbl_2);

+----------+
| count(1) |
+----------+
|    66360 |
+----------+

但当我将查询更改为not in时，它会返回一个空集：

select count(1) from tbl_1
where clean_deep_link not in (select clean_deep_link from tbl_2);
+----------+
| count(1) |
+----------+
|        0 |
+----------+

这怎麽可能？如果子查询包含大约一半的记录，那么子查询的not不应该包含另一半记录吗？这里我漏掉了什么？

谢谢

Answer 1

我假设其余行的tbl_1.clean_deep_link为NULL。

这些值既不是IN，也不是子查询的NOT IN。

另一个原因可能是，在tbl_2.clean_deep_link中有NULL。

请尝试以下操作：

select count(1) from tbl_1
where clean_deep_link not in (select clean_deep_link
                              from tbl_2 WHERE clean_deep_link IS NOT NULL);

NULL的问题在于它既不是=，也不是<>任何其他值(包括NULL)。

在检查NOT IN时，MySQL需要检查tbl_1中的每个值是否不包含在tbl_2中，从而检查它们是否为<>。

你的值是而不是 <> NULL，所以它们是而不是 NOT IN。

另请参阅：Using NOT IN operator with null values

检查SQL Fiddle中的示例。

Answer 2

MySQL中的NULL列被视为不同的，因此三个NULL值都被认为是不同的。

正如前面提到的，您不能使用传统的比较运算符(包括IN和NOT IN )将NULL与其他值进行比较。

以下运算符可以处理NULL值

x <=> y - returns 1 if x == y (even if both are NULL)
x IS NULL - returns 1 if x is null
x IS NOT NULL - returns 0 if x is null

Answer 3

SELECT COUNT(*) - COUNT(1) 
FROM tbl_1 
WHERE clean_deep_link IN (
    SELECT clean_deep_link 
    FROM tbl_2
);