oracle中的exists和not exists和in用法详解

Bertha 。 2022-08-24 04:27 306阅读 0赞

有两个简单例子，以说明 “exists”和“in”的效率问题

1) select \* from T1 where exists(select 1 from T2 where T1.a=T2.a) ;

T1数据量小而T2数据量非常大时，T1<<T2 时，1) 的查询效率高。

2) select \* from T1 where T1.a in (select T2.a from T2) ;

T1数据量非常大而T2数据量小时，T1>>T2 时，2) 的查询效率高。

exists 用法：

请注意 1）句中的有颜色字体的部分 ，理解其含义；

其中 “select 1 from T2 where T1.a=T2.a” 相当于一个关联表查询，相当于

“select 1 from T1,T2 where T1.a=T2.a”

但是，如果你当当执行 1） 句括号里的语句，是会报语法错误的，这也是使用exists需要注意的地方。

“exists（xxx）”就表示括号里的语句能不能查出记录，它要查的记录是否存在。

因此“select 1”这里的 “1”其实是无关紧要的，换成“\*”也没问题，它只在乎括号里的数据能不能查找出来，是否存在这样的记录，如果存在，这 1） 句的where 条件成立。

in 的用法：

继续引用上面的例子

“2) select \* from T1 where T1.a in (select T2.a from T2) ”

这里的“in”后面括号里的语句搜索出来的字段的内容一定要相对应，一般来说，T1和T2这两个表的a字段表达的意义应该是一样的，否则这样查没什么意义。

打个比方：T1，T2表都有一个字段，表示工单号，但是T1表示工单号的字段名叫“ticketid”，T2则为“id”，但是其表达的意义是一样的，而且数据格式也是一样的。这时，用 2）的写法就可以这样：

“select \* from T1 where T1.ticketid in (select T2.id from T2) ”

Select name from employee where name not in (select name from student);

Select name from employee where not exists (select name from student);

第一句SQL语句的执行效率不如第二句。

通过使用EXISTS，Oracle会首先检查主查询，然后运行子查询直到它找到第一个匹配项，这就节省了时间。Oracle在执行IN子查询时，首先执行子查询，并将获得的结果列表存放在一个加了索引的临时表中。在执行子查询之前，系统先将主查询挂起，待子查询执行完毕，存放在临时表中以后再执行主查询。这也就是使用EXISTS比使用IN通常查询速度快的原因

==================================

假设如下应用：  
两张表——用户表TDefUser（userid，address,phone）和消费表 TAccConsume(userid,time,amount)，需要查消费超过5000的用户记录。  
用exists:  
select \* from TDefUser  
where exists (select 1 from TAccConsume where TDefUser.userid=TAccConsume.userid and TAccConsume.amount>5000)  
用in:  
select \* from TDefUser  
where userid in (select userid from TAccConsume where TAccConsume.amount>5000)

通常情况下采用exists要比in效率高。

exists()后面的子查询被称做相关子查询 他是不返回列表的值的.只是返回一个ture或false的结果(这也是为什么子查询里是"select 1"的原因，换成"select 6"完全一样，当然也可以select字段，但是明显效率低些)  
其运行方式是先运行主查询一次 再去子查询里查询与其对应的结果 如果是ture则输出,反之则不输出.再根据主查询中的每一行去子查询里去查询.

in()后面的子查询 是返回结果集的,换句话说执行次序和exists()不一样.子查询先产生结果集,然后主查询再去结果集里去找符合要求的字段列表去.符合要求的输出,反之则不输出.

比如用户表TDefUser（userid，address,phone），消费表 TAccConsume(userid,time,amount)数据如下：

消费表聚集索引是userid,time  
数据（注意因为有聚集索引,实际存储也是按以下次序的）  
1   2006-1-1  200  
1   2006-1-2  300  
1   2006-1-2  500  
1   2006-1-3  2000  
1   2006-1-3  2000  
1   2006-1-4  400  
1   2006-1-5  500  
2   2006-1-1  200  
2   2006-1-2  300  
2   2006-1-2  500  
2   2006-1-3  2000  
2   2006-1-3  6000  
2   2006-1-4  400  
2   2006-1-5  8000  
3   2006-1-1  7000  
3   2006-1-2  30000  
3   2006-1-2  50000  
3   2006-1-3  20000

语句：  
select \* from TDefUser  
where exists (select 1 from TAccConsume where TDefUser.userid=TAccConsume.userid and TAccConsume.amount>5000)

对于userid=1，需要找所有记录，才返回false，与第二个语句的效率差不多  
对于userid=2，找到2006-1-3的记录，就返回true，比第而个语句的效率高  
对于userid=3，第一条记录就返回true，比第二个语句的效率高

语句  
select \* from TDefUser  
where userid in (select userid from TAccConsume where TAccConsume.amount>5000)

返回空记录集  
2  
2  
3  
3  
3  
3

再判断

语句  
select \* from TDefUser  
where userid in (select userid from TAccConsume where userid=TDefUser.userid and amount>5000)

对于userid=1，需要找所有记录，返回空记录集，比较判断  
对于userid=2，需要找所有记录，返回记录集  
2  
2  
，比较判断  
对于userid=3需要找所有记录，返回记录集  
3  
3  
3  
3  
，比较判断

表中如果没有聚集索引，对exists每个userid查找的条数都不同，但都是<=第三个语句需要扫描的条数，极端的（比如> 5000的都是在最后）与第三个语句效率相似，一般的比第二个语句快，所以说,“一般”exists比in效率高