子查询中的IN与EXISTS的区别

在复杂的查询中选择正确的子句将会对性能产生很大的影响考虑一下在你的编码中使用过哪一些子句

在主要/明细关系表中写一个SQL的时候多数人都会经历这么一步那就是决定是使用WHEREEXISTS（…）子句还是WHERE值IN（…）子句来编写查询语句你可能会拒绝使用WHEREEXISTS因为用它来编写的话要返回一个值在语法上很困难而这正是你经常忽视的

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

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

select ename from emp e

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

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

OBJECT OPERATION

SELECT STATEMENT()

NESTED LOOPS()

EMPTABLE ACCESS(FULL)

EMP TABLE ACCESS(BY INDEX ROWID)

PK_EMP INDEX(UNIQUE SCAN)

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

select eename from emp e(select empno from emp where ename = KING) ewhere emgr = eempno;

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

select ename from emp e

where exists (select from emp where emgr = empno and ename = KING);

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

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

OBJECT OPERATION

SELECT STATEMENT()

FILTER()

EMPTABLE 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 mgrename from emp) loop

select count(*) into l_count from emp

where emgr = empno and ename = KING;

if l_count != then

dbms_outputput_line(eename);

end if;

end loop;

end;

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

有些人尽量避免使用EXISTS子句这是因为它要求必须从该查询中返回一个结果纵使这个结果根本就不会用到由于个人喜好的原因人们经常使用x或零从说明计划的输出我们可以看到它显示了最优化会一直使用而拒绝接受你所有输入的其它任何值许多开发人员有这样一种习惯那就是经常输入一些常量

如果你想运行一下你自己的测试或者想看看其它的例子以下是我使用的两个脚本

REM explainsql view plan from PLAN_TABLE

set feedback off

set verify off

set pages

column operation format a

column object format a

TTITLE * STATEMENT_ID = & *

select object_name object

lpad( level)||operation||(||options||) operation

from plan_table

start with id = and statement_id = &

connect by prior id = parent_id and statement_id = &;

REM existssql examples with EXPLAIN PLAN

REM IN vs EXISTS

REM if you dont have a PLAN_TABLE run

REM @?/rdbms/admin/xplan

alter session set optimizer_goal = rule;

truncate table plan_table;

REM find direct reports to KING

explain plan set statement_id = IN for

select ename from emp e

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

explain plan set statement_id = JOININ for

select eename from emp e(select empno from emp where ename = KING) e

where emgr = eempno;

explain plan set statement_id = EXISTS for

select ename from emp e

where exists (select from emp where emgr = empno and ename = KING);

explain plan set statement_id = = for

select ename from emp e

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

explain plan set statement_id = JOIN for

select eename from emp eemp e

where emgr = eempno

and eename = KING;

REM find employees with greater than average salaries

explain plan set statement_id = > for

select ename from emp e where esal > (select avg(sal) from emp);

explain plan set statement_id = JOIN for

select eename from emp e(select avg(sal) sal from emp) e

where esal > esal;

@@explain IN

@@explain JOININ

@@explain EXISTS

@@explain =

@@explain JOIN

@@explain >

@@explain JOIN

上一篇：新农小编教你将U盘做成系统安装盘

下一篇：恢复“拼写和语法”功能的操作方法