何时使用左外连接？

Question

我不明白左外连接、右外连接的概念，也不明白为什么我们需要使用连接！我正在努力解决的问题和我正在处理的表格在这里： 链接

问题 3(b)

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

问题 3(c) -

在 SQL 中构造一个命令来解决以下查询，使用 (i) join 方法， (ii) 子查询方法。 [10 分] “查找在该设备上工作超过 20 小时的每个工作人员的身份姓名 计算机化项目”

有人可以简单地向我解释一下吗？

Answer 1

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

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

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

这将产生如下记录集：

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

我在上面使用了 INNER JOIN。 INNER JOIN 组合两个表，以便仅显示两个表中都匹配的记录，并且在本例中，它们在部门上连接编号（员工表中的字段 DNO，部门表中的字段 DNUMBER）。

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

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

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

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

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

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

Answer 2

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

我有一家汽车租赁公司。

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

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

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

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

使用内部联接

客户 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.

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

请注意，我们还有一个 bike_id，链接到自行车表，它与汽车表非常相似，但有所不同。
我们如何为客户 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.

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

使用外连接

为了解决我们需要一个外部连接。

我们用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.

这样看。 内连接是一个AND，左连接是一个OR，如以下伪代码所示：

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}

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

术语

左连接与左外连接相同。
没有额外前缀的连接是内部连接
还有一个完全外部连接。在 25 年的编程生涯中我从未使用过它。

为什么左连接

嗯，涉及到两个表。在我们链接的示例中
通过内部联接从客户到租赁，在内部联接中，两个表必须链接，因此left:customer表和右：租赁表。

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

Answer 3

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

Answer 4

我认为问题 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'

Answer 5

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