SQL 连接差异
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/419375/
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
SQL Join Differences
提问by leo
What's difference between inner join and outer join (left join,right join), and which has the best performance of them?
内连接和外连接（左连接，右连接）有什么区别，哪个性能最好？
Thanks!
谢谢！
回答by CMS
Check this article:
检查这篇文章：
A Visual Explanation of SQL Joins
Inner Join:
内部联接：
Left Outer Join:
左外连接：
Right Outer Join:
右外连接：
回答by HLGEM
Performance comparisions between types of joins are irrelevant as they give differnt results sets. Even if an inner join is faster you wouldn't use it if you needed the results of a left join (which includes even the records which don't match the second table in the join).
连接类型之间的性能比较无关紧要，因为它们会给出不同的结果集。即使内部联接更快，如果您需要左联接的结果（甚至包括与联接中的第二个表不匹配的记录），您也不会使用它。
回答by Keith
Left, right, inner and outer don't affect performance, and they have been well explained here already.
左、右、内和外不会影响性能，这里已经很好地解释了它们。
However there are hints you can add to joins that do effect performance: hash, loop and merge.
但是，您可以向会影响性能的连接添加一些提示：散列、循环和合并。
Normally the query planner decides which of these to do, but occasionally you can improve performance by overriding it.
通常查询计划器决定执行哪些操作，但有时您可以通过覆盖它来提高性能。
A loopjoingoes through every row in the second table for each row in the first. This is good if you have one very big table and one much smaller.
对于第一个表中的每一行，A遍历第二个表中的每一行。如果您有一张非常大的桌子和一张小得多的桌子，这很好。loopjoin
A mergejoingoes through both tables together in order. It can be very quick if both tables are already ordered by the field that you're joining on.
A按顺序一起遍历两个表。如果两个表都已按您要加入的字段排序，则速度会非常快。mergejoin
A hashjoinuses lots of temporary tables to group the output as it sorts through the joined data.
A使用大量临时表对输出进行分组，因为它对连接的数据进行排序。hashjoin
Some specialist DBs also supports other types, such as bitmap joins.
一些专业 DB 还支持其他类型，例如位图连接。
回答by Michael Buen
A LEFT JOIN Bis the same as B RIGHT JOIN A. Some RDBMS don't have RIGHT JOIN, so you have to rewrite your RIGHT JOINlogic to LEFT JOINlogic
ALEFT JOIN B与 相同B RIGHT JOIN A。一些 RDBMS 没有RIGHT JOIN，所以你必须将你的RIGHT JOIN逻辑重写为LEFT JOINlogic
A 1 2 3
B 2 3 4 3
SELECT A.I FROM INNER JOIN B ON B.I = A.I;
output: 2 3, 3
SELECT A.I AS X, B.I AS Y FROM A LEFT JOIN B ON B.I = A.I;
read above code as A on LEFT, JOINs B
output:
X Y
1 NULL
2 2
3 3
3 3
SELECT A.I AS X, B.I AS Y FROM B RIGHT JOIN A ON A.I = B.I;
Read the above code as B on RIGHT, JOINs A. Which is just the same as A is on LEFT
将上面的代码阅读为B on RIGHT, JOINs A. 这与 A 位于 LEFT 相同
Whatever is on left, is always evaluated, always have an output. You can imagine A LEFT JOIN B, B RIGHT JOIN A as:
无论左边是什么，总是被评估，总是有输出。你可以把 A LEFT JOIN B, B RIGHT JOIN A 想象成：
var result = new Dictionary<int, int?>();
var A = new int[] { 1, 2, 3 };
var B = new int[] { 2, 3, 4, 3 };
foreach (int aElem in A)
{
bool hasMatch = false;
foreach (int bElem in B)
{
if (bElem == aElem)
{
result.Add(aElem, bElem);
hasMatch = true;
}
}
if (!hasMatch)
result.Add(aElem, null);
}
foreach(int X in result.Keys)
{
MessageBox.Show(string.Format("X {0} Y {1}", X, result[X].HasValue ? result[X].Value.ToString() : "NULL" ));
}
回答by dkretz
Hopefully you understand the pictures. Performance-wise, they are equivalent - no difference.
希望你能看懂图片。在性能方面，它们是等效的 - 没有区别。
EDIT: Oops. Guess you didn't care about that part of the answer.
编辑：哎呀。猜猜你不关心答案的那部分。
