sql查询篇，exists和not exists的使用

目录

一、oracle中exists用法

二、例句

1.准备工作

2. exists和not exists的使用

3. 判断表或某个值是否存在

4. exists和in子句的使用场景

5. oracle数据库 in 子句表达式值数量1000报错的问题

---

一、oracle中exists用法

exists语句用来判断()内的表达式是否存在返回值， 如果存在就返回true，where条件成立，前面的主sql语句执行。如果不存在就返回false，where条件不成立，sql语句就不会执行。

not exists和exists相反，子查询语句结果为空，此时返回true，则表示where条件成立，执行主sql语句，否则不执行。

使用exists时， 它只要括号中的表达式有一个值存在， 就立刻返回true，而不用遍历表中所有的数据。在一定场景使用可以提高SQL的执行效率。

二、例句

1.准备工作

建立测试表：

create table tb0A
(
  id    NUMBER(15) not null,
  ac003 VARCHAR2(40) not null,
  ab001 VARCHAR2(40) not null,
  STATUS VARCHAR2(5) not null,
  addtime DATE
);
comment on table tb0A
  is '人员';
-- Add comments to the columns 
comment on column tb0A.id
  is 'ID';
comment on column tb0A.ac003
  is '姓名';
  comment on column tb0A.ab001
  is '单位号';
  comment on column tb0A.STATUS
  is '状态，0未处理，1已处理';
    comment on column tb0A.addtime
  is '添加时间';

create table tb0B
(
  id    NUMBER(15) not null,
  ac003 VARCHAR2(40) not null,
  ab001 VARCHAR2(40) not null,
  res01 VARCHAR2(40),
  addtime DATE
);
comment on table tb0B
  is '接口对接记录表';
-- Add comments to the columns 
comment on column tb0B.id
  is 'ID';
comment on column tb0B.ac003
  is '姓名';
  comment on column tb0B.ab001
  is '单位号';
  comment on column tb0B.res01
  is '接口返回状态，001成功，002失败';
    comment on column tb0B.addtime
  is '添加时间';


create table tb0C
(
  id    NUMBER(15) not null,
  ac003 VARCHAR2(40) not null,
  ab001 VARCHAR2(40) not null,
  ae014 VARCHAR2(5) not null,
  addtime DATE
);
comment on table tb0C
  is '单位职工状态';
-- Add comments to the columns 
comment on column tb0C.id
  is 'ID';
comment on column tb0C.ac003
  is '姓名';
  comment on column tb0C.ab001
  is '单位号';
  comment on column tb0C.ae014
  is '在职状态，0，在职，1退休，2试用期';
    comment on column tb0C.addtime
  is '添加时间';

--查询
 select * from tb0A;
 select * from tb0B;
 select * from tb0C;

--插入语句
insert into tb0A(id,Ac003,Ab001,Status,Addtime) values(1,'菜哥1','11123','0',sysdate);
insert into tb0A(id,Ac003,Ab001,Status,Addtime) values(2,'小明','11123','1',sysdate);
insert into tb0A(id,Ac003,Ab001,Status,Addtime) values(3,'小众','11123','0',sysdate);
insert into tb0A(id,Ac003,Ab001,Status,Addtime) values(4,'张哥','11123','0',sysdate);
insert into tb0A(id,Ac003,Ab001,Status,Addtime) values(5,'张嫂','11135','0',sysdate);
insert into tb0A(id,Ac003,Ab001,Status,Addtime) values(6,'强哥','11135','0',sysdate);

insert into tb0B(id,Ac003,Ab001,res01,Addtime) values(1,'菜哥1','11123','001',sysdate);
insert into tb0B(id,Ac003,Ab001,res01,Addtime) values(2,'小明','11123','002',sysdate);
insert into tb0B(id,Ac003,Ab001,res01,Addtime) values(8,'小众','11123','002',sysdate);
insert into tb0B(id,Ac003,Ab001,res01,Addtime) values(4,'张哥','11123','002',sysdate);
insert into tb0B(id,Ac003,Ab001,res01,Addtime) values(5,'张嫂','11135','002',sysdate);
insert into tb0B(id,Ac003,Ab001,res01,Addtime) values(7,'强哥','11135','001',sysdate);

