sql server 'in' 或 'or' - 这是最快的 [英] sql server 'in' or 'or' - which is fastest

问题描述

好的，我有一个查询:

select distinct(a)
from mytable
where
b in (0,3)

什么会更快，上面的还是

What is going to be faster, the above or

select distinct(a)
from mytable
where
b = 0
or
b = 3

有一般规则吗?

谢谢

推荐答案

IN 和 OR 都会查询 b = 0后跟一个for b = 3，然后对两个结果集做一个合并连接，最后过滤掉所有重复项.

Both IN and OR will do a query for b = 0 followed by one for b = 3, and then do a merge join on the two result sets, and finally filter out any duplicates.

对于IN，重复没有意义，因为b 不能同时是0 和3，但事实是IN 将被转换为b = 0 OR b = 3，并且使用OR，重复做是有道理的，因为你可以有b = 0 OR a = 3，如果你要加入两个单独的结果集，你可能会得到每个匹配记录的重复两个标准.

With IN, duplicates doesn't really make sense, because b can't both be 0 and 3, but the fact is that IN will be converted to b = 0 OR b = 3, and with OR, duplicates do make sense, because you could have b = 0 OR a = 3, and if you were to join the two separate result sets, you could end up with duplicates for each record that matched both criteria.

因此，无论您使用的是IN 还是OR，都将始终进行重复过滤.但是，如果您从一开始就知道您不会有任何重复项(通常在使用 IN 时就是这种情况)，那么您可以通过使用 UNION ALL 不会过滤掉重复项:

So a duplicate filtering will always be done, regardless of whether you're using IN or OR. However, if you know from the outset that you will not have any duplicates - which is usually the case when you're using IN - then you can gain some performance by using UNION ALL which doesn't filter out duplicates:

select distinct(a)
from mytable
where
b = 0

UNION ALL

select distinct(a)
from mytable
where
b = 3

这篇关于sql server 'in' 或 'or' - 这是最快的的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！