MySQL 什么时候使用左外连接？

When to use a left outer join?

提问by John Conrad

I don't understand the concept of a left outer join, a right outer join, or indeed why we need to use a join at all! The question I am struggling with and the table I am working from is here: Link

我不明白左外连接、右外连接的概念，或者根本不明白为什么我们需要使用连接！我正在努力解决的问题以及我正在使用的表格在这里：链接

Question 3(b)

问题 3(b)

Construct a command in SQL to solve the following query, explaining why it had to employ the (outer) join method. [5 Marks] “Find the name of each staff member and his/her dependent spouse, if any”

在 SQL 中构造一个命令来解决以下查询，解释为什么它必须使用（外部）连接方法。[5 分] “请找出每位工作人员及其受抚养配偶的姓名（如有）”

Question 3(c) -

问题 3(c) -

Construct a command in SQL to solve the following query, using (i) the join method, and (ii) the subquery method. [10 Marks] “Find the identity name of each staff member who has worked more than 20 hours on the Computerization Project”

在 SQL 中构造一个命令来解决以下查询，使用 (i) 连接方法和 (ii) 子查询方法。【10分】“找出每个在计算机化项目中工作超过20小时的工作人员的身份名称”

Can anyone please explain this to me simply?

谁能简单地向我解释一下？

回答by Jordan Reiter

Joins are used to combine two related tables together.

联接用于将两个相关表组合在一起。

In your example, you can combine the Employee table and the Department table, like so:

在您的示例中，您可以组合 Employee 表和 Department 表，如下所示：

SELECT FNAME, LNAME, DNAME
FROM
EMPLOYEE INNER JOIN DEPARTMENT ON EMPLOYEE.DNO=DEPARTMENT.DNUMBER

This would result in a recordset like:

这将导致像这样的记录集：

FNAME   LNAME   DNAME
-----   -----   -----
John    Smith   Research
John    Doe     Administration

I used an INNER JOINabove. INNER JOINs combine two tables so that onlyrecords with matches in both tables are displayed, and they are joinedin this case, on the department number (field DNO in Employee, DNUMBER in Department table).

我用了INNER JOIN上面的。INNER JOIN小号组合两个表，以便只显示与两个表中记录的比赛，他们都加入了在这种情况下，部门编号（以员工现场DNO，DNUMBER在Department表）。

LEFT JOINs allow you to combine two tables when you have records in the first table but might nothave records in the second table. For example, let's say you want a list of all the employees, plus any dependents:

LEFT JOIN当第一个表中有记录但第二个表中可能没有记录时，s 允许您合并两个表。例如，假设您想要一个包含所有员工以及任何家属的列表：

SELECT EMPLOYEE.FNAME as employee_first, EMPLOYEE.LNAME as employee_last, DEPENDENT.FNAME as dependent_last, DEPENDENT.LNAME as dependent_last
FROM
EMPLOYEE INNER JOIN DEPENDENT ON EMPLOYEE.SSN=DEPENDENT.ESSN

The problem here is that if an employee doesn'thave a dependent, then their record won't show up at all -- because there's no matching record in the DEPENDENT table.

这里的问题是，如果员工没有受抚养人，那么他们的记录根本不会显示——因为 DEPENDENT 表中没有匹配的记录。

So, you use a leftjoin which keeps all the data on the "left" (i.e. the first table) and pulls in any matching data on the "right" (the second table):

因此，您使用左连接将所有数据保留在“左侧”（即第一个表）并在“右侧”（第二个表）中提取任何匹配数据：

SELECT EMPLOYEE.FNAME as employee_first, EMPLOYEE.LNAME as employee_last, DEPENDENT.FNAME as dependent_first, DEPENDENT.LNAME as dependent_last
FROM
EMPLOYEE LEFT JOIN DEPENDENT ON EMPLOYEE.SSN=DEPENDENT.ESSN

Now we get allof the employee records. If there is no matching dependent(s) for a given employee, the dependent_firstand dependent_lastfields will be null.

现在我们得到了所有的员工记录。如果给定员工没有匹配的受抚养人，则dependent_first和dependent_last字段将为空。

回答by Johan

example(not using your example tables :-)

示例（不使用您的示例表:-)

I have a car rental company.

我有一家汽车租赁公司。

Table car
id: integer primary key autoincrement
licence_plate: varchar
purchase_date: date

Table customer
id: integer primary key autoincrement
name: varchar

Table rental
id: integer primary key autoincrement
car_id: integer
bike_id: integer
customer_id: integer
rental_date: date

Simple right? I have 10 records for cars because I have 10 cars.
I've been running this business for 10 years, so I've got 1000 customers.
And I rent the cars about 20x per year per cars = 10 years x 10 cars x 20 = 2000 rentals.

简单吧？我有 10 条汽车记录，因为我有 10 辆汽车。
我经营这家公司已经 10 年了，所以我有 1000 名客户。
我每年租车约 20 倍，每辆车 = 10 年 x 10 辆 x 20 = 2000 辆。

If I store everything in one big table I've got 10x1000x2000 = 20 million records.
If I store it in 3 tables I've got 10+1000+2000 = 3010 records.
That's 3 orders of magnitude, so that's why I use 3 tables.

如果我将所有内容都存储在一张大表中，我将得到 10x1000x2000 = 2000 万条记录。
如果我将它存储在 3 个表中，我有 10+1000+2000 = 3010 条记录。
这是 3 个数量级，这就是我使用 3 个表的原因。

