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

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

-- 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

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)

阐明：

两个查询都履行没有犯错，可是第二个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的处理方式类似。因而榜首个子查询回来了正确的成果集。

【测验二：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)

阐明：

查看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就用吧。