當前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

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

發布時間：2023/12/31 数据库 32 豆豆

生活随笔收集整理的這篇文章主要介紹了 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 #EmployeesON #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 #DepartmentsON #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 #DepartmentsON #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/