[SQL Server]Awesome ALL,SOME &ANY

最近进行SQL Tuning时,深入查看相关执行计划,发现QO改写用有趣的陈叙式,马上又引起我的兴趣


从SQL2008开始提供ALL、SOME或ANY这美妙比较判断陈述式,

这让开发人员在比较数值上更方便且简洁,下面我来简单进行一些性能比较。

Not in
select c2
from tbl5
where c2 not in
(
select c2
from tbl6
)

Logical reads: 4342737+8197  , plan cost: 51.81

Not exists
select c2
from tbl5
where not exists
(
select c2
from tbl6
where tbl6.c2=tbl5.c2
)

Logical reads: 8197+ 73 , plan cost:6.959

ALL
select c2
from tbl5
where c2 != all
(
select c2
from tbl6
)

Logical reads: 4342737+ 8197 , plan cost:51.81

In
select c2
from tbl5
where c2 in
(
select c2
from tbl6
)

Logical reads: 73+ 8197 , plan cost:7.49

Any | Some
select c2
from tbl5
where c2 = Any
(
select c2
from tbl6
)
select c2
from tbl5
where c2 = some
(
select c2
from tbl6
)

Logical reads: 73+ 8197 , plan cost:7.49

结论:

看来ALL, SOME|ANY和 IN , not in性能是相同的,所以原本in和not in的性能问题也同样会发生在ALL, SOME|ANY,

比较可惜的是只能比较单一字段,希望后面版本可以看到多字段比较。

参考

ALL (Transact-SQL)

SOME | ANY (Transact-SQL)