SQL：NOT IN与NOT EXISTS不等价

在对SQL语句进行性能优化时，经常用到一个技巧是将IN改写成EXISTS，这是等价改写，并没有什么问题。问题在于，将NOT IN改写成NOT EXISTS时，结果未必一样。

一、举例验证

例如，有如下一张表 rr 。要求：选择4月2号的数据，并且其type1是4月1号没有的（从表看，就是4月2号C的那条）。
在这里插入图片描述

使用NOT IN ，单纯按照这个条件去实现

select * from rr 
where create_date='2024-04-02'and type1 not in (select type1 from rr where create_date='2024-04-01')
;

在这里插入图片描述

使用NOT EXISTS

select r1.* from rr as r1
where r1.create_date='2024-04-02'and not exists (select r2.type1 from rr as r2 where r2.create_date='2024-04-01' and r1.type1=r2.type1)
;

在这里插入图片描述

主要原因是4月1号的数据中，存在type1为NULL的。如果该type1不是NULL，使用NOT IN就可以正确找出来结果了。

其中的原理涉及三值逻辑。

二、三值逻辑简述

以下的式子都会被判为unknown
1、 = NULL
2、> NULL
3、< NULL
4、<> NULL
NULL = NULL

unknown，它是因关系数据库采用了NULL而被引入的“第三个真值”。
（这里还有一点需要注意：真值unknown和作为NULL的一种UNKNOWN（未知）是不同的东西。前者是明确的布尔类型的真值，后者既不是值也不是变量。为了便于区分，前者采用粗体小写字母unknown，后者用普通的大写字母UNKNOWN表示。）

加上true和false，这三个真值之间有下面这样的优先级顺序。

AND 的情况：false > unknown > true
OR 的情况：true > unknown > false

下面看具体例子，连同unknown一起理解下
在这里插入图片描述

三、附录：用到的SQL

（运行环境Mysql）
1、表 rr 的构建

-- 使用了with语句
with rr as (
select '2024-04-01' as create_date,'A' as type1,001 as code1union all select '2024-04-01' as create_date,'A' as type1,002 as code1union all select '2024-04-01' as create_date,'A' as type1,002 as code1union all select '2024-04-01' as create_date,'B' as type1,013 as code1union all select '2024-04-01' as create_date,null as type1,013 as code1union all select '2024-04-02' as create_date,'B' as type1,013 as code1union all select '2024-04-02' as create_date,'C' as type1,109 as code1union all select '2024-04-03' as create_date,'A' as type1,002 as code1union all select '2024-04-04' as create_date,'A' as type1,002 as code1
)

2、 unknown的理解

set @a:=2, @b:=5, @c:= NULL ;select @a+@b as result1,case when (@b>@c) is true then 'true!'when (@b>@c) is false then 'false!'else 'unknown'	end as result2, -- 与NULL比较		 case when (@a<@b and @b>@c) is true then 'true!'when (@a<@b and @b>@c) is false then 'false!'else 'unknown'	end as result3, -- and条件下 的优先级展示case when (@a<@b or @b>@c) is true then 'true!'when (@a<@b or @b>@c) is false then 'false!'else 'unknown'	end as result4, -- or条件下 的优先级展示case when (not(@b<>@c)) is true then 'true!'when (not(@b<>@c)) is false then 'false!'else 'unknown'	end as result5