（转）SQL 里的 EXISTS与in、not exists与not in

 

SQL 里的 EXISTS与in、not exists与not in  

 

系统要求进行SQL优化，对效率比较低的SQL进行优化，使其运行效率更高，其中要求对SQL中的部分in/not in修改为exists/not exists

 

修改方法如下：

in的SQL语句

 

SELECT id, category_id, htmlfile, title, convert(varchar(20),begintime,112) as pubtime 
FROM tab_oa_pub WHERE is_check=1 and 
category_id in (select id from tab_oa_pub_cate where no='1') 
order by begintime desc

 

修改为exists的SQL语句

SELECT id, category_id, htmlfile, title, convert(varchar(20),begintime,112) as pubtime 
FROM tab_oa_pub WHERE is_check=1 and 
exists (select id from tab_oa_pub_cate where tab_oa_pub.category_id=convert(int,no) and no='1') 
order by begintime desc

 

分析一下exists真的就比in的效率高吗？

 

    我们先讨论IN和EXISTS。

    select * from t1 where x in ( select y from t2 )
   

 事实上可以理解为：

    select * from t1, ( select distinct y from t2 ) t2  where t1.x = t2.y;

    ——如果你有一定的SQL优化经验，从这句很自然的可以想到t2绝对不能是个大表，因为需要对t2进行全表的“唯一排序”，如果t2很大这个排序的性能是不可忍受的。但是t1可以很大，为什么呢？最通俗的理解就是因为t1.x=t2.y可以走索引。但这并不是一个很好的解释。试想，如果t1.x和t2.y都有索引，我们知道索引是种有序的结构，因此t1和t2之间最佳的方案是走merge join。另外，如果t2.y上有索引，对t2的排序性能也有很大提高。
   

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

    ——这个更容易理解，t1永远是个表扫描！因此t1绝对不能是个大表，而t2可以很大，因为y=x.x可以走t2.y的索引。
    综合以上对IN/EXISTS的讨论，我们可以得出一个基本通用的结论：

IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。

我们要根据实际的情况做相应的优化，不能绝对的说谁的效率高谁的效率低，所有的事都是相对的

//----------------------------------------------------------------------------------------------------------------------------------------------------------------

学数据库的时候你们老师一定那 选课那3个表做例子吧   题目查询选修了全部课程的学生的姓名    这是那三个表  学生表:student    sno,sname  0001,张三  0002,李四  0003,xxxx  ...    课程表Course  cno,cname  001,语文  002,数学  003,英语    选课表    sno,cno  0001,001  0001,002  0001,003  0002,001  0002,002  ....                select Sname  from student  Where not exists  (select * from Course where not exists  (select * from sc where Sno=student.sno AND cno=Course.Cno))    咱们从最后一个select说起.    select * from sc where Sno=student.sno AND cno=Course.Cno  这个sql的意思就是遍历这三个表,  找到所有所有学生选修所有课程记的记录..    (select * from Course where not exists  (select * from sc where Sno=student.sno AND cno=Course.Cno))    那么这条sql,依据上条sql的意思是,就是选中上条sql的相反的条件,就是加入某个学生没有选某个课程,就把这个记录查出来,  假如学生0003没有选课程003,   学生0004没有选001等等.    那么最后  select Sname  from student  Where not exists  (select * from Course where not exists  (select * from sc where Sno=student.sno AND cno=Course.Cno))    这句就排除了所有没有选一门课的学生,只要某个学生没有选某们课,不管是哪一门,就在上面的sql过滤出来了,那么上句sql的相反的,  就是 not exists (不符合上面sql结果的)    就是选全部课程的学生了      我的表达意思不是很清楚,不知道能否看懂呢.....     not exists的含义你可以google出来,上面几位也说的很清楚了  这句三层嵌套语句就是这么个含义....         当然,举一反三,你也可以写出,  被全部学生都选的课程,,,被全部学生都不选的课程,,,呵呵...

//---------------------------------------------------------------------------------------------------

sql　exists和not exists用法
 
               
               

                
                
 
                
                
exists （sql返回结果集，为真）   
not exists （sql不返回结果集，为真） 
如下： 
表A 

                
                
ID   NAME   
                
                
1       A1 
                
                
2       A2 
                
                
3       A3 
                
                
 
                
                
表B 
ID   AID   NAME 
1       1       B1 
2       2       B2   
3       2       B3 

