在对SQL语句进行性能优化时,经常用到一个技巧是将IN改写成EXISTS,这是等价改写,并没有什么问题。问题在于,将NOT IN改写成NOT EXISTS时,结果未必一样。
执行环境:MySQL
一、举例验证
例如,有如下一张表 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 code1
union all select '2024-04-01' as create_date,'A' as type1,002 as code1
union all select '2024-04-01' as create_date,'A' as type1,002 as code1
union all select '2024-04-01' as create_date,'B' as type1,013 as code1
union all select '2024-04-01' as create_date,null as type1,013 as code1
union all select '2024-04-02' as create_date,'B' as type1,013 as code1
union all select '2024-04-02' as create_date,'C' as type1,109 as code1
union all select '2024-04-03' as create_date,'A' as type1,002 as code1
union 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