exists与in的区别以及exists在oracle与mysql中的语句写法

一、EXISTS与IN的使用效率的问题，通常情况下采用exists要比in效率高，因为IN不走索引，但要看实际情况具体使用：

IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况；

做查询时，in不包含空值,而exists包含空值。

例子：A表跟B表中都有列a

1)select * from A where A.a in(select B.a from B);

内部执行过程类似：

List resultSet=[];
Array A=(select * from A);
Array B=(select id from B);
for(int i=0;i<A.length;i++) {
   for(int j=0;j<B.length;j++) {
      if(A[i].id==B[j].id) {
         resultSet.add(A[i]);
         break;
      }
   }
}
return resultSet;

2)select * from A where exists(select 1 from B where A.a=B.a);

内部执行过程类似：

List resultSet=[];
Array A=(select * from A)
for(int i=0;i<A.length;i++) {
   if(exists(A[i].id) {    //执行select 1 from B b where b.id=a.id是否有记录返回
       resultSet.add(A[i]);
   }
}
return resultSet;

二、exists在oracle中的语句

exists在oracle中作为一个判断，其括号中重点不在内容，而在于是否有返回值，最终返回的是布尔类型的true or false;

在exists前面加上not是对原布尔值的否定，真假得假，假假得真；

本人练习过程中新建了张用户表users，主键为userID，外键有roleID

1）oracle用exists判断插入数据

select * from users

insert
when (not exists(select 1 from users where roleid=4)) then
into users(userid,username,age,roleid) select 8,'xxx',19,3 from users where roleid=2;

如果像以上在全局限定词where后面加上roleid=2,查到的结果会有3条数据，数据库会开辟三块内存空间来循环插入我们要insert的数据，

在插入第二块内存空间时数据库检测到表里已有该userID值，会报违反唯一约束错误，执行无效终止，弹窗报错，因此

全局限定词where后面的条件返回的数据不能多于1条

如果执行的是

insert
when (not exists(select 1 from users where roleid=4)) then
into users(userid,username,age,roleid) select 8,'xxx',19,3 from users where roleid=1;(此处的roleid取值可随意，只要满足结果返回1条数据就好)，

则会成功将数据插入数据表，然后commit

2）oracl用exists更新数据

例子：现有两张表，用户表跟角色表，roleid主外键关联

update users
set username='aa'
where exists (select 1
from role 
where role.roleid=users.roleid and role.roleid=3
)

通过exists括号里面的限定语句返回是否有数据，来对对应的数据进行修改，最后commit;

3）oracl用exists删除整行数据

delete from users
where exists (select 1
from role 
where role.roleid=users.roleid and role.roleid=3
)

以上语句可以对查询到的数据进行批量删除，最后commit;

三、exists在mysql中的语句

例子：在mysql中建用户表user

初始化数据：select * from user;

1）mysql用exists判断插入数据

insert into user(id,name,password,age) select 101,'mmm','234567',27 from 

user where not exists(select * from user where age=27) and name='ha';

2）mysql更新数据直接使用

update user set name='mmm1' where name='mmm';

无需使用exists,如果遇多表联合更新，方法类似oracle中的数据更新方法

3）mysql用exists删除整行数据

delete from user where name='mmm1';

无需使用exists,如果遇多表联合删除，方法类似oracle中的数据删除方法

----------------------------------------------------------------------
以上是个人总结他人经验与自己的实践生成的心得，客官有问题欢迎提出来，总结不好，请多多指教！