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/