Exists和IN的原理解析

select * from A where id in(select id from B)
select a.* from A a where exists(select 1 from B b where a.id=b.id)
1.子查询集小即B表比A表数据小，用IN（子查询是内表即匹配表，父查询是外表即驱动表）

2.子查询集大即B表比A表数据大，用EXISTS（子查询是内表即匹配表，父查询是外表即驱动表）

3.当A表数据与B表数据一样大时,in与exists效率差不多,可任选一个使用.

这样的结论在11G时是完全错误的观点。在8i、9i时代，可能是正确的，11G的CBO已经优化了IN,EXISTS的区别，所以可能SQL写法不同，但是执行计划却是完全一样的。

IN
IN Condition
An in_condition is a membership condition. It tests a value for membership in a list of values or subquery

select * from A where id in(select id from B)

以上查询使用了in语句,它查出B表中的所有id字段并缓存起来.之后,检查A表的id是否与B表中的id相等,如果相等则将A表的记录加入结果集中,直到遍历完A表的所有记录.

它的查询过程类似于以下过程

List resultSet=[];

Array A=(select * from A);

Array B=(select id from B);

for(int i=0;i<a.length;i++)       {=""

   for(int j=0;j<b.length;j++){

      if(A[i].id==B[j].id) {

         resultSet.add(A[i]);

         break; }   }   }

return resultSet;

子查询有null值时，not in (子查询)的结果是null，而in (子查询)的结果不会这样
SELECT distinct emp.employee_id FROM employees emp order by 1;--107行，没有null的行
SELECT distinct mgr.manager_id FROM employees mgr order by 1;--19行，最后一行是null
SELECT emp.employee_id,emp.last_name FROM employees emp WHERE emp.employee_id not IN (SELECT mgr.manager_id FROM employees mgr);--0行结果
SELECT emp.employee_id,emp.last_name FROM employees emp WHERE emp.employee_id not IN (SELECT mgr.manager_id FROM employees mgr where mgr.manager_id is not null);--89行结果
SELECT emp.employee_id,emp.last_name FROM employees emp WHERE emp.employee_id IN (SELECT mgr.manager_id FROM employees mgr);--18行结果
SELECT emp.employee_id,emp.last_name FROM employees emp WHERE emp.employee_id IN (SELECT mgr.manager_id FROM employees mgr where mgr.manager_id is not null);--18行结果

where条件的字段如果值有null，如果where后面还有not in子查询，则会忽略外查询中null的查询结果
select * from employees where DEPARTMENT_ID not in (select DEPARTMENT_ID from departments);--没有结果
select * from employees where nvl(DEPARTMENT_ID,1) not in (select DEPARTMENT_ID from departments);--有DEPARTMENT_ID为null的那一行
select * from employees where DEPARTMENT_ID  in (select DEPARTMENT_ID from departments);--106行记录
select * from employees where nvl(DEPARTMENT_ID,1)  in (select DEPARTMENT_ID from departments)--106行记录

select department_id from departments order by 1;--27行，没有null
SELECT department_id FROM employees order by 1;--107行，最后一行是null
SELECT employee_id, last_name FROM employees e,departments d where e.department_id=d.department_id ORDER BY d.department_name;--结果为106行，employee_id=178这行的department_id字段是null值，不能用于=比较
SELECT employee_id, last_name FROM employees e ORDER BY (SELECT department_name FROM departments d WHERE e.department_id = d.department_id);--结果为107行，employee_id=178这行的department_id字段是null值，所以排最后

exists
EXISTS Condition
An EXISTS condition tests for existence of rows in a subquery.
TRUE if a subquery returns at least one row.

If a subquery row value is found:
--The search does not continue in the inner query
--The condition is flagged TRUE
如果有一个子查询结果找到：
--子查询不再继续
--条件被标志为TRUE
（意思是，当子查询匹配到了一条记录，就把条件标志为TRUE，不再需要去遍历完子查询）
If a subquery row value is not found:
--The condition is flagged FALSE
--The search continue in the inner query
如果子查询记录值没找到：
--条件被标志为FALSE
--子查询继续
（意思是，当子查询都遍历完了还没有找到的话，则把条件标志为FALSE）

select a.* from A a where exists(select 1 from B b where a.id=b.id)

以上查询使用了exists语句,exists()会执行A.length次,它并不缓存exists()结果集,因为exists()结果集的内容并不重要,重要的是结果集中是否有记录,如果有则返回true,没有则返回false.

它的查询过程类似于以下过程

List resultSet=[];

Array A=(select * from A)

