何时使用左外连接？ [英] When to use a left outer join?

问题描述

我不明白左外连接的概念，右外连接，或者为什么我们需要使用连接！我正在努力的问题，我正在工作的表格在这里： http://www.cs.ucc.ie/pipermail/cs2501.2011/attachments/20110323/4288bee0/attachment-0001.pdf 。

问题3（b）

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

问题3（c） -

在SQL中构造一个命令来解决以下查询，使用（i）连接方法和（ii）
子查询方法。 [10 Marks]
找到在
上工作超过20个小时的工作人员的身份名称电脑化项目

解决方案

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

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

  SELECT FNAME，LNAME，DNAME 
从
雇员内部员工在员工的身份部署= DEPARTMENT.DNUMBER 
  

这将导致一个记录集，如：

  FNAME LNAME DNAME 
 -----  - ---- ----- 
 John Smith Research 
 John Doe行政管理
  

我使用了一个 INNER JOIN 。 INNER JOIN 组合两个表格，以便在两个表格中匹配的仅记录被显示，并且 这个案例，在部门号码（员工的DNO，部门表中的DNUMBER）。

LEFT JOIN s当您在第一个表中有记录但可能不在第二个表中记录时，可以组合两个表。例如，假设您想要列出所有员工以及任何家属：

 将EMPLOYEE.FNAME作为employee_first，EMPLOYEE .LNAME为employee_last，DEPENDENT.FNAME为dependent_last，DEPENDENT.LNAME为dependent_last 
 FROM 
雇员内在员工依赖于EMPLOYEE.SSN = DEPENDENT.ESSN 
  

这里的问题是，如果员工不具有依赖关系，那么他们的记录根本就不会显示出来 - 因为在DEPENDENT表中没有匹配的记录。

所以，你使用一个左连接，保留所有数据在左（即第一个表），并在右（第二个表）中拉入任何匹配的数据：

  SELECT EMPLOYEE.FNAME作为employee_first，EMPLOYEE.LNAME作为employee_last，DEPENDENT.FNAME为dependent_first，DEPENDENT.LNAME为dependent_last 
 FROM 
雇员LEFT JOIN依赖于EMPLOYEE.SSN = DEPENDENT.ESSN 
  

现在我们得到了所有员工记录的。如果给定员工没有匹配的依赖项，则 dependent_first 和 dependent_last 字段将为空。 / p>

I dont 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 : http://www.cs.ucc.ie/pipermail/cs2501.2011/attachments/20110323/4288bee0/attachment-0001.pdf .

Question 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"

Question 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"

Can anyone please explain this to me simply?

解决方案

Joins are used to combine two related tables together.

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

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 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

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.

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

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屋！