INEXISTS与NOT INNOT EXISTS 的优化原则小结

1. EXISTS的执行流程 select * from t1 where exists ( select null
from t2 where y = x ) 可以理解为: 复制代码 代码如下: for x in ( select * from t1
) loop if ( exists ( select null from t2 where y = x.x ) then OUTPUT THE
RECORD end if end loop 对于in 和 exists的性能区别:
如果子查询得出的结果集记录较少,主查询中的表较大且又有索引时应该用in,反之如果外层的主查询记录较少,子查询中的表大,又有索引时使用exists。
其实我们区分in和exists主要是造成了驱动顺序的改变,如果是exists,那么以外层表为驱动表,先被访问,如果是IN,那么先执行子查询,所以我们会以驱动表的快速返回为目标,那么就会考虑到索引及结果集的关系了
另外IN时不对NULL进行处理,如: select 1 from dual where null in
(0,1,2,null) 结果为空。 2. NOT IN 与NOT EXISTS: NOT EXISTS的执行流程
复制代码 代码如下: select ….. from
rollup R where not exists ( select ‘Found’ from title T where
R.source_id = T.Title_ID); 可以理解为: 复制代码 代码如下: for x in ( select * from
rollup ) loop if ( not exists ( that query ) ) then OUTPUT end if; end;
注意:NOT EXISTS 与 NOT IN
不能完全互相替换,看具体的需求。如果选择的列可以为空,则不能被替换。
例如下面语句,看他们的区别: select x,y from t; x y —— —— 1 3 3
1 1 2 1 1 3 1 5 select * from t where x not in (select y from t t2 ) no
rows select * from t where not exists (select null from t t2 where
t2.y=t.x ) x y —— —— 5 NULL 所以要具体需求来决定 对于not in 和
not exists的性能区别: not in 只有当子查询中,select 关键字后的字段有not
null约束或者有这种暗示时用not
in,另外如果主查询中表大,子查询中的表小但是记录多,则应当使用not
in,并使用anti hash join.
如果主查询表中记录少,子查询表中记录多,并有索引,可以使用not
exists,另外not in最好也可以用/*+ HASH_AJ */或者外连接+is null NOT IN
在基于成本的应用中较好 比如: 复制代码
代码如下: select ….. from rollup R where not exists ( select ‘Found’
from title T where R.source_id = T.Title_ID); 改成 select …… from
title T, rollup R where R.source_id = T.Title_id(+) and T.Title_id is
null; 或者 sql select /*+ HASH_AJ */ … from rollup R where
ource_id NOT IN ( select ource_id from title T where ource_id IS NOT
NULL )
注意:上面只是从理论上提出了一些建议,最好的原则是大家在上面的基础上,能够使用执行计划来分析,得出最佳的语句的写法。
‘//============================= exists,not exists总结 1 exists
SELECT * FROM anken_m WHERE EXISTS( SELECT
my_list_temp_m.sales_code FROM my_list_temp_m WHERE
my_list_temp_m.sales_code=anken_m.sales_code) 说明: 1)
查询在anken_m表和my_list_temp_m表中都存在的sales_code。 2)
sales_code是anken_m的主键,my_list_temp_m的外键。 注意: 1)
外层查询表anken_m是查询的对象。 2)
内层查询表my_list_temp_m是条件对象。 3) 内外层的查询表不能相同。 4)
作为关联条件的anken_m表不需要在内层查询FROM后添加。 5)
my_list_temp_m.sales_code=anken_m.sales_code条件的左右顺序不影响查询结果。
2 not exists SELECT * FROM anken_m WHERE NOT EXISTS( SELECT
my_list_temp_m.sales_code FROM my_list_temp_m WHERE
my_list_temp_m.sales_code=anken_m.sales_code) 说明: 1)
查询在anken_m表中存在,但是在my_list_temp_m表中不存在的sales_code。
2) sales_code是anken_m的主键,my_list_temp_m的外键。 注意: 1)
外层查询表anken_m是查询的对象。 2)
内层查询表my_list_temp_m是条件对象。 3) 内外层的查询表不能相同。 4)
作为关联条件的anken_m表不需要在内层查询FROM后添加。 5)
my_list_temp_m.sales_code=anken_m.sales_code条件的左右顺序不影响查询结果。
3 综合运用 UPDATE anken_m SET(plan_type_code, branch_name,
business_type_code) =(SELECT
anken.plan_type_code,anken.branch_name,anken.business_type_code
FROM anken WHERE anken.sales_code=anken_m.sales_code) WHERE EXISTS (
SELECT anken.sales_code FROM anken,my_list_temp_m WHERE
my_list_temp_m.sales_code=anken.sales_code AND
anken.sales_code=anken_m.sales_code ) 说明: 1)
用一个表的记录数据更新另一个表的记录数据。 2)
用一个SQL语句进行批量更新。 2)
sales_code是anken,anken_m的主键,my_list_temp_m的外键。 注意: 1)
set
语句中的要被更新字段必须跟数据源字段一一对应,另外数据源查询中的条件必须限定一条记录。也就是根据sales_code可以唯一确定anken的一条记录,和anken_m的一条记录,这样才能保证要被更新的记录和数据源记录的主键是相同的。
2) 根据WHERE
EXISTS语句可以确定数据源记录的范围,也就是可以用anken表中哪些记录更新anken_m表。所以anken_m不需要在WHERE
EXISTS语句中的FROM后添加。

