ANBOB™

今天有人提起了 in 与not in 的数据集里如果有null 值的情况下返回数据，not in的怪现象，其实这不是not in 的bug,在sqlserver,mysql,oracle都一样
下面看这个例子

 
create table testa(id int,name varchar(10));
insert into testa values(1,'anbob.com');
insert into testa values(2,'weejar');
insert into testa values(3,null);
create table testb(id int,name varchar(10));
insert into testb values(1,'anbob.com');
insert into testb values(2,'weejar');
insert into testb values(3,null);

select * from testa where name in(select name from testb);
select * from testa where name not in(select name from testb);

SQL> select * from testa;

ID NAME
---------- ----------
1 anbob.com
2 weejar
3

SQL> select * from testb;

ID NAME
---------- ----------
1 anbob.com
2 weejar
3

SQL> select * from testa where name in(select name from stb);

ID NAME
---------- ----------
1 anbob.com
2 weejar

SQL> select * from testa where name not in(select name from testb);

no rows selected

SQL> select * from testa a where not exists(select null from testb b where a.name=b.name);                                                                  
 
        ID NAME
---------- ----------
         3

为什么一条都没有呢？id 1,2不都有么？ 原理是这样的，col in (1,2,null)其实是这么比较
where col=1 or col=2 or col=3 是或的关系，而col not in（1，2，null)是
where col<>1 and col<>2 and col<>null
col<>null 比较会返回unknow ，并且是与的关系，如果有一个条件为nuknow 所以整个条件都为false,
条件比对为true,false,unknow三种情况，所以当用not in 是一定要注意null的存在，可以用not exists替换not in

打赏

微信扫一扫，打赏作者吧～