对于mysql中的字符串列类型，NOT IN未按预期工作

Question

我在NOT IN和IN之间发现了一些奇怪的东西，我使用了字符串类型的列。它适用于IN，但不适用于not IN。尽管这两种方法(非IN和IN)都适用于整型列

SELECT * FROM `table1` t1 where t1.email not in (select t2.email from `table2` t2)

不会从存在于t2中的t1返回任何结果，尽管它应该返回符合条件的记录，而

SELECT * FROM `table1` t1 where t1.email in (select t2.email from `table2` t2)

从t1返回t2格式的记录。

Answer 1

这里有一个简单的示例来解释这种现象，这种现象也出现在其他DBMS中，如SQL Server和Oracle。

假设

select t2.email from `table2` t2

返回

+-------------+
| email       |
+-------------+
| 111@abc.com |
| 222@abc.com |
| NULL        |
+-------------+

执行时

SELECT * FROM `table1` t1 where t1.email not in (select t2.email from `table2` t2)

MySQL将其翻译为

SELECT * FROM `table1` t1 
where 
t1.email <> '111@abc.com' AND t1.email <> '222@abc.com' AND t1.email <> NULL

where子句总是返回NULL，因为任何与NULL进行比较的值都会返回NULL，而任何布尔值对NULL执行AND操作也会返回NULL。因此，如上所示的整个SQL查询总是返回non record。

类似地，

SELECT * FROM `table1` t1 where t1.email in (select t2.email from `table2` t2)

将被翻译成

SELECT * FROM `table1` t1 
where 
t1.email = '111@abc.com' OR t1.email = '222@abc.com' OR t1.email <> NULL

t1.email <> NULL的值为NULL，如果任何其他比较为TRUE，则将忽略该值。

Answer 2

将查询修改为下面有效。

SELECT * 
  FROM table1 t1 
 WHERE t1.email NOT IN ( SELECT t2.email 
                           FROM table2 t2 
                          WHERE t2.email IS NOT NULL )

因为表2的电子邮件列值为NULL，有一行没有返回任何记录。

Answer 3

不要对子查询使用NOT IN。原因很简单:它不像人们期望的那样处理NULL值。

相反，只要习惯使用NOT EXISTS即可。

SELECT t1.*
FROM `table1` t1 
WHERE NOT EXISTS (SELECT 1
                  FROM `table2` t2
                  WHERE t1.email= t2.email 
                 ) ;

为什么这不能像预期的那样工作？这是因为SQL值是由NULL定义的。它们具有“未知”值的语义，而不是“缺失”值。

考虑以下情况：

• 1 IN (1, 2) --求值为true
• 3 IN (1, 2) --求值为false

NULL不会影响这一点，因为它是完全匹配的：

• 1 IN (1, 2, NULL) --求值为true
• 3 IN (1, 2, NULL) --求值为NULL，将被视为false

但是NULL会影响NOT IN。

• 1 NOT IN (1, 2, NULL) --求值为false
• 3 NOT IN (1, 2, NULL) --求值为NULL，因为NULL是“未知”的

关键是NULL没有特定的含义--它的意思是“未知”。因此，NULL可能等于3。因此，最后一个表达式的结果是“未知”(即NULL)而不是"true“。