关于查询语句中的in和exists的区别

Question

谁来告诉我in和exists的区别，最好能举个例子，为什么一般exists的效率比in的高，他们的具体执行过程是怎么样的，请详述~~~~谢谢大侠们~~~

Answer 1

1、适用表的类型不同。

in是子查询为驱动表，外面的表为被驱动表，故适用于子查询结果集小而外面的表结果集大的情况。

exists是外面的表位驱动表，子查询里面的表为被驱动表，故适用于外面的表结果集小而子查询结果集大的情况。

2、子查询关联不同。

exists一般都是关联子查询。对于关联子查询，必须先执行外层查询，接着对所有通过过滤条件的记录，执行内层查询。外层查询和内层查询相互依赖，因为外层查询会把数据传递给内层查询。

in则一般都是非关联子查询，非关联子查询则必须先完成内层查询之后，外层查询才能介入。

3、执行次数不同。

IN 语句：只执行一次，确定给定的值是否与子查询或列表中的值相匹配。in在查询的时候，首先查询子查询的表，然后将内表和外表做一个笛卡尔积，然后按照条件进行筛选。所以相对内表比较小的时候，in的速度较快。

EXISTS语句：执行次数根据表的长度而定。指定一个子查询，检测行的存在。遍历循环外表，然后看外表中的记录有没有和内表的数据一样的。匹配上就将结果放入结果集中。

SQL语句语言特点：

1、SQL风格统一

SQL可以独立完成数据库生命周期中的全部活动，包括定义关系模式、录人数据、建立数据库、査询、更新、维护、数据库重构、数据库安全性控制等一系列操作，这就为数据库应用系统开发提供了良好的环境，在数据库投入运行后，还可根据需要随时逐步修改模式，且不影响数据库的运行，从而使系统具有良好的可扩充性。

2、高度非过程化

非关系数据模型的数据操纵语言是面向过程的语言，用其完成用户请求时，必须指定存取路径。而用SQL进行数据操作，用户只需提出“做什么”，而不必指明“怎么做”，因此用户无须了解存取路径，存取路径的选择以及SQL语句的操作过程由系统自动完成。这不但大大减轻了用户负担，而且有利于提高数据独立性。

Answer 2

区别：in是关系运算符，exists是相关子查询。
exists的效率比in高的说法不正确，要看具体情况，尽可能利用索引。
例：设有两个表td（大表）和tx（小表），看下面的查询：
1、select * from td where col in (select col from tx); --利用了td的索引
select * from td where exists (select col from tx where col=td.col); --利用tab2的索引
结果：第一句效率高。
2、select * from tx where col in (select col from td); --利用了tx的索引
select * from tx where exists (select col from td where col=tx.col); --利用了td的索引
结果：第二句效率高。

追问

利用索引什么意思？？？

追答

对于数据量很大的表都为其经常作为查询条件的列建立索引，以此提高查询速度。数据量越大其查询效果越好，有无索引的查询速度相差几倍到几十倍。在上述答案是两个表td和tx都有col列索引的前提下，为了提高效率尽可能在大表上利用索引。

Answer 3

in和exists区别in 是把外表和内表作hash 连接，而exists是对外表作loop循环，每次loop循环再对内表进行查询。一直以来认为exists比in效率高的说法是不准确的。如果查询的两个表大小相当，那么用in和exists差别不大。如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in。NOT EXISTS,exists的用法跟in不一样，一般都需要和子表进行关联，而且关联时，需要用索引，这样就可以加快速度。 exists 相当于存在量词：表示集合存在,也就是集合不为空只作用一个集合。例如 exist P 表示P不空时为真; not exist P表示p为空时为真。 in表示一个标量和一元关系的关系。例如：s in P表示当s与P中的某个值相等时 为真; s not in P 表示s与P中的每一个值都不相等时为真： not in 和not exists的区别如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引；而not extsts 的子查询依然能用到表上的索引。所以无论那个表大，用not exists都比not in要快

Answer 4

in与exists的作用基本相同，效率也基本相同，通过查看查询计划可以看到in与exists会产生相同的查询计划，但是not in与not exists就有区别了，not exists会处理null值，优化过程，而not in没有此优化，所以效率要不如exists，而且一旦有null值，not in的结果集会不准确

Answer 5

类似问题以前也给其他朋友回答过，“exists的效率比in的高”的说法不完全正确，要视情况而定。
“exists”和“in”是Oracle中，都是查询某集合的值是否存在在另一个集合，但对不同的数据有不同的用法，主要是在效率问题上存在很大的差别，以下有两个简单例子，以说明 “exists”和“in”的效率问题。
1、 select * from Table1 where exists(select 1 from Table2 where Table1.a=Table2.a) ;
Table1数据量小而Table2数据量非常大时，Table1<<Table2 时，exists的查询效率高。
2、 select * from Table1 where Table1.a in (select Table2.a from Table2) ;
Table1数据量非常大而Table2数据量小时，Table1>>Table2 时，in的查询效率高。

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

追问

能细说一下exists的执行过程么？
如 是先查一下主查询得到一条结果就和后面的子表中比较，还是把主查询所有结果查出来再执行后面的子查询呢？能不能具体说一下它的执行过程，刚刚学数据库，实现不懂丫~~

为什么table1<<table2时 exists查询效率高呢？