有哪些使用 SQL 的 OUTER JOIN 的好例子？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2698302/
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
What are some good examples where SQL's OUTER JOIN is used?
提问by nonopolarity
I often get asked the questions in an interview that "what is an outer join in SQL"?
我经常在面试中被问到“什么是 SQL 中的外连接”这样的问题？
While it can be answered, I wonder what might be some classic and good real life examples where a (LEFT) OUTER JOIN is used?
虽然可以回答，但我想知道使用（LEFT）OUTER JOIN 的一些经典和良好的现实生活示例是什么？
采纳答案by Raj Kaimal
In the Northwind database on the Customers and Orders table.
在客户和订单表上的 Northwind 数据库中。
Doing an inner join will only give you customers that have placed orders.
进行内部联接只会为您提供已下订单的客户。
Doing an outer join will get allcustomers and orders for customers that have placed orders.
执行外部联接将获得所有客户和已下订单的客户的订单。
回答by CTDev
To add to Robin Day's answer, you can also use a Left Outer Join to grab only customers who have NOT placed orders by checking for NULL.
要添加到 Robin Day 的答案中，您还可以使用 Left Outer Join 通过检查 NULL 来仅获取未下订单的客户。
SELECT *
FROM Customer
LEFT OUTER JOIN Order
ON Customer.CustomerId = Order.CustomerId
WHERE Order.CustomerId IS NULL
回答by Pranay Rana
Following is the visual represntation of the left outer join
以下是左外连接的可视化表示
SELECT <select_list>
FROM Table_A A
LEFT JOIN Table_B B
ON A.Key = B.Key
read more about joins in the below article http://www.codeproject.com/KB/database/Visual_SQL_Joins.aspx( one of the best article must read )
在下面的文章http://www.codeproject.com/KB/database/Visual_SQL_Joins.aspx 中阅读有关连接的更多信息 （必须阅读的最佳文章之一）
回答by RedFilter
A LEFT OUTER JOINcan be used when you want all records from one table, as well as records from another table if any.
LEFT OUTER JOIN当您想要一个表中的所有记录以及另一个表中的记录（如果有）时，可以使用A。
E.g., given table Userand Address, where Addresshas a FK to Userand there could be 0 or more addresses per user:
例如，给定表Userand Address，其中Address有一个 FK toUser并且每个用户可能有 0 个或多个地址：
select *
from User u
left outer join Address a on u.UserID = a.UserID
This will ensure you get all Userrecords, regardless of whether there was a corresponding Addressrecord or not.
这将确保您获得所有User记录，无论是否有相应的Address记录。
If you want to show all Users that do not have addresses, you can do this:
如果要显示所有没有地址的用户，可以执行以下操作：
select *
from User u
left outer join Address a on u.UserID = a.UserID
where a.UserID is null
回答by Tom Cabanski
Classic example is cutomers and orders. Some customers have orders and others do not. You want to show a list of customers with total sales. So you do a left outer join from the customer to the order and get:
典型的例子是客户和订单。有些客户有订单，有些则没有。您想显示具有总销售额的客户列表。所以你做一个从客户到订单的左外连接并得到：
Customer A: $100; Customer B: $0; Customer C: $500
客户 A：100 美元；客户 B：$0；客户 C：500 美元
instead of:
代替：
Customer A: $100; Customer C: $500
客户 A：100 美元；客户 C：500 美元
回答by Oded
Here is an example:
下面是一个例子：
I need a list of all customers, with their vouchers, I also need the customers that never used vouchers.
我需要所有客户的清单，以及他们的代金券，我还需要从未使用过代金券的客户。
SELECT *
FROM Customer
LEFT OUTER JOIN Voucher
ON Customer.CustomerId = Voucher.CustomerId
回答by Robin Day
Get a list of all customers including any details of orders they have made. Some customers may not have made orders and therefore an INNER JOIN would exclude them from this list.
获取所有客户的列表，包括他们所下订单的任何详细信息。一些客户可能没有下订单，因此 INNER JOIN 会将他们排除在此列表之外。
SELECT
*
FROM
Customer
LEFT OUTER JOIN
Order
ON
Customer.CustomerId = Order.CustomerId