表A和表B是１对多的关系   A.ID   =>   B.AID 

SELECT   ID,NAME   FROM   A   WHERE   EXIST   (SELECT   *   FROM   B   WHERE   A.ID=B.AID) 
执行结果为 
1       A1 
2       A2 
原因可以按照如下分析 
SELECT   ID,NAME   FROM   A   WHERE   EXISTS   (SELECT   *   FROM   B   WHERE   B.AID=１) 
---> SELECT   *   FROM   B   WHERE   B.AID=１有值，返回真，所以有数据

SELECT   ID,NAME   FROM   A   WHERE   EXISTS   (SELECT   *   FROM   B   WHERE   B.AID=2) 
---> SELECT   *   FROM   B   WHERE   B.AID=２有值，返回真，所以有数据

SELECT   ID,NAME   FROM   A   WHERE   EXISTS   (SELECT   *   FROM   B   WHERE   B.AID=3) 
---> SELECT   *   FROM   B   WHERE   B.AID=３无值，返回假，所以没有数据

NOT   EXISTS   就是反过来 
SELECT   ID,NAME   FROM   A   WHERE　NOT   EXIST   (SELECT   *   FROM   B   WHERE   A.ID=B.AID) 
执行结果为 
3       A3 
=========================================================================== 
EXISTS   =   IN,意思相同不过语法上有点点区别，好像使用IN效率要差点，应该是不会执行索引的原因 
SELECT   ID,NAME   FROM   A　   WHERE　ID   IN   (SELECT   AID   FROM   B) 

NOT   EXISTS   =   NOT   IN   ,意思相同不过语法上有点点区别 
SELECT   ID,NAME   FROM   A   WHERE　ID　NOT   IN   (SELECT   AID   FROM   B) 
                
                
 
                
                
 
                
                
 
                
                
UNION与EXISTS简单用法                                    
                
                

                
                
UNION：
                
                
UNION 指令的目的是将两个 SQL 语句的结果合并起来。从这个角度来看， UNION 跟 JOIN 有些许类似，因为这两个指令都可以由多个表格中撷取资料。 UNION 的一个限制是两个 SQL 语句所产生的栏位需要是同样的资料种类。另外，当我们用 UNION这个指令时，我们只会看到不同的资料值 (类似 Select DISTINCT)。 union只是将两个结果联结起来一起显示，并不是联结两个表………… UNION 的语法如下： [SQL 语句 1]
UNION
[SQL 语句 2] 
                
                

UNION ALL 这个指令的目的也是要将两个 SQL 语句的结果合并在一起。 UNION ALL 和 UNION 不同之处在于 UNION ALL 会将每一笔符合条件的资料都列出来，无论资料值有无重复。 UNION ALL 的语法如下： [SQL 语句 1]
UNION ALL
[SQL 语句 2] 
                
                

                
                
现在以实例来说明SQL Union的用法：(SQL Union All的用法是一样的。只是SQL Union All不会考虑记录是否有重复。)
比如:在一个会员表Users中有会员类型有两种，一种为VIP会员，另一种为普通会员，为VIP会员的在VIP字段中为yes，普通会员的在VIP字段为no。
要在前台显示10笔会员记录，其中五个最早注册的VIP会员和五个最早注册的普通会员，最早注册的VIP要排在最早注册的普通会员的前面。
SQL语句如下：
                
                
select top 10 * from (select top 5 * from users where vip="yes"
 order by id desc union select top 10 * from users where vip="no"
 order by id desc) as users
                
                

EXISTS：
                
                
系统要求进行SQL优化，对效率比较低的SQL进行优化，使其运行效率更高，其中要求对SQL中的部分in/not in修改为exists/not exists
                
                
 
                
                
修改方法如下：
                
                
in的SQL语句
                
                
SELECT id, category_id, htmlfile, title, convert(varchar(20),begintime,112) as pubtime 
FROM tab_oa_pub WHERE is_check=1 and 
category_id in (select id from tab_oa_pub_cate where no='1') 
order by begintime desc
                
                
修改为exists的SQL语句
SELECT id, category_id, htmlfile, title, convert(varchar(20),begintime,112) as pubtime 
FROM tab_oa_pub WHERE is_check=1 and 
exists (select id from tab_oa_pub_cate where tab_oa_pub.category_id=convert(int,no) and no='1') 
order by begintime desc

               
               

原文地址：http://belives2012.blog.163.com/blog/static/1761064342011079582249/