SQL避坑：当in，not in遇上null这种坑你避过吗？_not in null

作者：Guff_9hys | 2024-07-19 14:08:52

踩

not in null

0 数据准备

1 分析

案例1：当过滤条件中in里面包含null

案例2：当主表中有null，采用not in且not in后面不为子查询的时候会忽略NULL，匹配不到NULL，即查询不到任何NULL的记录

案例3：当not in中包含NULL

案例4：如果not in后面跟子查询时，所选的字段包含NULL值要特别注意

2 小结

0 数据准备


create table t as 
select '张三' as name,1 as class
union all
select '李四' as name,1 as class
union all
select '王五' as name,2 as class
union all
select '老六' as name,null as class
union all
select '陈七' as name, 4 as class
union all
select '赵八' as name, 5 as class
union all
select '孙九' as name, 5 as class

1 分析

原表数据如下：

案例1：当过滤条件中in里面包含null

select * from t where class in (null,'1')

in相当于or，对于null值得判断不是is null而是等于，因

本文内容由网友自发贡献，转载请注明出处：https://www.wpsshop.cn/w/Guff_9hys/article/detail/851914