for(int i=0;i<a.length;i++) {=""

   if(exists(A[i].id) {    //执行select 1 from B b where b.id=a.id是否有记录返回

       resultSet.add(A[i]);

   }

}

return resultSet;

结论：
EXISTS用于检查子查询是否至少会返回一行数据，EXISTS(包括 NOT EXISTS )子句的返回值是一个BOOLEAN值。 
EXISTS有一个子查询语句(SELECT XX FROM YY)，根据其子查询语句的结果集是否最少有一行记录，返回一个布尔值，就算结果集返回为一行但是数值为null，也算返回了数据，返回一个true，比如子查询为select null from dual的情况。

一种通俗的理解为：
将外查询表的每一行A，代入子查询作为检验，如果子查询返回的结果产生了任意一条记录，则EXISTS子句返回TRUE，这一行A则可作为外查询的结果行，否则不能作为结果。

Exist：前面查询的列不一定都要在子查询中出现

IN：子查询中的列必须匹配where条件的列

 

exists如果后面子查询和父查询有关联

则如下

exists （sql 返回结果集为真）

not exists (sql 不返回结果集为真）

 

exists如果后面子查询和父查询没有关联（当然现实中没人会这样写SQL，下面的案例只是为了理解原理而举的例子）

则子查询结果类似一个true和false，结果为true执行父查询得出父查询所有结果，结果为false，不执行父查询返回0行记录

 

具体实验：

create table t101 (hid number,hname varchar2(10));
insert into t101 values(1,'name1');
insert into t101 values(2,'name2');
insert into t101 values(3,'name3');

create table t102 (hid number,hname varchar2(10));
insert into t102 values(2,'name2');
insert into t102 values(3,'name3');
insert into t102 values(4,'name4');
insert into t102 values(5,'name5');
insert into t102 values(6,'name6');

SQL> select * from t101;
       HID HNAME
---------- ----------
         1 name1
         2 name2
         3 name3

SQL>  select * from t102;
       HID HNAME
---------- ----------
         2 name2
         3 name3
         4 name4
         5 name5
         6 name6

 

select * from t101  where exists (select hid from t102 where t101.hid=t102.hid);

执行结果为

2 name2

3 name3

原因可以按照如下分析（只把父查询表中的值带入子查询即可，案例即只有1、2、3）

SELECT * FROM t101  WHERE EXISTS (SELECT hid FROM t102 WHERE t102.hid =1)

---> SELECT hid FROM t102 WHERE t102.hid =1没有值不返回所以没有数据

 

SELECT * FROM t101  WHERE EXISTS (SELECT hid FROM t102 WHERE t102.hid =2)

---> SELECT hid FROM t102 WHERE t102.hid =2有值返回真所以有数据

 

SELECT * FROM t101  WHERE EXISTS (SELECT hid FROM t102 WHERE t102.hid =3)

---> SELECT hid FROM t102 WHERE t102.hid =3有值返回真所以有数据

 

 

NOT EXISTS 就是反过来

select * from t101  where not exists (select hid from t102 where t101.hid=t102.hid);

执行结果为

1 name1




SQL> select * from t101  where exists (select hid from t102 where t101.hid=t102.hid);
       HID HNAME
---------- ----------
         2 name2
         3 name3
SQL> select * from t101  where hid in (select hid from t102 where t101.hid=t102.hid);
       HID HNAME
---------- ----------
         2 name2
         3 name3

SQL> select * from t101  where not exists (select hid from t102 where t101.hid=t102.hid);
       HID HNAME
---------- ----------
         1 name1
SQL> select * from t101  where hid not in (select hid from t102 where t101.hid=t102.hid);
       HID HNAME
---------- ----------
         1 name1



SQL> select * from t101  where exists (select hid from t102);
       HID HNAME
---------- ----------
         1 name1
         2 name2
         3 name3
SQL> select * from t101 where hid in (select hid from t102);
       HID HNAME
---------- ----------
         2 name2
         3 name3

SQL> select * from t101  where not exists (select hid from t102);
no rows selected
SQL> select * from t101 where hid not in (select hid from t102);
       HID HNAME
---------- ----------
         1 name1



SQL>  select * from t101  where exists (select hid from t102 where hid=2);
       HID HNAME
---------- ----------
         1 name1
         2 name2
         3 name3
SQL> select * from t101  where hid in (select hid from t102 where hid=2);
       HID HNAME
---------- ----------
         2 name2

SQL> select * from t101  where not exists (select hid from t102 where hid=2);
no rows selected
SQL>  select * from t101  where hid not in (select hid from t102 where hid=2);
       HID HNAME
---------- ----------
         3 name3
         1 name1



SQL> select * from t101  where exists (select hid from t102 where hid=8);
no rows selected
SQL> select * from t101  where hid in (select hid from t102 where hid=8);
no rows selected

SQL> select * from t101  where not exists (select hid from t102 where hid=8);
       HID HNAME
---------- ----------
         1 name1
         2 name2
         3 name3
SQL> select * from t101  where hid not in (select hid from t102 where hid=8);
       HID HNAME
---------- ----------
         3 name3
         1 name1
         2 name2



SQL> select * from t101  where exists (select 9 from dual where 1=1);
       HID HNAME
---------- ----------
         1 name1
         2 name2
         3 name3
SQL> select * from t101  where hid in (select 9 from dual where 1=1);
no rows selected

SQL> select * from t101  where not exists (select 9 from dual where 1=1);
no rows selected
SQL> select * from t101  where hid not in (select 9 from dual where 1=1);
       HID HNAME
---------- ----------
         1 name1
         2 name2
         3 name3



SQL> select * from t101  where exists (select 9 from dual where 1=2);
no rows selected
SQL> select * from t101  where hid in (select 9 from dual where 1=2);
no rows selected

SQL> select * from t101  where not exists (select 9 from dual where 1=2);
       HID HNAME
---------- ----------
         1 name1
         2 name2
         3 name3
SQL> select * from t101  where hid not in (select 9 from dual where 1=2);
       HID HNAME
---------- ----------
         1 name1
         2 name2
         3 name3



SQL> select * from t101  where exists (select null from dual);
       HID HNAME
---------- ----------
         1 name1
         2 name2
         3 name3
SQL> select * from t101  where hid in (select null from dual);
no rows selected

SQL> select * from t101  where not exists (select null from dual);
no rows selected
SQL> select * from t101  where hid not in (select null from dual);
no rows selected



SQL> select * from t101  where exists (select null from dual where 1=2);
no rows selected
SQL> select * from t101  where hid in (select null from dual where 1=2);
no rows selected

SQL> select * from t101  where not exists (select null from dual where 1=2);
       HID HNAME
---------- ----------
         1 name1
         2 name2
         3 name3
SQL> select * from t101  where hid not in (select null from dual where 1=2);
       HID HNAME
---------- ----------
         1 name1
         2 name2
         3 name3

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30126024/viewspace-2122360/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30126024/viewspace-2122360/