本文章介绍一篇关于sql中IN与EXISTS,NOT IN与NOT
EXISTS的详细介绍,有需要了解的同学可以参考一下。

先来比较下语法:

强调下:在含有NULL值的列的时候,就要小心了,not exists与not
in在逻辑上是不等价的–先创建2张表

–deals=交易表,areas=地域表,例如香港;我们的目的:查看有交易的地域

代码如下复制代码

select * from areas where id in (select city_id from deals);

use master;

select * from areas where id in (select city_id from deals where
deals.city_id = areas.id);

if db_id(‘DbTest’) is not null drop database DbTest;

select * from areas where exists (select null from deals where
deals.city_id = areas.id);

create database DbTest;go

区别:

use DbTest;go

EXISTS语法并没有说哪个字段落在了子查寻的结果中,而是说exists后面的语句执行的结果是不是有记录,只要有记录,则主查询语句就成立。它代表‘存在’,用来引领嵌套查询的子查询,它不返回任何数据,只产生逻辑真值‘true’与逻辑假值‘false’。由EXISTS引出的子查询,其目标列表达式通常都用*(用null也可以),因为带有EXISTS的子查询只返回真值或假值,给出列名没有实际意义。

–创建Customers表create table Customers( custid INT NOT NULL IDENTITY,
companyname NVARCHAR(40) NOT NULL, country NVARCHAR(15) NOT NULL,
constraint pk_customer primary key(custid));

性能变化的关键:explain

–创建Orders表

1 执行的先后顺序

谁是驱动表,谁先执行查询,谁后执行查询

代码如下复制代码

2 执行过程

exists的优点是:只要存在就返回了,这样的话很有可能不需要扫描整个表。
in需要扫描完整个表,并返回结果。
所以,在字表比较小的情况下,扫描全表和部分表基本没有差别;但在大表情况下,exists就会有优势。
看这两个语句:
–子查询会执行完全关联,并返回所有符合条件的city_id
select * from areas where id in (select city_id from deals where
deals.city_id = areas.id);

–子查询的关联其实是一样的,但子查询只要查到一个结果,就返回了,所以效率还是比较高些的
select * from areas where exists (select null from deals where
deals.city_id = areas.id);

CREATE TABLE Orders( orderid INT NOT NULL IDENTITY, custid INT NULL,
CONSTRAINT PK_Orders PRIMARY KEY(orderid), CONSTRAINT
FK_Orders_Customers FOREIGN KEY(custid) REFERENCES
Customers(custid),); set identity_insert Customers on;

3 字表查询的结果

exists判断子查询的结果是不是存在,但查到什么结果,什么字段,并不关心;
in 需要子查询查得的结果给主查询使用

对于in和exists的性能区别:
如果子查询得出的结果集记录较少,主查询中的表较大且又有索引时应该用in,反之如果外层的主查询记录较少,子查询中的表大,又有索引时使用exists。
其实我们区分in和exists主要是造成了驱动顺序的改变(这是性能变化的关键),如果是exists,那么以外层表为驱动表,先被访问,如果是IN,那么先执行子查询,所以我们会以驱动表的快速返回为目标,那么就会考虑到索引及结果集的关系了

另外IN时不对NULL进行处理
如:
select 1 from dual where null in (0,1,2,null)

注意:NOT EXISTS与 NOT
IN不能完全互相替换,看具体的需求。如果选择的列可以为空,则不能被替换。

对于not in和 not exists的性能区别:
not in只有当子查询中,select 关键字后的字段有not
null约束或者有这种暗示时用not
in,另外如果主查询中表大,子查询中的表小但是记录多,则应当使用not
in,并使用anti hash join.
如果主查询表中记录少,子查询表中记录多,并有索引,可以使用not
exists,另外not in最好也可以用/*+ HASH_AJ */或者外连接+is null
NOT IN在基于成本的应用中较好

比如:
select …..
from rollup R
where not exists ( select ‘Found’ from title T
where R.source_id = T.Title_ID);

改成(关联查询)

select ……
from title T, rollup R
where R.source_id = T.Title_id(+)
and T.Title_id is null;