But because I use 3 tables (to save space and time)I have to use joins in order to get the data out again
(at least if I want names and licence plates instead of numbers).

但是因为我使用了 3 个表（以节省空间和时间），所以我必须使用连接来再次获取数据
（至少如果我想要姓名和车牌而不是数字）。

Using inner joins

使用内连接

All rentals for customer 345?

客户 345 的所有租金？

SELECT * FROM customer
INNER JOIN rental on (rental.customer_id = customer.id)
INNER JOIN car on (car.id = rental.car_id)
WHERE customer.id = 345.

That's an INNER JOIN, because we onlywant to know about cars linked torentals linked tocustomers that actually happened.

这是一个INNER JOIN，因为我们只是想了解汽车linked to租赁linked to实际发生的客户。

Notice that we also have a bike_id, linking to the bike table, which is pretty similar to the car table but different. How would we get all bike + car rentals for customer 345.
We can tryand do this

请注意，我们还有一个自行车表，链接到自行车表，它与汽车表非常相似，但不同。我们如何获得客户 345 的所有自行车 + 汽车租赁。
我们可以尝试这样做

SELECT * FROM customer
INNER JOIN rental on (rental.customer_id = customer.id)
INNER JOIN car on (car.id = rental.car_id)
INNER JOIN bike on (bike.id = rental.bike_id)
WHERE customer.id = 345.

But that will give an empty set!!
This is because a rental can either be a bike_rental OR a car_rental, but not both at the same time.
And the non-working inner joinquery will only give results for all rentals where we rent out both a bike and a car in the same transaction.
We are trying to get and boolean ORrelationship using a boolean ANDjoin.

但这将给出一个空集！！
这是因为租赁可以是自行车租赁或汽车租赁，但不能同时是两者。
并且非工作inner join查询只会给出我们在同一笔交易中同时出租自行车和汽车的所有出租的结果。
我们正在尝试OR使用布尔AND连接来获取布尔关系。

Using outer joins

使用外连接

In order to solve this we need an outer join.

为了解决这个问题，我们需要一个outer join.

Let's solve it with left join

让我们解决它 left join

SELECT * FROM customer
INNER JOIN rental on (rental.customer_id = customer.id) <<-- link always
LEFT JOIN car on (car.id = rental.car_id) <<-- link half of the time
LEFT JOIN bike on (bike.id = rental.bike_id) <<-- link (other) 0.5 of the time.
WHERE customer.id = 345.

Look at it this way. An inner joinis an ANDand a left joinis a ORas in the following pseudocode:

这样看。An inner joinis an ANDand a left joinis aOR如以下伪代码所示：

if a=1 AND a=2 then {this is always false, no result}
if a=1 OR a=2 then  {this might be true or not}

If you create the tables and run the query you can see the result.

如果您创建表并运行查询，您可以看到结果。

on terminology

关于术语

A left joinis the same as a left outer join. A joinwith no extra prefixes is an inner joinThere's also a full outer join. In 25 years of programming I've never used that.

Aleft join与 a 相同left outer join。join没有额外前缀的Ainner join也是一个full outer join. 在 25 年的编程生涯中，我从未使用过它。

Why Left join

为什么左加入

Well there's two tables involved. In the example we linked
customer to rental with an inner join, in an inner join both tables mustlink so there is no difference between the left:customertable and the right:rentaltable.

嗯，涉及到两个表。在示例中，我们使用 将
客户链接到租赁inner join，在内部联接中，两个表都必须链接，因此left:customer表和right:rental表之间没有区别。

The next link was a left joinbetween left:rentaland right:car. On the left side all rows must link and the right side they don't have to. This is why it's a left join

下一个链接是left join介于left:rental和之间right:car。在左侧，所有行都必须链接，而右侧则不必链接。这就是为什么它是一个left join

回答by Oded

You use outer joins when you need allof the results from one of the join tables, whether there is a matching row in the other table or not.

当您需要一个连接表中的所有结果时，无论另一个表中是否有匹配的行，您都可以使用外部连接。

回答by onedaywhen

I think Question 3(b) is confusing because its entire premise wrong: you don't have to use an outer join to "solve the query" e.g. consider this (following the style of syntax in the exam paper is probably wise):

我认为问题 3(b) 令人困惑，因为它的整个前提是错误的：您不必使用外连接来“解决查询”，例如考虑这一点（遵循试卷中的语法风格可能是明智的）：

SELECT FNAME, LNAME, DEPENDENT_NAME
  FROM EMPLOYEE, DEPENDENT
 WHERE SSN = ESSN
       AND RELATIONSHIP = 'SPOUSE'
UNION 
SELECT FNAME, LNAME, NULL
  FROM EMPLOYEE
EXCEPT 
SELECT FNAME, LNAME, DEPENDENT_NAME
  FROM EMPLOYEE, DEPENDENT
 WHERE SSN = ESSN
       AND RELATIONSHIP = 'SPOUSE'

回答by Sheng Chen

In general: JOIN joints two tables together. Use INNER JOIN when you wanna "look up", like look up detailed information of any specific column. Use OUTER JOIN when you wanna "demonstrate", like list all the info of the 2 tables.

一般而言： JOIN 将两个表连接在一起。当您想“查找”时使用 INNER JOIN，例如查找任何特定列的详细信息。当您想“演示”时使用 OUTER JOIN，例如列出 2 个表的所有信息。