SQL之 exists 、in

浏览数：26 / 时间：2015年06月12日
tips ： 
1.外表为大表，内表为小表时，使用exist 
2.外表为小表，内表为大表时，使用in


示例：外表大，内表小
create table outTable (id1 int);
insert into outtable select generate_series(1,1000000);


create table inTable (id1 int);
insert into inTable values(1),(10000),(100000),(1000000);


test=# explain select count(*) from outtable big where exists (select id1 from inTable small  where small.id1=big.id1);  
                                       QUERY PLAN                                        
-----------------------------------------------------------------------------------------
 Aggregate  (cost=18968.50..18968.51 rows=1 width=0)
   ->  Hash Join  (cost=44.50..17718.50 rows=500000 width=0)
         Hash Cond: (big.id1 = small.id1)
         ->  Seq Scan on outtable big  (cost=0.00..13922.00 rows=1000000 width=4)
         ->  Hash  (cost=42.00..42.00 rows=200 width=4)
               ->  HashAggregate  (cost=40.00..42.00 rows=200 width=4)
                     ->  Seq Scan on intable small  (cost=0.00..34.00 rows=2400 width=4)
(7 rows)


Time: 3.743 ms
test=# explain select count(*) from outtable big where id1  in  (select id1 from inTable small  where small.id1=big.id1);
                                  QUERY PLAN                                  
------------------------------------------------------------------------------
 Aggregate  (cost=20032672.00..20032672.01 rows=1 width=0)
   ->  Seq Scan on outtable big  (cost=0.00..20031422.00 rows=500000 width=0)
         Filter: (SubPlan 1)
         SubPlan 1
           ->  Seq Scan on intable small  (cost=0.00..40.00 rows=12 width=4)
                 Filter: (id1 = big.id1)
(6 rows)


Time: 1.286 ms
test=# select count(*) from outtable big where exists (select id1 from inTable small  where small.id1=big.id1);          
 count 
-------
     4
(1 row)


Time: 272.027 ms
test=# select count(*) from outtable big where id1  in  (select id1 from inTable small  where small.id1=big.id1);        
 count 
-------
     4
(1 row)


Time: 4021.244 ms




外表小，内表大
test=# select count(*) from intable small where exists (select id1 from outtable big  where big.id1=small.id1);       
 count 
-------
     4
(1 row)


Time: 4792.643 ms
test=# select count(*) from intable small where id1 in  (select id1 from outtable big  where big.id1=small.id1);         
 count 
-------
     4
(1 row)


Time: 223.778 ms
test=# explain select count(*) from intable small where exists (select id1 from outtable big  where big.id1=small.id1);   
                                       QUERY PLAN                                       
----------------------------------------------------------------------------------------
 Aggregate  (cost=33336.10..33336.11 rows=1 width=0)
   ->  Hash Semi Join  (cost=29840.00..33333.10 rows=1200 width=0)
         Hash Cond: (small.id1 = big.id1)
         ->  Seq Scan on intable small  (cost=0.00..34.00 rows=2400 width=4)
         ->  Hash  (cost=13922.00..13922.00 rows=1000000 width=4)
               ->  Seq Scan on outtable big  (cost=0.00..13922.00 rows=1000000 width=4)
(6 rows)


Time: 1.021 ms
test=# explain select count(*) from intable small where id1 in  (select id1 from outtable big  where big.id1=small.id1);         
                                  QUERY PLAN                                  
------------------------------------------------------------------------------
 Aggregate  (cost=19706446.00..19706446.01 rows=1 width=0)
   ->  Seq Scan on intable small  (cost=0.00..19706443.00 rows=1200 width=0)
         Filter: (SubPlan 1)
         SubPlan 1
           ->  Seq Scan on outtable big  (cost=0.00..16422.00 rows=1 width=4)
                 Filter: (id1 = small.id1)
(6 rows)


Time: 3.578 ms




1、exists 与 in 的区别
可以看出，in是一个集合运算符， a in {a1,a2,a3} 
in前面是一个元素，后面是一个集合，就是判断元素是否在集合里面，是则成立。
从上面可以看出，id1 in  (select id1 from outtable big  where big.id1=small.id1);      --如果id1 在 in 后面的集合中，则该行算在count（*）中，否则不算在count(*)中。


而exists 则为一个存在判断，如果exists后查询有结果，则为真，否则为假。
看下面的示例：
test=#  select count(*) from outtable big where exists  (select id1 from outtable where big.id1>1);                         
 count  
--------
 999999
(1 row)


Time: 488.649 ms
test=#  select count(*) from outtable big where exists  (select id1 from outtable where id1>1);    
  count  
---------
 1000000
(1 row)


Time: 313.216 ms


为什么结果不同？
    (select id1 from outtable where id1>1) 我们知道，这个是有返回结果的，且对exists前面的count(*) 即表任一行是没有任何影响的，故相当于elect count(*) from outtable big ; 即不存在限制条件。
    (select id1 from outtable where big.id1>1)我们知道，这个是有返回结果的，且exists对前面的语句是存在限制的，那就是big.id1>1 故相当于select count(*) from outtable big where big.id1>1 ;




select * from 表A where exists(select * from 表B where 表B.id=表A.id)
这句相当于
select * from 表A where id in (select id from 表B)
对于表A的每一条数据,都执行select * from 表B where 表B.id=表A.id的存在性判断,如果表B中存在表A当前行相同的id,则exists为真,该行显示,否则不显示。


tips:
    1.通常NOT EXISTS要比NOT IN 要快, 因为NOT EXISTS可以使用结合算法而NOT IN　就不行了，而EXISTS则不如IN快, 因为这时候IN可能更多的使用结合算法.
    2.通常exists 不走索引，而in走索引。


参考
1. http://www.cnblogs.com/a-zx/articles/1749957.html

郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。

标签： sqlsql

相关文章

随机文章

您可能还喜欢

您可能还喜欢