或者(佳)
sql> select /*+ HASH_AJ */ …
from rollup R
where ource_id NOT IN ( select ource_id
from title T
where ource_id IS NOT NULL )

问题和解决

问题1:

–users表有1000条记录,id自增,id都大于0

select * from users where exists (select * from users limit 0);
–输出多少条记录?
select * from users where exists (select * from users where id <
0); –输出多少条记录?

答案(请选中查看):

10000条
0条

原因:
exists查询的本质,只要碰到有记录,则返回true;所以limit根本就不会去管,或者说执行不到。

问题2:

exists可以完全代替in吗?

不能。

例如:
–没有关联字段的情况:枚举常量
select * from areas where id in (4, 5, 6);

–没有关联字段的情况:这样exists对子查询,要么全true,要么全false
select * from areas where id in (select city_id from deals where
deals.name = ‘xxx’);

9、用exists替代in(发现好多程序员不知道这个怎么用):
在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接。
在这种情况下,使用exists(或not exists)通常将提高查询的效率。
举例:
(低效)
select … from table1 t1 where
t1.id > 10 and pno in
(select no from table2 where name like ‘www%’);
(高效)
select … from table1 t1 where
t1.id > 10 and exists
(select 1 from table2 t2 where t1.pno =
t2.no and name like ‘www%’);

10、用not exists替代not in:
在子查询中,not in子句将执行一个内部的排序和合并。
无论在哪种情况下,not in都是最低效的
(因为它对子查询中的表执行了一个全表遍历)。
为了避免使用not in,我们可以把它改写成外连接(Outer Joins)或not exists。

11、用exists替换distinct:
当提交一个包含一对多表信息的查询时,避免在select子句中使用distinct.
一般可以考虑用exists替换
举例:
(低效)
select distinct d.dept_no, d.dept_name from t_dept d, t_emp e where
d.dept_no = e.dept_no;
(高效)
select d.dept_no, d.dept_name from t_dept d where exists (select 1
from t_emp e where d.dept_no = e.dept_no);
exists使查询更为迅速,因为RDBMS核心模块将在子查询的条件一旦满足后,立刻返回结果.

12、用表连接替换exists:
通常来说,采用表连接的方式比exists更有效率。
举例:
(低效)
select ename from emp e where exists (select 1 from dept where dept_no
= e.dept_no and dept_cat = ‘W’);
SELECT ENAME
(高效)
select ename from dept d, emp e where e.dept_no = d.dept_no and
dept_cat = ‘W’;

INSERT INTO Customers(custid, companyname,country) VALUES(1, N’大众’,
N’中国’); INSERT INTO Customers(custid, companyname,country) VALUES(2,
N’宝马’, N’美国’); INSERT INTO Customers(custid, companyname,country)
VALUES(3, N’奔驰’, N’中国’); INSERT INTO Customers(custid,
companyname,country) VALUES(4, N’奇瑞’, N’德国’); INSERT INTO
Customers(custid, companyname,country) VALUES(5, N’福特’, N’美国’); set
identity_insert Customers off; set identity_insert Orders
on;–custid代表员工号 INSERT INTO Orders(orderid, custid) VALUES(1,1);
INSERT INTO Orders(orderid, custid) VALUES(2,2); INSERT INTO
Orders(orderid, custid) VALUES(3,3); INSERT INTO Orders(orderid, custid)
VALUES(4,4); INSERT INTO Orders(orderid, custid)
VALUES(5,5);–查看表的数据select custid,companyname,country from
Customers;select orderid,custid from Orders;–插入数据成功

–咱们回到正题,比较Exists与in,not exists与 not in

–查询来自中国,而且下过订单的所有客户

代码如下复制代码 select custid,companynamefrom Customers as Cwhere
country=N’中国’and exists (select * from Orders as O where
O.custid=C.custid);–返回–custid companyname–1 大众–3 奔驰

–外部查询返回来自中国的客户信息,对于这个客户,exists谓词在Orders表查找是否至少存在一个与外部客户行信息相同的custid订单行

–用IN查询刚刚的需求

代码如下复制代码 select custid,companynamefrom Customers as Cwhere
country=N’中国’and custid in(select custid from Orders);

–结果跟上面的返回一样的值

–下面的知识点我们需要认识到:–当列表中有NULL时,in实际会产生一个UNKNOWN的结果,例如
a in(d,b,null)的结果是UNKNOWN,而a not in (d,b,null)返回的是not
unknowd仍然是unknowd–而not in与not exists则结果会很不同,例如a
in(a,b,null)返回的是TRUE,而a not in(a,b,null)返回的肯定是not
true即为false–有了上面的认识,好继续开工了….–我们现在向Orders表插入一行数据

代码如下复制代码

set identity_insert Orders on;