SQL 什么时候使用全外连接比较合适？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2094793/
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 is a good situation to use a full outer join?
提问by Nick
I'm always discouraged from using one, but is there a circumstance when it's the best approach?
我总是不鼓励使用它，但是有没有一种情况是最好的方法？
采纳答案by Cade Roux
It's rare, but I have a few cases where it's used. Typically in exception reports or ETL or other very peculiar situations where both sides have data you are trying to combine.
The alternative is to use an INNER JOIN, a LEFT JOIN(with right side IS NULL) and a RIGHT JOIN(with left side IS NULL) and do a UNION- sometimes this approach is better because you can customize each individual join more obviously (and add a derived column to indicate which side is found or whether it's found in both and which one is going to win).
这很少见，但我有一些使用它的情况。通常在异常报告或 ETL 或其他非常特殊的情况下，其中双方都有您试图合并的数据。
另一种方法是使用 an INNER JOIN、 a LEFT JOIN（右侧IS NULL）和 a RIGHT JOIN（左侧IS NULL）并执行 a UNION- 有时这种方法更好，因为您可以更明显地自定义每个单独的连接（并添加派生列以指示找到哪一侧或者是否在两者中都找到了，哪个会获胜）。
回答by ewernli
I noticed that the wikipedia page provides an example.
我注意到维基百科页面提供了一个例子。
For example, this allows us to see each employee who is in a department and each department that has an employee, but also see each employee who is not part of a department and each department which doesn't have an employee.
例如，这使我们能够查看属于某个部门的每个员工和每个有员工的部门，还可以查看每个不属于某个部门的员工和每个没有员工的部门。
Note that I never encountered the need of a full outer join in practice...
请注意，我在实践中从未遇到过完全外部联接的需要......
回答by George Johnston
I've used full outer joins when attempting to find mismatched, orphaned data, from both of my tables and wanted all of my result set, not just matches.
在尝试从我的两个表中查找不匹配的孤立数据时，我使用了完整的外部联接，并且想要我的所有结果集，而不仅仅是匹配。
回答by NonProgrammer
Just today I had to use Full Outer Join. It is handy in situations where you're comparing two tables. For example, the two tables I was comparing were from different systems so I wanted to get following information:
就在今天，我不得不使用完全外部联接。在比较两个表的情况下，它很方便。例如，我比较的两个表来自不同的系统，所以我想获得以下信息：
- Table A has any rows that are not in Table B
- Table B has any rows that are not in Table A
- Duplicates in either Table A or Table B
- For matching rows whether values are different (Example: The table A and Table B both have Acct# 12345, LoanID abc123, but Interest Rate or Loan Amount is different
- 表 A 有任何不在表 B 中的行
- 表 B 有任何不在表 A 中的行
- 表 A 或表 B 中的重复项
- 对于匹配行的值是否不同（例如：表 A 和表 B 都有 Acct# 12345，LoanID abc123，但利率或贷款金额不同
In addition, I created an additional field in SELECT statement that uses a CASE statement to 'comment' why I am flagging this row. Example: Interest Rate does not match / The Acct doesn't exist in System A, etc.
此外，我在 SELECT 语句中创建了一个附加字段，该字段使用 CASE 语句来“评论”我标记该行的原因。示例：利率不匹配/系统 A 中不存在账户等。
Then saved it as a view. Now, I can use this view to either create a report and send it to users for data correction/entry or use it to pull specific population by 'comment' field I created using a CASE statement (example: all records with non-matching interest rates) in my stored procedure and automate correction, etc.
然后将其保存为视图。现在，我可以使用此视图创建报告并将其发送给用户进行数据更正/输入，或者使用它通过我使用 CASE 语句创建的“评论”字段来提取特定人群（例如：所有具有不匹配兴趣的记录率）在我的存储过程中并自动更正等。
If you want to see an example, let me know.
如果你想看一个例子，让我知道。
回答by kevchadders
The rare times i have used it has been around testing for NULLs on both sides of the join in case i think data is missing from the initial INNER JOIN used in the SQL i'm testing on.
我很少使用它来测试连接两侧的 NULL，以防我认为我正在测试的 SQL 中使用的初始 INNER JOIN 中缺少数据。
回答by Ashraf Abusada
In the rare times that I used Full Outer Joinit was for data analysis and comparison purpose such as when comparing two customers tables from different databases to find out duplicates in each table or to compare the two tables structures, or to find out null values in one table compared to the other, or finding missing information in one tables compared to the other.
我很少使用Full Outer Join它进行数据分析和比较，例如比较来自不同数据库的两个客户表以找出每个表中的重复项或比较两个表结构，或在比较的表中找出空值到另一个表，或者在一个表中找到与另一个表相比缺失的信息。
回答by gingerbreadboy
They're handy for finding orphaned data but I rarely use then in production code. I wouldn't be "always discouraged from using one"but I think in the real world they are less frequently the best solution compared to inners and left/right outers.
它们对于查找孤立数据很方便，但我很少在生产代码中使用。我不会“总是不鼓励使用一个”，但我认为在现实世界中，与内部和左/右外部相比，它们不是最好的解决方案。
