SQL Server T-SQL 慎用 IN 和 NOT IN

大多的开发者在使用一些场景进行范围查询时，使用的是 IN 或者 NOT IN。然而，这样使用 SQL 很可能存在隐患，也可能导致查询结果不准确。下面我们进行一些测试。

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、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表没有用到。

上面第二个查询，实际相当于如下SQL，表示 father中每行的fid与oid比较，相同则返回结果。

select * from father where fid in(select 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，所以谓词fid not in(2,3,null)只返回not true 或not unknown，结果都不会是true。所以当子查询存在null时，not in和not exists 在逻辑上是不等价的。

In 或 Not In 在 SQL 语句中经常用到，尤其当子查询中有空值的时候，要谨慎考虑。因为即使写了“正确”的脚本，但是返回结果却不正确，也不出错。在不是很理解的情况下，最好使用 exists 和 not exists 来替换。而且 exists 查询更快一些，因为只要在子查询找到第一个符合的值就不继续往下找了，所以能用 exists 就用吧。

select * from father a where exists(select 1 from son b where a.fid=b.fid)
select * from father a where not exists(select 1 from son b where a.fid=b.fid)