Estimated time needed: 20 minutes
预计所需时间：20 分钟

SQL JOIN is a clause that combines rows from two or more tables based on a related column between them. The table's relationship is established by comparing the values in the columns. The purpose of using JOINs is to retrieve data from multiple tables in a single query. There are four types of JOINs in SQL: INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN.
SQL JOIN 是一个根据它们之间的相关列组合两个或多个表中的行的子句。表之间的关系是通过比较列中的值来建立的。使用 JOIN 的目的是在单个查询中从多个表中检索数据。SQL 中有四种 JOIN 类型：INNER JOIN、LEFT JOIN、RIGHT JOIN 和 FULL OUTER JOIN。

• INNER JOIN: Returns only the rows with matching values in both tables.
  INNER JOIN：只返回两个表中具有匹配值的行。
• LEFT JOIN: Returns all the rows from the left table and matching rows from the right table.
  LEFT JOIN：返回左表的所有行以及右表中匹配的行。
• RIGHT JOIN: Returns all the rows from the right table and matching rows from the left table.
  RIGHT JOIN：返回右表的所有行以及左表中匹配的行。
• FULL OUTER JOIN: Returns all the rows when a match in the left or right table.
  全外连接：当左表或右表中存在匹配时，返回所有行。

Objectives 目标

By the end of this lab, you'll be able to:
在本实验结束时，您将能够：

• Write SQL queries on multiple tables using INNER JOINS
  使用内连接在多个表上编写 SQL 查询
• Write SQL queries on multiple tables using OUTER JOINS
  使用外连接在多个表上编写 SQL 查询

Software Used in this Lab 本实验中使用的软件

In this lab, you will use MySQL. MySQL is a Relational Database Management System (RDBMS) designed to efficiently store, manipulate, and retrieve data.
在这个实验中，你将使用 MySQL。MySQL 是一种关系型数据库管理系统 (RDBMS)，设计用于高效地存储、操作和检索数据。

To complete this lab, you will utilize MySQL relational database service available as part of IBM Skills Network Labs (SN Labs) Cloud IDE. SN Labs is a virtual lab environment used in this course.
要完成此实验，您将使用作为 IBM 技能网络实验室 (SN Labs) 云集成开发环境 (Cloud IDE) 的一部分的 MySQL 关系数据库服务。SN Labs 是本课程中使用的虚拟实验室环境。

Database Used in this Lab 本次实验使用的数据库

The database used in this lab is internal. You will be working on a sample HR database. This HR database schema consists of five tables: EMPLOYEES, JOB_HISTORY, JOBS, DEPARTMENTS, and LOCATIONS. Each table has a few rows of sample data. The following diagram shows the tables for the HR database:
本实验使用的数据库是内部的。您将使用一个示例人力资源数据库。该人力资源数据库模式由五个表组成：EMPLOYEES（员工）、JOB_HISTORY（工作历史）、JOBS（工作）、DEPARTMENTS（部门）和 LOCATIONS（位置）。每个表都包含几行示例数据。下图显示了人力资源数据库的表：

In this lab, you will run through some SQL practice problems that will provide hands-on experience with the different kinds of join operations.
在本实验中，您将运行一些 SQL 练习题，以获得不同类型连接操作的实践经验。

NOTE: This lab requires you to have all five of these tables of the HR database populated with sample data on MySQL.
注意：本实验要求您在 MySQL 中为人力资源数据库的这五个表都填充示例数据。

Load the database 加载数据库

Using the skills acquired in the previous modules, you should first create the database in MySQL. Follow the steps below:
运用前几个模块所学的技能，您首先应该在 MySQL 中创建数据库。按照以下步骤操作：

1. Open the phpMyAdmin interface from the Skills Network Toolbox in Cloud IDE.
   从云 IDE 中的 Skills Network Toolbox 中打开 phpMyAdmin 界面。
2. Create a blank database named 'HR'. Use the script shared in the link below to create the required tables.
   创建一个名为"HR"的空数据库。使用以下链接中共享的脚本创建所需的表。
   Script_Create_Tables.sql
3. Download the files in the links below to your local machine (if not already done in previous labs).
   将以下链接中的文件下载到您的本地计算机（如果之前实验室尚未完成）。
   Departments.csv
   Jobs.csv
   JobsHistory.csv  工作历史.csv
   Locations.csv  位置.csv
   Employees.csv
4. Use these files to the interface as data for respective tables in the 'HR' database.
   使用这些文件作为界面数据，用于'HR'数据库中相应表的输入。

JOINS 连接

Let us see some examples of JOINS being used to query the data.
让我们看看一些使用连接查询数据的例子。

1. Retrieve the names and job start dates of all employees who work for department number 5.
   检索所有在部门编号为 5 的部门工作的员工的姓名和工作开始日期。

We need to use the Inner join operation with the EMPLOYEES table as the left table and the JOB_HISTORY table as the right table. The join will be made over employee ID, and the query response will be filtered for the Department ID value 5.
我们需要使用内连接操作，将 EMPLOYEES 表作为左表，将 JOB_HISTORY 表作为右表。连接将基于员工 ID 进行，查询结果将按部门 ID 值 5 进行过滤。
The query for this question will be as shown below.
这个问题中的查询将如下所示。

SELECT E.F_NAME,E.L_NAME, JH.START_DATE
FROM EMPLOYEES as E
INNER JOIN JOB_HISTORY as JH
ON E.EMP_ID=JH.EMPL_ID
WHERE E.DEP_ID ='5';

