sql怎么实现2个表连接_多表上SQL连接：概述和实现

sql怎么实现2个表连接

SQL join multiple tables is one of the most popular types of statements executed while handling relational databases. As known, there are five types of join operations: Inner, Left, Right, Full and Cross joins.

SQL连接多个表是在处理关系数据库时执行的最流行的语句类型之一。 众所周知，共有五种连接操作：内部，左，右，完全和交叉连接。

In this article, we will explain the meaning of Joins in SQL, we will describe each one of the Join operation types and we will show the main use cases where it is used by providing examples.

在本文中，我们将解释SQL中Joins的含义，我们将描述每种Join操作类型，并通过提供示例来说明使用它的主要用例。

为什么要使用联接？ (Why using Joins?)

Joins are used to combine the rows from multiple tables using mutual columns. As an example, assume that you have two tables within a database; the first table stores the employee’s information while the second stores the department’s information, and you need to list the employees with the information of the department where they are working. In that case, you must find a way to SQL Join multiple tables to generate one result set that contains information from these tables. Noting that joins can be applied over more than two tables.

联接用于使用相互的列组合来自多个表的行。 例如，假设您在一个数据库中有两个表。 第一个表存储员工的信息，第二个表存储部门的信息，您需要列出员工及其所在部门的信息。 在这种情况下，您必须找到一种SQL连接多个表的方法，以生成一个包含这些表中信息的结果集。 请注意，联接可以应用于两个以上的表。

To apply join between two tables, one table must contain a column that is a reference for the other table. In the example above, the Employees table must have a column that contain a reference key for the department (ex: Department id).

若要在两个表之间应用联接，一个表必须包含一列，该列是另一张表的引用。 在上面的示例中， Employees表必须具有一列，其中包含部门的参考键（例如： Department id ）。

As mentioned above, there are multiple approaches to SQL join multiple tables. We will describe each approach briefly in the next sections.

如上所述，SQL有多种连接多个表的方法。 我们将在下一部分中简要描述每种方法。

创建示例中使用的表 (Creating tables used in examples)

To illustrate different types of joins, we will create Employees and Departments tables as following:

为了说明不同类型的联接，我们将创建雇员和部门表，如下所示：

• Employees员工
  • Employee_idEmployee_id
  • Employee_name员工名
  • Employee_DOBEmployee_DOB
  • Department_IdDepartment_Id
• Departments部门
  • Department_idDepartment_id
  • Department_Name部门名称

There are four departments defined within the Departments table:

在“ 部门”表中定义了四个部门：

• Human resources
  人力资源
• Development
  发展历程
• Sales
  营业额
• Technical Support
  技术支援

And there are five employees defined within the Employees table:

在“ 雇员”表中定义了五名雇员：

• Alan Smith (Department: Human Resources)
  艾伦·史密斯（部门：人力资源）
• Sultan Nader (Department: Human Resources)
  苏丹·纳德（部门：人力资源）
• Mohd Rasheed (Department: Development)
  Mohd Rasheed（部门：发展部）
• Brian Wallace (Department: Sales)
  Brian Wallace（部门：销售）
• Peter Hilton (Not assigned to a department until now)
  彼得·希尔顿（目前尚未分配给部门）

We have used the following commands to create this example:

我们使用以下命令来创建此示例：

-- Create employees table
CREATE TABLE #Employees(Employee_id int, Employee_name varchar(250), Employee_DOB date, Department_ID int)
    
-- Create departments table
CREATE TABLE #Departments(Department_id int, Department_Name varchar(250))
    
-- Insert values into departments table
INSERT INTO #Departments(Department_id,Department_Name)
VALUES(1,'Human Resources'), (2,'Development'), (3,'Sales'), (4, 'Technical Support')
    
-- Insert values into employees table
INSERT INTO #Employees(Employee_id,Employee_name, Employee_DOB,Department_ID)
VALUES (1,'Alan Smith','19890101',1),
       (2,'Sultan Nader','19920101',1),
       (3,'Mohd Rasheed','19990101',2),
       (4,'Brian Wallace','19790101',3),
       (5,'Peter Hilton','19860101',NULL)

内部联接 (INNER JOIN)

Inner join is the most popular join type, it selects the rows where values are the mutual columns values are matched. If we apply an inner join to the Employees-Departments example, it will only return the employees working within departments that have a row within the Departments table:

内部联接是最流行的联接类型，它选择值是相互的列值匹配的行。 如果我们将内部联接应用于Employees-Departments示例，它将仅返回在Departments表中具有一行的部门内工作的雇员：

Back to the Employees and Departments tables, to select the employees that are working within departments we can use the following query:

返回“ 员工和部门”表，要选择部门内正在工作的员工，我们可以使用以下查询：

SELECT Employee_id,Employee_name, Employee_DOB, Department_Name
FROM #Departments INNER JOIN #Employees
                ON #Departments.Department_id = #Employees.Department_ID

As shown in the query above, first we need to specify the columns we want to retrieve within the SELECT clause, then we need to specify the tables we need to read from and to specify the join type within the FROM clause, also we need to specify the columns used to perform the join operation after the ON keyword. The result of the query mentioned is as shown in the following screenshot:

如上面的查询所示，首先我们需要在SELECT子句中指定要检索的列，然后我们需要指定要从中读取的表，并在FROM子句中指定联接类型，还需要在ON关键字之后指定用于执行联接操作的列。 所提到的查询结果如以下屏幕快照所示：

