SQL INNER JOIN 能否提供比 EXISTS 更好的性能
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2177346/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Can an INNER JOIN offer better performance than EXISTS
提问by James Wiseman
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.
我一直在研究如何改进一系列程序的性能，最近一位同事提到他在使用 INNER JOIN 代替 EXISTS 时取得了显着的性能改进。
As part of the investigation as to why this might be I thought I would ask the question here.
作为调查为什么会这样的一部分，我想我会在这里问这个问题。
So:
所以：
- Can an INNER JOIN offer better performance than EXISTS?
- What circumstances would this happen?
- How might I set up a test case as proof?
- Do you have any useful links to further documentation?
- 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.
如果有任何答案可以专门解决这个问题，而没有任何其他可能的性能改进建议，我将不胜感激。我们已经取得了相当程度的成功，我只是对这一项感兴趣。
Any help would be much appreciated.
任何帮助将非常感激。
回答by Quassnoi
Generally speaking, INNER JOINand EXISTSare different things.
一般来说，INNER JOIN和EXISTS是不同的东西。
The former returns duplicates and columns from both tables, the latter returns one record and, being a predicate, returns records from only one table.
前者返回两个表中的重复项和列，后者返回一条记录，作为谓词，只返回一个表中的记录。
If you do an inner join on a UNIQUEcolumn, they exhibit same performance.
如果对UNIQUE列进行内部联接，则它们表现出相同的性能。
If you do an inner join on a recordset with DISTINCTapplied (to get rid of the duplicates), EXISTSis usually faster.
如果您使用DISTINCT应用（以消除重复项）对记录集进行内部联接，EXISTS通常会更快。
INand EXISTSclauses (with an equijoin correlation) usually employ one of the several SEMI JOINalgorithms which are usually more efficient than a DISTINCTon one of the tables.
INandEXISTS子句（具有 equijoin 相关性）通常使用几种SEMI JOIN算法中的一种，这些算法通常比DISTINCT其中一个表上的a 更有效。
See this article in my blog:
在我的博客中看到这篇文章：
回答by gbn
Maybe, maybe not.
也许，也许不是。
- The same plan will be generated most likely
- An INNER JOIN may require a DISTINCT to get the same output
- EXISTS deals with NULL
- 最有可能生成相同的计划
- INNER JOIN 可能需要 DISTINCT 才能获得相同的输出
- EXISTS 处理 NULL
