左外连接和右外连接哪个更好？
我们可以通过这两种方式得到相同的结果。
Table_1 LEFT OUTER JOIN Table_2
Table_2 RIGHT OUTER JOIN Table_1
如果我们可以得到相同的结果为什么要使用右外连接？哪一个更好？
We can get the same result in both these ways..
Table_1 LEFT OUTER JOIN Table_2
Table_2 RIGHT OUTER JOIN Table_1
If we can get the same result why to use right outer join ? Which one is better ?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
正如其他人已经指出的那样，
LEFT OUTER JOIN和RIGHT OUTER JOIN是完全相同的操作，只是它们的参数相反。你的问题就像问是否写a <; 更好。 b或b >一个。它们是相同的 - 这只是一个偏好问题。话虽如此，我发现大多数人都更熟悉
LEFT JOIN并在 SQL 中一致地使用它。有些人甚至会发现，如果查询中间突然出现一个 RIGHT JOIN，那么阅读起来会非常困难，这会导致他们不得不停下来思考这意味着什么。因此，我建议在两个选项之间进行同等选择的情况下，更喜欢使用 LEFT JOIN 。保持一致将使其他开发人员更容易理解您的 SQL。As others have pointed out already
LEFT OUTER JOINandRIGHT OUTER JOINare exactly the same operation, except with their arguments reversed. Your question is like asking whether it is better to writea < borb > a. They're the same - it's just a matter of preference.Having said that, I find that most people are more familiar with
LEFT JOINand use it consistently in their SQL. Some people even find it quite difficult to read if there is suddenly aRIGHT JOINin the middle of a query, and it causes them to have to stop and think what it means. So I'd suggest that given an equal choice between the two options, prefer to useLEFT JOIN. Being consistent will make it easier for other developers to understand your SQL.这些是平等的，只是偏好和可读性的问题。我假设它是同一张桌子？
Those are equal, just a matter of preference and readability. I assume its the same tables?
左外连接
表 A 和 B 的左外连接（或简称左连接）的结果始终包含“左”表 (A) 的所有记录，即使连接条件不存在在“右”表 (B) 中找到任何匹配的记录。这意味着，如果 ON 子句匹配 B 中的 0（零）条记录，则联接仍将在结果中返回一行，但 B 中的每一列中都包含 NULL。这意味着左外联接返回所有值左表中的匹配值，加上右表中的匹配值（如果没有匹配的连接谓词，则为 NULL）
右外连接
右外连接（或右连接）与左外连接非常相似外连接，除了处理表格颠倒了。 “右”表 (B) 中的每一行都将在连接表中至少出现一次。如果“左”表 (A) 中不存在匹配行，则 A 中的列中将出现 NULL对于那些在 B 中没有匹配的记录。右外连接返回右表中的所有值以及左表中的匹配值（如果没有匹配连接谓词，则为 NULL）。
http://en.wikipedia.org/wiki/Join_%28SQL%29
Left Outer Join
The result of a left outer join (or simply left join) for table A and B always contains all records of the "left" table (A), even if the join-condition does not find any matching record in the "right" table (B). This means that if the ON clause matches 0 (zero) records in B, the join will still return a row in the result—but with NULL in each column from B. This means that a left outer join returns all the values from the left table, plus matched values from the right table (or NULL in case of no matching join predicate)
Right Outer Join
A right outer join (or right join) closely resembles a left outer join, except with the treatment of the tables reversed. Every row from the "right" table (B) will appear in the joined table at least once. If no matching row from the "left" table (A) exists, NULL will appear in columns from A for those records that have no match in B. A right outer join returns all the values from the right table and matched values from the left table (NULL in case of no matching join predicate).
http://en.wikipedia.org/wiki/Join_%28SQL%29
SQL 语言的设计者正确地认为，强制执行从左到右的连接优先级将是对语言的不必要的约束（遗憾的是，他们对列排序的感觉不一样！）
似乎确实对
LEFT OUTER在 Stackoverflow 上，人们会改变整个连接只是为了能够使用LEFT（我们有一个 此处就在昨天）。假设您最初在查询
Table_2 INNER JOIN Table_1中编写，然后才意识到实际上需要一个外连接来保留Table_1中的所有行。将INNER更改为RIGHT OUTER比将整个连接更改为能够使用LEFT OUTER要简单得多。简单在这里很好，因为它的侵入性较小，因此查询意图被无意更改的风险较小。要使用另一个类似的示例，请考虑关系运算符 半连接；作为关系代数的一部分，如果没有它，一项技术就不能被认为是关系完整的。尽管标准 SQL 确实有半连接谓词
MATCH，但它并未得到广泛实现。然而，大多数 SQL 产品都支持各种解决方法。 Stackoverflow 上最常见的方法似乎是在SELECT子句中使用INNER JOIN和DISTINCT并省略连接表中的属性。紧随其后的是使用WHERE table_1.ID IN (SELECT ID FROM Table_2)。接下来最流行的是WHERE EXISTS (SELECT * FROM Table_2 WHERE table_1.ID = table_1.ID)。重点是，以上都是半连接，在野外很常见。虽然我个人偏好使用
EXISTS（尽管奇怪的是它更接近关系演算），但我仍然需要能够将其他连接识别为半连接；有趣的是，最流行的方法（INNER JOIN加DISTINCT加非投影）可能是最难识别的方法！仅仅为了适应个人风格而重构代码是不受欢迎的：不必要的努力成本、增加风险、对源代码控制的影响等。学会认识和尊重他人的偏好是一项重要的技能：如果你发现自己重构了一次如果你能够理解它，你就会让自己处于不利的境地。
当然，从关系上来说，“正确”的答案是完全避免外连接。关系模型中不存在 null 这样的东西，并且外连接被明确设计为产生 null。
The designers of the SQL language rightly felt that enforcing left to right precedence of joins would be an unnecessary constraint on the language (sadly, they didn’t feel the same about column ordering!)
There does seems to be a strong preference for
LEFT OUTERhere on Stackoverflow, to the extent where folk will change the whole join just to be able to useLEFT(we had one here just yesterday).Say you had originally written in your query
Table_2 INNER JOIN Table_1before you realized you actually need an outer join preserving all rows fromTable_1. It would be a lot simpler to just changeINNERtoRIGHT OUTERthan to change the whole join to be able to useLEFT OUTER. Simple is good here because it is less invasive and therefore less risk of the query's intent being changed inadvertently.To use another similar example, consider the relational operator semi join; being part of the relational algebra, a technology cannot be considered to be relationally complete without it. Although Standard SQL does have a semi join predicate
MATCH, it is not widely implemented. However, most SQL products support various workarounds. The most common approach seen on Stackoverflow seems to be to useINNER JOINwithDISTINCTin theSELECTclause and omitting attributes from the joined table. This is closely followed by usingWHERE table_1.ID IN (SELECT ID FROM Table_2). Next most popular isWHERE EXISTS (SELECT * FROM Table_2 WHERE table_1.ID = table_1.ID).The point is, all above are semi joins that are very commonly found in the wild. Although my personal preference is to use
EXISTS(although curiously it is the one closer to the relational calculus), I still need to be able to identify the others as semi joins; interestingly, the most popular approach (INNER JOINplusDISTINCTplus non-projection) can be the hardest one to identify!Refactoring code for the sole purpose of fitting one's personal style is rightly frowned upon: cost of unnecessary effort, increase risk, implication for source control, etc. Learning to recognise and respect others preferences is an important skill: If you find yourself refactoring once just to be able to understand it you will be putting yourself at a disadvantage.
Of course, relationally speaking, the 'correct' answer is to avoid outer joins entirely. There is no such thing as null in the relational model and outer joins are expressly designed to produce nulls.
这取决于我们的需要——我们是否需要左表或右表中的所有列。
两者并不相同。
It depends on our need — whether we need all columns from Left table or Right table.
Both are not the same.
“LEFT OUTER JOIN 和 RIGHT OUTER JOIN 完全相同”的答案根本不正确。操作顺序是先执行JOIN select，最后执行FROM select。
因此，如果您从一个表中进行了非常具体的选择（您希望从中获得所有合格记录），并且从另一个表中进行了非常广泛的选择（您只想从中匹配记录），那么右外连接的性能可能会比左外连接好得多。
事实上，右外连接可能会在短短几秒钟内运行，而左外连接则会在 Oracle 等数据库中给出类似 ORA-01652 的错误。
The answer that "LEFT OUTER JOIN and RIGHT OUTER JOIN are exactly the same" isn't at all true. Order of operations is that the JOIN select is executed first and then the FROM select is executed last.
So if you have a very specific selection from one table from which you want all qualifying records and a very broad selection from another from which you want merely matching records, a right outer join may perform significantly better than a left outer join.
In fact, a right outer join may run in mere seconds when a left outer join would otherwise give you an error like ORA-01652 in a database like Oracle.