2. Retrieve employee ID, last name, department ID, and department name for all employees.
   检索所有员工的工作证号、姓氏、部门 ID 和部门名称。

For this, you must use the Left Outer Join operation with the EMPLOYEES table as the left table and the DEPARTMENTS table as the right table. The join will happen on the Department ID.
为此，你必须使用左外连接操作，将 EMPLOYEES 表作为左表，将 DEPARTMENTS 表作为右表。连接将在部门 ID 上进行。

Left join query retrieves all employees, including their department details if available. If an employee does not belong to any department, the department fields will be NULL.

The query will be written as follows:
查询将编写如下：

SELECT E.EMP_ID, E.L_NAME, E.DEP_ID, D.DEP_NAME
FROM EMPLOYEES AS E
LEFT OUTER JOIN DEPARTMENTS AS D
ON E.DEP_ID=D.DEPT_ID_DEP;

3. Retrieve the First name, Last name, and Department name of all employees.
   获取所有员工的姓名、姓氏和部门名称。

For this, you will use the Full Outer Join operation with the EMPLOYEES table as the left table and the DEPARTMENTS table as the right table. A full outer join in MySQL is implemented as a UNION of left and right outer joins.
为此，您将使用以 EMPLOYEES 表为左表、DEPARTMENTS 表为右表的完全外连接操作。在 MySQL 中，完全外连接是通过左外连接和右外连接的 UNION 实现的。

Full Outer Join query retrieves all employees and departments, showing all combinations. If an employee is not associated with a department, or a department has no employees, the missing fields will be NULL.

The query will be written as shown below.
查询将如下所示编写。

1. SELECT E.F_NAME, E.L_NAME, D.DEP_NAME
2. FROM EMPLOYEES AS E
3. LEFT OUTER JOIN DEPARTMENTS AS D
4. ON E.DEP_ID = D.DEPT_ID_DEP
6. UNION
8. SELECT E.F_NAME, E.L_NAME, D.DEP_NAME
9. FROM EMPLOYEES AS E
10. RIGHT OUTER JOIN DEPARTMENTS AS D
11. ON E.DEP_ID=D.DEPT_ID_DEP

Practice Problems 练习题

1. Retrieve the names, job start dates, and job titles of all employees who work for department number 5.
   获取为部门编号 5 工作的所有员工的姓名、工作开始日期和工作职位。
   Hint  提示

Perform an INNER JOIN with 3 tables: EMPLOYEES, JOB_HISTORY, JOBS.
对三个表：EMPLOYEES、JOB_HISTORY、JOBS 执行 INNER JOIN。
Solution  解决方案

1. select E.F_NAME,E.L_NAME, JH.START_DATE, J.JOB_TITLE
2. from EMPLOYEES as E
3. INNER JOIN JOB_HISTORY as JH on E.EMP_ID=JH.EMPL_ID
4. INNER JOIN JOBS as J on E.JOB_ID=J.JOB_IDENT
5. where E.DEP_ID ='5';
6. Retrieve employee ID, last name, and department ID for all employees but department names for only those born before 1980.
   检索所有员工的工作 ID、姓氏和部门 ID，但对于 1980 年之前出生的员工，只检索部门名称。
   Hint  提示

Use an AND in the LEFT OUTER JOIN clause.
在 LEFT OUTER JOIN 子句中使用 AND。
Solution  解决方案

1. SELECT E.EMP_ID, E.L_NAME, E.DEP_ID, D.DEP_NAME
2. FROM EMPLOYEES AS E
3. LEFT OUTER JOIN DEPARTMENTS AS D
4. ON E.DEP_ID = D.DEPT_ID_DEP
5. AND YEAR(E.B_DATE) < 1980;
6. Retrieve the first name and last name of all employees but department ID and department names only for male employees.
   检索所有员工的首名和姓氏，但对于男性员工仅检索部门 ID 和部门名称。
   Hint  提示

Add an AND in Query 3A to filter on male employees in the ON clause. Alternatively, you can also use Left Outer Join.
在查询 3A 的 ON 子句中添加 AND 以过滤男性员工。或者，你也可以使用左外连接。
Solution  解决方案

1. SELECT E.F_NAME, E.L_NAME, D.DEPT_ID_DEP, D.DEP_NAME
2. FROM EMPLOYEES AS E
3. LEFT OUTER JOIN DEPARTMENTS AS D
4. ON E.DEP_ID=D.DEPT_ID_DEP AND E.SEX = 'M'
6. UNION
8. SELECT E.F_NAME, E.L_NAME, D.DEPT_ID_DEP, D.DEP_NAME
9. from EMPLOYEES AS E
10. RIGHT OUTER JOIN DEPARTMENTS AS D
11. ON E.DEP_ID=D.DEPT_ID_DEP AND E.SEX = 'M';

Conclusion 结论

Congratulations! You have completed this lab and you are ready for the next topic.
恭喜！您已完成此实验，现在可以进入下一个主题。

You now can:  您现在可以：

• Query multiple tables using INNER JOINS
  使用 INNER JOIN 查询多个表
• Query multiple tables using LEFT/RIGHT OUTER JOINS
  使用 LEFT/RIGHT OUTER JOIN 查询多个表
• Query multiple tables using FULL OUTER JOINS
  使用 FULL OUTER JOINS 查询多个表

Author(s) 作者

Lakshmi Holla  拉克希米·霍拉

Malika Singla

Abhishek Gagneja  阿比什克·甘格贾

© IBM Corporation 2023. All rights reserved. © IBM Corporation 2023。保留所有权利。