我的理解是这样:综合来说,exists更好一点。
select * from A where id in (select id from B);
select * from A where exists (select 1 from B where A.id=B.id);
区别就是
in肯定用 先查B表,然后根据每一行数据 去A中匹配,这样B表比较小还好,如果B表大的话,空间占用高,循环次数也多
exist是转换为Join了,对join的情况,默认会把小表作为驱动表,这样的话,B表很大的话,可能会用A做驱动表,效率就会高一些。
相当于 in 肯定用B做驱动表,exists 会用小表做驱动表,更灵活
就按网上那些测试例子:
我oracle11 a表 5006 , b表550005。
T1:select * from a
where a.quantity in (select quantity from b)
T2:
select * from a
where exists (select 1 from b where b.quantity = a.quantity)
分别重复了几次,时间几乎一样。
还是针对自己的情况,看实际效果来优化吧。
mysql 有时还要把exists改成join 呢
roy_88 版主帅的一匹。
我很认同他的话。我自己去技术内幕调了这块看,网上也看了很多相关的博客(不是转,抄,就是复述)。
首先技术内幕上并没有提到说exists某些情况(比如表大表小)下会比in优,它们生成的执行计划完全一样的,里面着重提到not in 和not exists是有区别的,但不是强调在速度什么方面,两者执行计划的不同是因为考虑到null的处理。
然后是那些博客,比较看去有说服力的,就是理论得到结论!认为in里面是select * from b 这类语句,会扫描整个表,不会走索引。
而exists后面是select * from b where b.xx = a.xx 会走索引。认为b表大时用exists比较好。
最后我非常赞同 roy_88 的话:
①首先,我们不是为了找到一个说服自己的文章,然后就放下疑问。我们要学会一些能够动手的,自己去实验。
②时间在不断地走,我们用的那些产品是出自一批顶尖的人之手,今年都2017年,这么多年了,他们会拿着钱,不干事吗。很多以前的一些问题,随着时间过去,他们已经能够很好的优化了。
③不能忽略现实环境,在怎么样的情况下,可能就会有不同的情况了。没有一定这样写或者那样写,当时间都是毫秒,哪来不对。当你发现很慢,发现了问题,再进行具体的问题具体分析吧。
④前辈们给了我们很多以前的经验,但是时间在过,我们也得成长,学着自己解决问题,向前辈们学习解决问题的思路和能力。
刚出校的一个菜鸟,大牛前辈见谅,有不对的请指正。
其实在sql server 没什么影响,sql server 会自动选择较好的执行计划,所以 用 exists 和 in 都会自动优化可能选择相同的执行计划。
但是在mysql 方面可能就没这么方便了,mysql 中子exists查询或 in 的多少都会有些影响。
exists 和 in 在sqlserver 没什么差别,但是在sql 语句的写法中,使用 in 可能有出乎意料(不好)的结果。
参考:T-SQL查询:慎用 IN 和 NOT IN