insert into tb0C(id,Ac003,Ab001,ae014,Addtime) values(1,'菜哥1','11123','0',sysdate);
insert into tb0C(id,Ac003,Ab001,ae014,Addtime) values(2,'小明','11123','0',sysdate);
insert into tb0C(id,Ac003,Ab001,ae014,Addtime) values(3,'小众','11123','1',sysdate);
insert into tb0C(id,Ac003,Ab001,ae014,Addtime) values(4,'张哥','11123','1',sysdate);
insert into tb0C(id,Ac003,Ab001,ae014,Addtime) values(5,'张嫂','11135','0',sysdate);
insert into tb0C(id,Ac003,Ab001,ae014,Addtime) values(6,'强哥','11135','0',sysdate);

2. exists和not exists的使用

--exists的使用，查询tb0A表和tb0B表，id关联相等、单位号相等、Status为0的数据
select * from tb0A where exists(select 1 from tb0B Where tb0A.id = tb0B.id and tb0A.ab001='11123' and tb0A.status='0')--查询tb0A
select * from tb0B where exists(select 1 from tb0A Where tb0A.id = tb0B.id and tb0A.ab001='11123' and tb0A.status='0')--查询tb0B
--也可以 delete 删除使用
delete from tb0B where exists(select 1 from tb0A Where tb0A.id = tb0B.id and tb0A.ab001='11123' and tb0A.status='0')

--not exists的使用
select * from tb0A where not exists(select 1 from tb0B Where tb0A.id = tb0B.id and tb0A.ab001='11123' and tb0A.status='0')

--exists的使用，查询tb0A表和tb0B表，id关联相等、单位号相等、Status为0的数据
--增加tb0C表联查出两个表的数据显示，且tb0C表中 ae014为0的
select * from tb0A,tb0C where exists(select 1 from tb0B Where tb0A.id = tb0B.id and tb0A.ab001='11123' and tb0A.status='0') 
and tb0A.id=tb0C.id and tb0C.Ae014='0'

--exists的使用，查询tb0A表和tb0B表，id关联相等、单位号相等、Status为0的数据
--只显示tb0A表的数据，且tb0C表中 ae014为0的
select * from tb0A where exists(select 1 from tb0B,tb0C Where tb0A.id = tb0B.id and tb0A.ab001='11123' and tb0A.status='0' 
and tb0A.id = tb0C.id and tb0C.Ae014='0')

3. 判断表或某个值是否存在

--判断TB0A表是否存在
select 1 from dual where EXISTS( SELECT 1 FROM User_Tables WHERE table_name = 'TB0A')
--也可以这样写，返回结果为条数
SELECT COUNT(1) FROM User_Tables WHERE table_name = 'TB0A';

--判断tb0c表中ab001 = '11123'的是否存在
select 1 from dual where EXISTS(SELECT 1 FROM tb0c WHERE ab001 = '11123')
--也可以这样写，此例中返回结果为4
SELECT COUNT(1) FROM tb0c WHERE ab001 = '11123';

注：此例说明

使用exists时， 它只要括号中的表达式有一个值存在， 就立刻返回true，而不用遍历表中所有的数据。在一定场景使用可以提高SQL的执行效率。
select 1 from dual where EXISTS(SELECT 1 FROM tb0c WHERE ab001 = '11123')--使用EXISTS只查找到第一条ab001 = '11123'就返回true。
SELECT COUNT(1) FROM tb0c WHERE ab001 = '11123';--count需要查找表中所有ab001 = '11123'的数据。

4. exists和in子句的使用场景

--当b表的数据量远大于a表，使用exists的查询效率高。相反则使用in子句

select * from a where exists(select 1 from b where a.id=b.id);
select * from a where id in (select id from b) ;

5. oracle数据库 in 子句表达式值数量1000报错的问题

select * from a where id in ('1'，'2'，'3'，····);--在oracle中使用in，其括号内列表允许最大的数量为1000，超出报错。列表传值时注意。其它数据库貌似不受影响。

select * from a where id in (select id from b) ;--在容量上，这种语句在oracle,mysql ...等数据库都不受1000报错的影响。