[转]子查询中的IN与EXISTS的区别

在复杂的查询中，选择正确的子句将会对性能产生很大的影响。考虑一下在你的编码中使用过哪一些子句。
在主要/明细关系表中写一个SQL的时候，多数人都会经历这么一步，那就是决定是使用WHERE　EXISTS（…）子句还是WHERE值IN（…）子句来编写查询语句。你可能会拒绝使用WHERE　EXISTS，因为用它来编写的话，要返回一个值，在语法上很困难，而这正是你经常忽视的。

可是，如果你使用基于规则的最优化的话，情况就会大不相同了。你可以通过了解哪个表是驱动表，以及每一部份会返回多少行，来确定一个基于规则的查询的性能。

当你用IN子句来写一个查询语句的时候，就等于你向该基于规则的最优化传达了这样一个信息，即你想让内部的查询推动外部的查询（假定：IN＝由里而外）。举例来说，为在一个有14行记录的EMP表中查询员工名称等于“KING”的所有记录到一个直接报表中，你可以这样写：

select ename from emp e

where mgr in (select empno from emp where ename = 'KING');

以下是关于这个查询的说明计划:

OBJECT OPERATION

---------- ----------------------------------------

SELECT STATEMENT()

NESTED LOOPS()

EMP TABLE ACCESS(FULL)

EMP TABLE ACCESS(BY INDEX ROWID)

PK_EMP INDEX(UNIQUE SCAN)

这个查询实际上等同于以下这个:

select e1.ename from emp e1,(select empno from emp where ename = 'KING') e2 where e1.mgr = e2.empno;

你可以用EXISTS写同样的查询，你只要把外部查询一栏移到一个像下面这样的子查询环境中就可以了：

select ename from emp e

where exists (select 0 from emp where e.mgr = empno and ename = 'KING');

当你在一个WHERE子句中写EXISTS时，又等于向最优化传达了这样一条信息，即你想让外部查询先运行，使用每一个值来从内部查询（假定：EXISTS＝由外而内）中得到一个值。

关于这个查询的说明计划如下：

OBJECT OPERATION

---------- ----------------------------------------

SELECT STATEMENT()

FILTER()

EMP TABLE ACCESS(FULL)

EMP TABLE ACCESS(BY INDEX ROWID)

PK_EMP INDEX(UNIQUE SCAN)

这实际上与PL/SQL编码类似：

set serveroutput on;

declare

l_count integer;

begin

for e in (select mgr,ename from emp) loop

select count(*) into l_count from emp

where e.mgr = empno and ename = 'KING';

if l_count != 0 then

dbms_output.put_line(e.ename);

end if;

end loop;

end;

为了确定在基于规则的最优化中，哪一种子句性能更佳，不妨考虑一下，与外部查询相比，内部查询会返回多少行记录。许多情况下，EXISTS的表现更突出，这是因为，它需要你指定一个加入条件，这就可以调用一个INDEX扫描。尽管如此，如果该查询的结果很小的话，IN常常表现得更好。你通常都愿意运行那些能首先返回较少的结果的查询。