Article / 文章中心

SQL查询:慎用 IN 和 NOT IN

发布时间:2022-02-14 点击数:108

今天忽然想到之前在书上看到的一个比如,竟然想不起来了.

所以翻书找出来,测验一下.

-- drop table father,son
create table father(fid int,name varchar(10),oid int)
create table son(sid int,name varchar(10),fid int)


insert into father(fid,name,oid)
values(1,'father',5),(2,'father',9),(3,'father',null),(4,'father',0)

insert into son(sid,name,fid)
values(1,'son',2),(2,'son',2),(3,'son',3),(4,'son',null),(5,'son',null)

select * from father
select * from son

image.png

in和exists差异开端测验吧,现在测验运用in、not in 或许带来的“过错”。之所以过错,是由于咱们总是以自然语言去了解SQL,却忽略了数学中的逻辑语法。不废话了,测验看看吧!

【测验一:in子查询】

--回来在son中存在的一切father的数据

--正确的写法:
select * from father where fid in(select fid from son)

--过错的写法:
select * from father where fid in(select oid from son)

image.png

阐明:

两个查询都履行没有犯错,可是第二个tsql的子查询写错了。子查询(select oid from son)实践单独履行会犯错,由于表son不存在字段oid,可是在这里体系不会提示过错。而且father表有4行数据,一切子查询扫描了4次son表,可是第二个查询中,实践也只扫描了1次son表,也便是son表没有用到。

即便这样写也 不会犯错:select*fromfatherwherefidin(selectoid)

这个查询的意思是,表father中每行的fid与oid比较,相同则回来值。

实践查询是这样的:select * from father where fid = oid

测验一中,fid in(select fid from son)子查询中包含null值,所以 fid  in(null)回来的是一个未知值。可是在刷选器中,false和unknown的处理方式类似。因而榜首个子查询回来了正确的成果集。

image.png

【测验二:not  in子查询】

--回来在son中不存在的一切father的数据

--过错的写法:
select * from father where fid not in(select fid from son)

--过错的写法:
select * from father where fid not in(select oid from son)

--正确的写法:
select * from father where fid not in(select fid from son where fid is not null)

image.png

阐明:

查看select fid from son,子查询中有空值null,子查询中的值为(2,3,null),谓词fid in(2,3,null)永远不会回来false,只反会true或unknown,所以谓词fidnot in(2,3,null)只回来not true 或not unknown,成果都不会是true。所以当子查询存在null时,not in和not exists 在逻辑上是不等价的。

总结

In 或 not in在SQL语句中经常用到,尤其当子查询中有空值的时候,要慎重考虑。由于即便写了“正确”的脚本,可是回来成果却不正确,也不犯错。

在不是很了解的情况下,最好运用 exists和 not exists来替换。而且exists查询更快一些,由于只要在子查询找到榜首个契合的值就不继续往下找了,所以能用exists就用吧。