From the screenshot above, we can see that the fifth employee is not shown in the result since it is not assigned to any department. Also, we can note that the Department_Name is retrieved instead of the Department id.

从上面的屏幕截图中，我们可以看到结果中未显示第五名员工，因为它没有分配给任何部门。 另外，我们可以注意到， 部门名称进行检索，而不是部门ID。

左加入 (LEFT JOIN)

Another SQL join multiple tables approach, is LEFT JOIN which is used to retrieve all rows from the first table mentioned in the FROM clause in addition to the matched rows from the second table:

另一个SQL连接多表方法是LEFT JOIN，它用于从FROM子句中提到的第一个表中检索所有行，以及第二个表中的匹配行：

Back to the Employees and Departments tables, if we need to select all employees listed within the Employees table and to mention the department name if exists, we can use the following query:

返回雇员和部门表，如果我们需要选择“ 雇员”表中列出的所有雇员，并提及部门名称（如果存在），则可以使用以下查询：

SELECT Employee_id,Employee_name, Employee_DOB, Department_Name
FROM #Employees LEFT JOIN #Departments
                ON #Departments.Department_id = #Employees.Department_ID

The query result is as shown in the following screenshot:

查询结果如以下屏幕快照所示：

As we can see, the query result returned all five employees listed within the table with a NULL value in the Department_Name column in the fifth row since Peter Hilton is not assigned to any department yet.

正如我们所看到的，由于尚未将Peter Hilton分配给任何部门，因此查询结果返回了表中列出的所有五名员工，并且在第五行的Department _ Name列中使用NULL值。

正确加入 (RIGHT JOIN)

The next SQL join multiple tables approach is RIGHT JOIN, which is very similar to LEFT JOIN since it returns all rows from the table listed at the right of the JOIN operator with the matched values from the table listed at the left:

下一个SQL连接多表方法是RIGHT JOIN，它与LEFT JOIN非常相似，因为它返回JOIN运算符右侧列出的表中的所有行，并返回左侧列出的表中的匹配值：

Back to the Employees and Departments tables, if we need to select the employees that are working within departments, in addition to departments that does not have any employees, we can use the following query:

返回“ 员工和部门”表，如果我们需要选择部门内工作的员工，除了没有员工的部门之外，我们还可以使用以下查询：

SELECT Employee_id,Employee_name, Employee_DOB, Department_Name
FROM #Employees RIGHT JOIN #Departments
                ON #Departments.Department_id = #Employees.Department_ID

As shown in the screenshot below, the query returned the same rows of the INNER JOIN query in addition to the Technical support department that doesn’t have any employee:

如下面的屏幕快照所示，除了没有员工的技术支持部门外，该查询还返回了INNER JOIN查询的相同行：

完全加入 (FULL OUTTER JOIN)

FULL OUTTER JOIN is another approach used to SQL join multiple tables. It returns all matched rows between both tables specified in the JOIN operation in addition to all unmatched rows from the first and second tables:

FULL OUTTER JOIN是用于SQL连接多个表的另一种方法。 除了第一个和第二个表中所有不匹配的行外，它还返回在JOIN操作中指定的两个表之间的所有匹配行：

Back to the Employees and Departments tables, the Full join query will return all employees working within departments plus all employees that are not assigned and all departments that doesn’t contain any employee:

返回到“ 员工和部门”表，“完全联接”查询将返回在部门内工作的所有员工以及未分配的所有员工和不包含任何员工的所有部门：

SELECT Employee_id,Employee_name, Employee_DOB, Department_Name
FROM #Employees FULL JOIN #Departments #Departments.Department_id = #Employees.Department_ID
 

From the screenshot below, we can see that Peter Hilton is with no value in the Department_Name field, and Technical support department is shown with no employee information:

从下面的屏幕截图中，我们可以看到Peter Hilton在Department_Name字段中没有任何值，并且显示的技术支持部门没有任何员工信息：

交叉加入 (CROSS JOIN)

The last approach used to SQL Join multiple tables is CROSS join which is a bit different from the other Join operations. It is used to create a combination of two different sets without have mutual columns. As an example, if we need to create a combination of all departments with all employees.

用于SQL连接多个表的最后一种方法是CROSS连接，这与其他Join操作有些不同。 它用于创建两个不同集合的组合而没有相互的列。 例如，如果我们需要创建所有部门和所有员工的组合。

Example:

例：

SELECT Employee_id,Employee_name, Employee_DOB, Department_Name
FROM #Employees CROSS JOIN #Departments
 

Result:

结果：

结论 (Conclusion)

Joining table is one of the main uses of SQL language. In this article, we have explained why using Joins, and we illustrated five different approaches to SQL Join multiple tables by providing some examples. We noted that Inner, Left, Right, and Full joins require mutual columns between tables while Cross join is to multiply to rows of the first table with the ones stored in the second table.

连接表是SQL语言的主要用途之一。 在本文中，我们解释了为什么使用Joins，并通过提供一些示例说明了五种不同SQL Join多个表方法。 我们注意到，内部联接，左联接，右联接和完全联接要求表之间具有相互的列，而交叉联接将使用存储在第二个表中的行乘以第一个表的行。

翻译自: https://www.sqlshack.com/different-approaches-to-sql-join-multiple-tables/

sql怎么实现2个表连接