oracle中in与exists,Oracle中in和exists的区别

项目中对数据库的查询操作很多,各种拼接,各种in,但由于in的内容受字符限制,所以有些地方将in改成了Extist,两种写法有什么关联,查了写资料,分析看看:

in和exists

in 是把外表和内表作hash 连接，而exists是对外表作loop循环，每次loop循环再对内表进行查询。一直以来认为exists比in效率高的说法是不准确的。

如果查询的两个表大小相当，那么用in和exists差别不大。

如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in：

例如：表A(小表)，表B(大表)

1：

select * from A where cc in (select cc from B) 效率低，用到了A表上cc列的索引；

select * from A where exists(select cc from B where cc=A.cc) 效率高，用到了B表上cc列的索引。

相反的2：

select * from B where cc in (select cc from A) 效率高，用到了B表上cc列的索引；

select * from B where exists(select cc from A where cc=B.cc) 效率低，用到了A表上cc列的索引。

not in 和not exists如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引；而not extsts 的子查询依然能用到表上的索引。所以无论那个表大，用not exists都比notin要快。

in 与 =的区别

select name from student where name in ('zhang','wang','li','zhao');

与

select name from student where name='zhang' or name='li' or name='wang' orname='zhao'

的结果是相同的。

在ORACLE 11G大行其道的今天，还有很多人受早期版本的影响，记住一些既定的规则，1.子查询结果集小，用IN2.外表小，子查询表大，用EXISTS

简单说明：

a表的数据小，b表数据大时用exists。a为外表(也为主表)

SELECT * FROMa  WHERE EXISTS(  SELECT 1 FROM b WHEREa.employee_id=b.employee_id);

b表数据量小(子表)时，用in。

SELECT * FROMa WHERE a.employee_id IN (SELECT b.employee_id FROM b);

1 exists的使用

Exists用于只能用于子查询，可以替代in，若匹配到结果，则退出内部

查询，并将条件标志为true，传回全部结果资料,in不管匹配到匹配不到都

全部匹配完毕，使用exists可以将子查询结果定为常量，不影响查询效果，

而且效率高。如查询所有销售部门员工的姓名，对比如下：

IN is often better if the results of thesubquery are very small

When you write a query using the IN clause,you're telling the rule-based optimizer that you

want the inner query to drive the outerquery.

When you write EXISTS in a where clause,you're telling the optimizer that you want the outer

query to be run first, using each value tofetch a value from the inner query.

In many cases, EXISTS is better because itrequires you to specify a join condition, which can

invoke an INDEX scan. However, IN is oftenbetter if the results of the subquery are very

small. You usually want to run the query thatreturns the smaller set of results first.

In和exists对比：

若子查询结果集比较小，优先使用in，若外层查询比子查询小，优先使

用exists。因为若用in，则oracle会优先查询子查询，然后匹配外层查询，

若使用exists，则oracle会优先查询外层表，然后再与内层表匹配。最优化

匹配原则，拿最小记录匹配大记录。

在看下官网文档里怎么说：

首先看下语法，语法很简单，一看例子大家都会明白，但一定要注意Operation里面的那句话很重要，因为这样关系到null的问题，是返回至少一行就返回true值。

2 not exists的使用

与exists含义相反，也在子查询中使用，取出不满足条件的，与not in有一

定的区别，注意有时候not exists不能完全替代not in

可以看下面的例子，注意not in的检索是从外部查询逐条匹配子查询的检索出所有的元素。匹配相等就返回true，就会输出一条元素。而not exists就不是这个搜索原理了，他是看返回的元素个数如果针对notexists返回的元素为0则为真，所以会保留Null值。

可以看下以下的例子：

转载请注明：