什么时候使用左外连接? [英] When to use a left outer join?

问题描述

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

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

问题 3(b)

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

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"

问题 3(c) -

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

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"

谁能给我简单解释一下?

Can anyone please explain this to me simply?

推荐答案

Join 用于将两个相关的表组合在一起.

Joins are used to combine two related tables together.

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

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

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

我在上面使用了 INNER JOIN.INNER JOIN 合并两个表，以便仅 显示两个表中匹配的记录，并且在这种情况下，它们在部门中加入编号(Employee 中的字段 DNO，Department 表中的 DNUMBER).

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

LEFT JOINs 允许您在第一个表中有记录但可能没有在第二个表中有记录时合并两个表.例如，假设您想要一个包含所有员工以及所有家属的列表:

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

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 表中没有匹配的记录.

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

因此，您使用 left 连接，它将所有数据保留在左"(即第一个表)上，并在右"(第二个表)上拉入任何匹配数据:

So, you use a left join 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

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

Now we get all of the employee records. If there is no matching dependent(s) for a given employee, the dependent_first and dependent_last fields will be null.

这篇关于什么时候使用左外连接?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！