今天有人提起了 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