SQL 何时或为何使用右外连接而不是左连接？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/436345/
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
When or why would you use a right outer join instead of left?
提问by DCookie
Wikipediastates:
维基百科指出：
"In practice, explicit right outer joins are rarely used, since they can always be replaced with left outer joins and provide no additional functionality."
“在实践中，很少使用显式右外连接，因为它们总是可以被左外连接替换并且不提供额外的功能。”
Can anyone provide a situation where they have preferred to use the RIGHT notation, and why? I can't think of a reason to ever use it. To me, it wouldn't ever make things more clear.
任何人都可以提供他们更喜欢使用正确符号的情况，为什么？我想不出使用它的理由。对我来说，它永远不会让事情变得更清楚。
Edit: I'm an Oracle veteran making the New Year's Resolution to wean myself from the (+) syntax. I want to do it right
编辑：我是一名 Oracle 资深人士，正在制定新年决议以摆脱 (+) 语法。我想做对
采纳答案by Yes - that Jake.
The only reason I can think of to use RIGHT OUTER JOIN is to try to make your SQL more self-documenting.
我能想到的使用 RIGHT OUTER JOIN 的唯一原因是尝试使您的 SQL 更具自我记录性。
You might possibly want to use left joins for queries that have null rows in the dependent (many) side of one-to-many relationships and right joins on those queries that generate null rows in the independent side.
您可能希望对在一对多关系的从属（多）侧具有空行的查询使用左联接，对在独立侧生成空行的那些查询使用右联接。
This can also occur in generated code or if a shop's coding requirements specify the order of declaration of tables in the FROM clause.
这也可能发生在生成的代码中，或者如果商店的编码要求在 FROM 子句中指定了表的声明顺序。
回答by Roman Pekar
I've never used right joinbefore and never thought I could actually need it, and it seems a bit unnatural. But after I thought about it, it could be really useful in the situation, when you need to outer join one table with intersection of many tables, so you have tables like this:
我以前从未使用right join过，也从未想过我真的需要它，而且看起来有点不自然。但是经过我的思考，它在以下情况下可能非常有用，当您需要将一张表与许多表的交集进行外连接时，您会得到这样的表：
And want to get result like this:
并希望得到这样的结果：
Or, in SQL (MS SQL Server):
或者，在 SQL (MS SQL Server) 中：
declare @temp_a table (id int)
declare @temp_b table (id int)
declare @temp_c table (id int)
declare @temp_d table (id int)
insert into @temp_a
select 1 union all
select 2 union all
select 3 union all
select 4
insert into @temp_b
select 2 union all
select 3 union all
select 5
insert into @temp_c
select 1 union all
select 2 union all
select 4
insert into @temp_d
select id from @temp_a
union
select id from @temp_b
union
select id from @temp_c
select *
from @temp_a as a
inner join @temp_b as b on b.id = a.id
inner join @temp_c as c on c.id = a.id
right outer join @temp_d as d on d.id = a.id
id id id id
----------- ----------- ----------- -----------
NULL NULL NULL 1
2 2 2 2
NULL NULL NULL 3
NULL NULL NULL 4
NULL NULL NULL 5
So if you switch to the left join, results will not be the same.
因此，如果您切换到left join，结果将不一样。
select *
from @temp_d as d
left outer join @temp_a as a on a.id = d.id
left outer join @temp_b as b on b.id = d.id
left outer join @temp_c as c on c.id = d.id
id id id id
----------- ----------- ----------- -----------
1 1 NULL 1
2 2 2 2
3 3 3 NULL
4 4 NULL 4
5 NULL 5 NULL
The only way to do this without the right join is to use common table expression or subquery
在没有正确连接的情况下执行此操作的唯一方法是使用公共表表达式或子查询
select *
from @temp_d as d
left outer join (
select *
from @temp_a as a
inner join @temp_b as b on b.id = a.id
inner join @temp_c as c on c.id = a.id
) as q on ...
回答by Michael Buen
B RIGHT JOIN A is the same as A LEFT JOIN B
B RIGHT JOIN A 与 A LEFT JOIN B 相同
B RIGHT JOIN A reads: B ON RIGHT, THEN JOINS A. means the A is in left side of data set. just the same as A LEFT JOIN B
B RIGHT JOIN A 读作：B ON RIGHT, THEN JOINS A. 表示 A 在数据集的左侧。和 A LEFT JOIN B 一样
There are no performance that can be gained if you'll rearrange LEFT JOINs to RIGHT.
如果将 LEFT JOIN 重新排列为 RIGHT，则无法获得任何性能。
The only reasons I can think of why one would use RIGHT JOIN is if you are type of person that like to think from inside side out (select * from detail right join header). It's like others like little-endian, others like big-endian, others like top down design, others like bottom up design.
我能想到为什么要使用 RIGHT JOIN 的唯一原因是，如果您是那种喜欢从里到外思考的人（从详细信息右连接标题中选择 *）。就像其他人喜欢 little-endian，其他人喜欢 big-endian，其他人喜欢自顶向下设计，其他人喜欢自底向上设计。
The other one is if you already have a humongous query where you want to add another table, when it's a pain in the neck to rearrange the query, so just plug the table to existing query using RIGHT JOIN.
另一种情况是，如果您已经有一个庞大的查询，您想在其中添加另一个表，那么重新排列查询会很麻烦，因此只需使用 RIGHT JOIN 将表插入现有查询即可。
回答by Bill the Lizard
The only time I would think of a right outer join is if I were fixing a full join, and it just so happened that I needed the result to contain all records from the table on the right. Even as lazy as I am, though, I would probably get so annoyed that I would rearrange it to use a left join.
我唯一会想到右外连接的情况是，如果我正在修复一个完整的连接，恰好我需要结果包含右侧表中的所有记录。尽管我很懒惰，但我可能会很恼火，以至于我会重新排列它以使用左连接。
This example from Wikipediashows what I mean:
维基百科的这个例子说明了我的意思：
SELECT *
FROM employee
FULL OUTER JOIN department
ON employee.DepartmentID = department.DepartmentID
If you just replace the word FULLwith RIGHTyou have a new query, without having to swap the order of the ONclause.
如果你只是FULL用RIGHT你有一个新的查询替换这个词，而不必交换ON子句的顺序。
回答by Matt
SELECT * FROM table_a
INNER JOIN table_b ON ....
RIGHT JOIN table_c ON ....
How else could you quickly/easily inner join the first 2 tables and join with table_c while ensuring all rows in table_c are always selected?
您还能如何快速/轻松地内部连接前 2 个表并与 table_c 连接，同时确保 table_c 中的所有行始终被选中？
回答by Andrew G. Johnson
SELECT * FROM table1 [BLANK] OUTER JOIN table2 ON table1.col = table2.col
Replace [BLANK] with:
将 [BLANK] 替换为：
LEFT - if you want all records from table1 even if they don't have a col that matches table2's (also included are table2 records with matches)
LEFT - 如果您想要 table1 中的所有记录，即使它们没有与 table2 匹配的 col（还包括具有匹配项的 table2 记录）
RIGHT - if you want all records from table2 even if they don't have a col that matches table1's (also included are table1 records with matches)
右 - 如果您想要 table2 中的所有记录，即使它们没有与 table1 匹配的 col（还包括具有匹配项的 table1 记录）
FULL - if you want all records from table1 and from table2
FULL - 如果您想要 table1 和 table2 中的所有记录
What is everyone talking about? They're the same? I don't think so.
每个人都在谈论什么？他们是一样的吗？我不这么认为。
回答by HLGEM
The only times I've used a right join have been when I want to look at two sets of data and I already have the joins in a specific order for the left or inner join from a previously written query. In this case, say you want to see as one set of data the records not included in table a but in table b and in a another set the records not in table b but in table a. Even then I tend only to do this to save time doing research but would change it if it was code that would be run more than once.
我唯一使用过右连接的时候是当我想查看两组数据并且我已经按照特定顺序对之前编写的查询中的左连接或内连接进行了连接。在这种情况下，假设您希望将不包含在表 a 中但包含在表 b 中的记录视为一组数据，而在另一组中查看不在表 b 中但在表 a 中的记录。即便如此，我倾向于这样做只是为了节省研究时间，但如果代码将运行多次，我会更改它。
回答by dkretz
SQL statements, in addition to being correct, should be as easy to read and expressively concise as possible (because they represent single atomic actions, and your mind needs to grok them completely to avoid unintended consequences.) Sometimes an expression is more clearly stated with a right outer join.
SQL 语句除了正确之外，还应该尽可能易于阅读和表达简洁（因为它们代表单个原子操作，您需要完全理解它们以避免意外后果。）有时表达式用一个正确的外连接。
But one can always be transformed into the other, and the optimizer will do as well with one as the other.
但是一个总是可以转换成另一个的，优化器会处理一个和另一个一样好。
For quite a while, at least one of the major rdbms products only supported LEFT OUTER JOIN. (I believe it was MySQL.)
很长一段时间，至少有一个主要的rdbms产品只支持LEFT OUTER JOIN。（我相信它是 MySQL。）
回答by mattpm
I've not really had to think much on the right join but I suppose that I have not in nearly 20 years of writing SQL queries, come across a sound justification for using one. I've certainly seen plenty of them I'd guess arising from where developers have used built-in query builders.
我并没有对正确的连接考虑太多，但我想我在近 20 年的 SQL 查询编写过程中没有遇到过使用它的合理理由。我当然见过很多这样的问题，我猜是因为开发人员使用了内置查询构建器。
Whenever I've encountered one, I've rewritten the query to eliminate it - I've found they just require too much additional mental energy to learn or re-learn if you haven't visited the query for some time and it hasn't been uncommon for the intent of the query to become lost or return incorrect results - and it's usually this incorrectness that has led to requests for me to review why the queries weren't working.
每当我遇到一个查询时，我都会重写查询以消除它 - 我发现如果您有一段时间没有访问查询并且它没有，他们只是需要太多额外的精神能量来学习或重新学习查询的意图丢失或返回不正确的结果的情况并不少见 - 通常正是这种不正确导致要求我查看为什么查询不起作用。
In thinking about it, once you introduce a right-join, you now have what I'd consider competing branches of logic which need to meet in the middle. If additional requirements/conditions are introduced, both of these branches may be further extended and you now have more complexity you're having to juggle to ensure that one branch isn't giving rise to incorrect results.
考虑一下，一旦你引入了一个右连接，你现在就有了我认为需要在中间相遇的竞争逻辑分支。如果引入了额外的要求/条件，这两个分支可能会进一步扩展，您现在需要处理更多的复杂性，以确保一个分支不会产生不正确的结果。
Further, once you introduce a right join, other less-experienced developers that work on the query later may simply bolt on additional tables to the right-join portion of the query and in doing so, expanding competing logic flows that still need to meet in the middle; or in some cases I've seen, start nesting views because they don't want to touch the original logic, perhaps in part, this is because they may not understand the query or the business rules that were in place that drove the logic.
此外，一旦您引入了右连接，稍后处理查询的其他经验不足的开发人员可能会简单地将附加表附加到查询的右连接部分，并在这样做时扩展仍然需要满足的竞争逻辑流中间; 或者在我见过的某些情况下，开始嵌套视图是因为他们不想触及原始逻辑，也许部分是因为他们可能不理解驱动逻辑的查询或业务规则。
回答by Jiri Tousek
In some SQL databases, there are optimizer hints that tell the optimizer to join the tables in the order in which they appear in the FROMclause - e.g. /*+ORDERED */in Oracle. In some simple implementations, this might even be the only execution plan available.
在一些 SQL 数据库中，有优化器提示，告诉优化器按照它们在FROM子句中出现的顺序连接表- 例如/*+ORDERED */在 Oracle 中。在一些简单的实现中，这甚至可能是唯一可用的执行计划。
In such cases order of tables in the FROMclause matters so RIGHT JOINcould be useful.
在这种情况下，FROM子句中表格的顺序很重要，因此RIGHT JOIN可能很有用。
