SQL之 exists 、in，sqlexistsin
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
IN 其实与等于相似，比如in(1,2) 就是 = 1 or = 2的一种简单写法，所以一般在元素少的时候使用IN，如果多的话就用exists
exists的用法跟in不一样，一般都需要和子表进行关联，而且关联时，需要用索引，这样就可以加快速度。
你的SQL语句用NOT EXISTS可以写成
select MC001 from BOMMC WHERE NOT EXISTS (SELECT MD001 FROM BOMMD where BOMMC.MC001 = BOMMD.MD001)
假设如下应用：
两张表——用户表TDefUser（userid，address,phone）和消费表 TAccConsume(userid,time,amount)，需要查消费超过5000的用户记录。
用exists:
select * from TDefUser
where exists (select 1 from TAccConsume where TDefUser.userid=TAccConsume.userid and TAccConsume.amount>5000)
用in:
select * from TDefUser
where userid in (select userid from TAccConsume where TAccConsume.amount>5000)
通常情况下采用exists要比in效率高。
exists()后面的子查询被称做相关子查询 他是不返回列表的值的.只是返回一个ture或false的结果(这也是为什么子查询里是"select 1"的原因，换成"select 6"完全一样，当然也可以select字段，但是明显效率低些)
其运行方式是先运行主查询一次 再去子查询里查询与其对应的结果 如果是ture则输出,反之则不输出.再根据主查询中的每一行去子查询里去查询.
in()后面的子查询 是返回结果集的,换句话说执行次序和exists()不一样.子查询先产生结果集,然后主查询再去结果集里去找符合要求的字段列表去.符合要求的输出,反之则不输出.
本站文章为和通数据库网友分享或者投稿，欢迎任何形式的转载，但请务必注明出处.
同时文章内容如有侵犯了您的权益，请联系QQ：970679559，我们会在尽快处理。