INNER JOIN 能否提供比 EXISTS 更好的性能 [英] Can an INNER JOIN offer better performance than EXISTS
问题描述
我一直在研究如何改进一系列过程的性能，最近一位同事提到他在使用 INNER JOIN 代替 EXISTS 时取得了显着的性能改进.
I've been investigating making performance improvements on a series of procedures, and recently a colleague mentioned that he had achieved significant performance improvements when utilising an INNER JOIN in place of EXISTS.
作为调查的一部分，我想我会在这里问这个问题.
As part of the investigation as to why this might be I thought I would ask the question here.
所以:
- INNER JOIN 能否提供比 EXISTS 更好的性能?
- 在什么情况下会发生这种情况?
- 如何设置测试用例作为证明?
- 是否有任何有用的链接指向更多文档?
实际上，人们可以为这个问题带来任何其他经验.
And really, any other experience people can bring to bear on this question.
如果有任何答案可以专门解决这个问题，而没有任何其他可能的性能改进建议，我将不胜感激.我们已经取得了相当大的成功，我只是对这一项感兴趣.
I would appreciate if any answers could address this question specifically without any suggestion of other possible performance improvements. We've had quite a degree of success already, and I was just interested in this one item.
任何帮助将不胜感激.
推荐答案
一般来说，INNER JOIN 和 EXISTS 是不同的东西.
Generally speaking, INNER JOIN and EXISTS are different things.
前者返回两个表中的重复项和列，后者返回一个记录，作为谓词，只返回一个表中的记录.
The former returns duplicates and columns from both tables, the latter returns one record and, being a predicate, returns records from only one table.
如果您对 UNIQUE 列进行内部联接，则它们表现出相同的性能.
If you do an inner join on a UNIQUE column, they exhibit same performance.
如果您对应用了 DISTINCT 的记录集进行内部联接(以消除重复项)，EXISTS 通常会更快.
If you do an inner join on a recordset with DISTINCT applied (to get rid of the duplicates), EXISTS is usually faster.
IN 和 EXISTS 子句(具有 equijoin 相关性)通常使用几种 SEMI JOIN 算法之一，这些算法通常比DISTINCT 在其中一张表上.
IN and EXISTS clauses (with an equijoin correlation) usually employ one of the several SEMI JOIN algorithms which are usually more efficient than a DISTINCT on one of the tables.
在我的博客中查看这篇文章:
See this article in my blog:
这篇关于INNER JOIN 能否提供比 EXISTS 更好的性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
