首页 » MySQL, ORACLE, SQL Server » in, not in (null)

in, not in (null)

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

打赏

对不起,这篇文章暂时关闭评论。