开发者社区> 游客gsgxy6yg6ydlm> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

SQL查询:慎用 IN 和 NOT IN

简介: SQL查询:慎用 IN 和 NOT IN
+关注继续查看

今天突然想到之前在书上看到的一个例子,竟然想不起来了.

于是翻书找出来,测试一下.

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

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
SQL 查询横表变竖表
首先是三张表, CNo对应的是课程,在这里我就粘贴了。                                           主表                  人名表 按照常规查询 SELECT s.SName, c.CName,s2.SCgrade   FROM S s INNER JOIN SC s2 ON s2.SNo = s.SNo INNER JOIN C c ON c.CNo = s2.CNo 那么结果是这样的   但是这是横表 不是我想看到的结果。
808 0
sql 分页查询
引用:http://space.itpub.net/26273/viewspace-704021 初始定义: pageSize:每页显示大小 pageNum:第几页 Oracle分页: minus差分页: select * from table where rownum
772 0
asp.net sql server 中的存储过程的增加 查询 删除 更新
之前客户不推荐使用存储过程,由于业务比较复杂,为了提高性能,客户又推荐使用存储过程.于是就把以前整理的sql server 中的存储过程的增加 查询 删除 更新 找出来,重新温习了一下.现在贴出来 希望对大家有参考价值 下载:asp.
641 0
61
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载