首页 > 代码库 > In与Exists的区别
In与Exists的区别
这两个函数是差不多的，但由于优化方案不同，通常NOT Exists要比NOT IN要快，因为NOT EXISTS可以使用结合算法二NOT IN就不行了，而EXISTS则不如IN快，因为这时候IN可能更多的使用结合算法。
Select * from tableA Where exists(Select * From tableB Where tableB.ID=tableA.ID)
这句相当于：Select * from tableA Where id in (Select ID From tableB)
对于表tableA的每一条数据，都执行Select * From tableB Where tableB.ID=tableA.ID的存在性判断，如果表tableB中存在表tableA当前行相同的ID，则Exists为真，该行显示，否则不显示。
- IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况
- In确定给定的值是否与子查询或列表中的值相匹配
- Exists指定一个子查询，检测行的存在
-----IN 和NOT IN-----
----哪些部门中有雇员工资大于？-----
select * from Department where Department.deptID=
(select FDeptID from Employee where Employee.empSalary>7000)
----报错信息如下：子查询返回的值多于一个........
----Msg 512, Level 16, State 1, Line 1
----Subquery returned more than 1 value. This is not permitted when the
----subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.---
----改正：将“=”改为“in”
select * from Department where Department.deptID in
(select FDeptID from Employee where Employee.empSalary>7000)
go
----not in
select * from Department where Department.deptID in
(select FDeptID from Employee where Employee.empSalary>7000)
go
-----EXISTS 和NOT EXISTS-----一般用于IF语句的存在检测
----工资改革，检查雇员工资，达到以上的，每人提高，否则每人提高-----
/*--采用EXISTS子查询，进行酌情提升--*/
select * from Employee
go
if exists (select * from Employee where empSalary>7000)
begin
Print ‘有人工资达到，则每人提高，提高后工资为：‘
update Employee set empSalary=empSalary+300
select * from Employee
end
else
begin
Print ‘无人工资达到，则每人提高，提高后工资为：‘
update Employee set empSalary=empSalary+500
select * from Employee
end
go
----IN和Exists---
Select distinct deptName from Department
where exists(select * from Employee where empGender=1)
go
Select distinct deptName from Department
where deptID in(select FDeptID from Employee where empGender=1)
go
----exists相当于存在量词：表示集合存在，也就是集合不为空只作用于一个集合。
----exists P表示P不为空时为真；not Exists P表示P为空时，为真。
----in表示一个标量和医院关系的关系。s In P表示当s与P中的某个值相等时为真；
----s not in P表示s与P中的每一个值都不相等时，为真。
in和exists
in 是把外表和内表作hash 连接，而exists是对外表作loop循环，每次loop循环再对内表进行查询。一直以来认为exists比in效率高的说法是不准确的。
如果查询的两个表大小相当，那么用in和exists差别不大。
如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in：
例如：表A（小表），表B（大表）1：select * from A where cc in (select cc from B)
效率低，用到了A表上cc列的索引；select * from A where exists(select cc from B where cc=A.cc)
效率高，用到了B表上cc列的索引。
相反的2：select * from B where cc in (select cc from A)
效率高，用到了B表上cc列的索引；select * from B where exists(select cc from A where cc=B.cc)
效率低，用到了A表上cc列的索引。
not in 和not exists
如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引；而not extsts 的子查询依然能用到表上的索引。所以无论那个表大，用not exists都比not in要快。
not in 逻辑上不完全等同于not exists，如果你误用了not in，小心你的程序存在致命的BUG：
请看下面的例子：
create table t1 (c1 number,c2 number);
create table t2 (c1 number,c2 number);
insert into t1 values (1,2);
insert into t1 values (1,3);
insert into t2 values (1,2);
insert into t2 values (1,null);
select * from t1 where c2 not in (select c2 from t2);
no rows found
select * from t1 where not exists (select 1 from t2 where t1.c2=t2.c2);
c1 c2
1 3
[这里涉及到数据的null值的处理，因为null代表着无法预知]。
正如所看到的，not in 出现了不期望的结果集，存在逻辑错误。如果看一下上述两个select语句的执行计划，也会不同。后者使用了hash_aj。
因此，请尽量不要使用not in(它会调用子查询)，而尽量使用not exists(它会调用关联子查询)。如果子查询中返回的任意一条记录含有空值，则查询将不返回任何记录，正如上面例子所示。
除非子查询字段有非空限制，这时可以使用not in ,并且也可以通过提示让它使用hasg_aj或merge_aj连接
A: 用EXISTS替代IN、用NOT EXISTS替代NOT IN
在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下, 使用EXISTS(或NOT EXISTS)通常将提高查询的效率. 在子查询中,NOT IN子句将执行一个内部的排序和合并. 无论在哪种情况下,NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历). 为了避免使用NOT IN ,我们可以把它改写成外连接(OUTER JOINS)或NOT EXISTS.
例子：（高效）SELECT * FROM EMP (基础表) WHERE EMPNO > 0 AND EXISTS (SELECT ‘X‘ FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = ‘MELB’)
(低效)SELECT * FROM EMP (基础表) WHERE EMPNO > 0 AND DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE LOC = ‘MELB’)
B: 用EXISTS替换DISTINCT
当提交一个包含一对多表信息(比如部门表和雇员表)的查询时,避免在SELECT子句中使用DISTINCT. 一般可以考虑用EXIST替换, EXISTS 使查询更为迅速,因为RDBMS核心模块将在子查询的条件一旦满足后,立刻返回结果。
例：(低效): SELECT DISTINCT DEPT_NO,DEPT_NAME FROM DEPT D , EMP E WHERE D.DEPT_NO = E.DEPT_NO
(高效): SELECT DEPT_NO,DEPT_NAME FROM DEPT D WHERE EXISTS ( SELECT ‘X‘ FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO);
In与Exists的区别