SQL语句中exists和not exists的用法(常用于过滤)

1.介绍

exists和in都有过滤功能,他俩最大的差别就是in引导的子句只能对一个字段进行限制,比如

/对id字段进行限定
select * from A where  sid in (1,2,3)

但是如果我们想对多个字段进行限制,使用in就不合适了,例如

select * from A where (sid,tid) in (select sid,tid from B)
不过很可惜，上面的语句只能再DB2上执行，SQL Server不行

此时就可以使用exists 来对多个字段进行限制了

select * from A where exists (select 1 from B where A.sid=B.sid and A.tid=B.tid)

2.原理

exists做为where条件时,是先对where 前的主查询询进行查询,然后用主查询的结果一个一个的代入exists的查询进行判断，如果为真则输出当前这一条主查询的结果，否则不输出。

查询时，一般情况下，子查询会分成两种情况:

1.子查询与外表的字段有关系时

select * from A where exists (select 1 from B where A.sid=B.sid and A.tid=B.tid)

它先执行A表的查询,再将查询结果一条一条放到B表的条件中去查询,如果存在,则显示此条

2.子查询与外表的字段没有任何关联

select * from A where exists (select * from B where B.id=‘条件‘)

在这种情况下，只要子查询的条件成立，就会查询出表1中的所有记录，反之，如果子查询中没有查询到记录，则表1不会查询出任何的记录。

以上两种方式本质上都是对A表查询进行过滤

3.业务涉及

在阶数核查时,我们首先插入了一阶邻区关系,此后我们在插入二阶邻区关系时,要先做判断

• 1.此邻区关系是否在1阶中已经体现,如果体现则以一阶算
• 2.存在邻区关系的两个小区不能是同小区

测试:

1.阶数表(暂时只有1阶)
create table Nlevel(
	sid int,
	nid int,
	level int
)
insert into Nlevel values (1,2,1);
insert into Nlevel values (1,3,1);

2.邻区关系表
create table Ncell(
	sid int,
	nid int
)
insert into Ncell values (1,2);
insert into Ncell values (1,3);
insert into Ncell values (2,1);
insert into Ncell values (2,3);
insert into Ncell values (3,1);
insert into Ncell values (3,2);

一阶关系表如下

邻区关系表

如果我们想找二阶邻区,只要将Nlevel表中的nid和Ncell表的sid相关联

select 
	s.sid,
	n.nid,
	2 as level
from 
	(select sid,nid from Nlevel where level = 1) as s
inner join 
	Ncell as n on s.nid = n.sid  

结果如下:
nid为2时,可以找到两个2阶邻区:1-2(1阶)-1(2阶);1-2(1阶)-3(2阶)
nid为3时,可以找到两个2阶邻区:1-3(1阶)-1(2阶);1-3(1阶)-2(2阶)

其中第1和3条应该sid=nid应该过滤,而1-3和1-2虽然都是2阶,但是在1阶中都有体现,所以也要过滤

方式1:
select * from
	(
	select 
		s.sid,
		n.nid,
		2 as level
	from 
		(select sid,nid from Nlevel where level = 1) as s
	inner join 
		Ncell as n on s.nid = n.sid  
	) src
where not exists 
(
	select 1 from Nlevel where level < 2 and sid = src.sid and nid = src.nid
) and sid != nid

或者

方式2:
select * from
	(
	select 
		s.sid,
		n.nid,
		2 as level
	from 
		(select sid,nid from Nlevel where level = 1) as s
	inner join 
		Ncell as n on s.nid = n.sid  
	) src
where sid != nid and
not exists 
(
	select 1 from Nlevel where level < 2 and sid = src.sid and nid = src.nid
)

两者结果一致
方式1中求出了所有2阶记录,然后将结果一个一个代入到not exists表达式中,排除了1-2和1-3的记录,只剩下1-1的记录,之后再通过sid!=nid排除了1-1的记录
方式2中求出了所有2阶记录,先通过sid!=nid排除了1-1的记录,仅剩下1-2和1-3的记录,再通过not exists表达式排除

where后的条件是从左到右执行的,执行完一个条件才能执行下一个条件

Sql 语句中 IN 和 EXISTS 的区别及应用