SQL 中的 JOIN：INNER、LEFT、RIGHT、FULL、Cross、Self 和 Natural Join

在 SQL 中，JOIN 操作用于根据相关列将两个或多个表中的数据组合在一起。JOIN 是关系数据库中非常重要的概念，因为它允许我们从多个表中提取相关数据。本文将介绍 SQL 中的不同 JOIN 类型

什么是 JOIN？

JOIN 是一种 SQL 操作符，用于根据相关列将两个或多个表中的数据行组合在一起。在数据库设计中，数据通常分散在多个表中，通过 JOIN 操作可以将这些表中的数据组合起来，以便进行查询和分析。

INNER JOIN

定义

INNER JOIN 返回两个表中匹配的行。如果两个表中没有匹配的行，则不会在结果集中显示。

语法

sql">SELECT columns
FROM table1
INNER JOIN table2
ON table1.column = table2.column;

示例

假设我们有两个表：Employees 和 Departments。

sql">-- Employees 表
EmployeeID | Name      | DepartmentID
-------------------------------------
1          | John Doe  | 1
2          | Jane Smith| 2
3          | Sam Brown | 3-- Departments 表
DepartmentID | DepartmentName
-----------------------------
1            | HR
2            | IT
4            | Finance

使用 INNER JOIN：

sql">SELECT e.EmployeeID, e.Name, d.DepartmentName
FROM Employees e
INNER JOIN Departments d
ON e.DepartmentID = d.DepartmentID;

结果：

sql">EmployeeID | Name       | DepartmentName
----------------------------------------
1          | John Doe   | HR
2          | Jane Smith | IT

在这个示例中，只有在 Employees 和 Departments 表中都有匹配 DepartmentID 的行才会出现在结果集中。

LEFT JOIN

定义

LEFT JOIN（或 LEFT OUTER JOIN）返回左表中的所有行，即使在右表中没有匹配的行。对于在右表中没有匹配的行，结果集中包含 NULL 值。

语法

sql">SELECT columns
FROM table1
LEFT JOIN table2
ON table1.column = table2.column;

示例

使用相同的表：

sql">SELECT e.EmployeeID, e.Name, d.DepartmentName
FROM Employees e
LEFT JOIN Departments d
ON e.DepartmentID = d.DepartmentID;

结果：

sql">EmployeeID | Name       | DepartmentName
----------------------------------------
1          | John Doe   | HR
2          | Jane Smith | IT
3          | Sam Brown  | NULL

在这个示例中，所有 Employees 表中的行都会出现在结果集中，即使它们在 Departments 表中没有匹配的 DepartmentID。

RIGHT JOIN

定义

RIGHT JOIN（或 RIGHT OUTER JOIN）返回右表中的所有行，即使在左表中没有匹配的行。对于在左表中没有匹配的行，结果集中包含 NULL 值。

语法

sql">SELECT columns
FROM table1
RIGHT JOIN table2
ON table1.column = table2.column;

示例

使用相同的表：

sql">SELECT e.EmployeeID, e.Name, d.DepartmentName
FROM Employees e
RIGHT JOIN Departments d
ON e.DepartmentID = d.DepartmentID;

结果：

sql">EmployeeID | Name       | DepartmentName
----------------------------------------
1          | John Doe   | HR
2          | Jane Smith | IT
NULL       | NULL       | Finance

在这个示例中，所有 Departments 表中的行都会出现在结果集中，即使它们在 Employees 表中没有匹配的 DepartmentID。

FULL JOIN

定义

FULL JOIN（或 FULL OUTER JOIN）返回左表和右表中的所有行。如果在左表或右表中没有匹配的行，结果集中包含 NULL 值。

语法

sql">SELECT columns
FROM table1
FULL JOIN table2
ON table1.column = table2.column;

示例

使用相同的表：

sql">SELECT e.EmployeeID, e.Name, d.DepartmentName
FROM Employees e
FULL JOIN Departments d
ON e.DepartmentID = d.DepartmentID;

结果：

sql">EmployeeID | Name       | DepartmentName
----------------------------------------
1          | John Doe   | HR
2          | Jane Smith | IT
3          | Sam Brown  | NULL
NULL       | NULL       | Finance

在这个示例中，所有 Employees 和 Departments 表中的行都会出现在结果集中，即使它们在另一表中没有匹配的 DepartmentID。

CROSS JOIN

定义

CROSS JOIN 返回两个表的笛卡尔积，即每个表的每一行与另一个表的每一行组合在一起。CROSS JOIN 通常在没有 WHERE 子句的情况下使用，会生成非常大的结果集。

语法

sql">SELECT columns
FROM table1
CROSS JOIN table2;

示例

假设我们有两个表：Employees 和 Departments。

sql">-- Employees 表
EmployeeID | Name
-----------------
1          | John Doe
2          | Jane Smith
3          | Sam Brown-- Departments 表
DepartmentID | DepartmentName
-----------------------------
1            | HR
2            | IT
3            | Finance

使用 CROSS JOIN：

sql">SELECT e.EmployeeID, e.Name, d.DepartmentName
FROM Employees e
CROSS JOIN Departments d;

结果：

sql">EmployeeID | Name       | DepartmentName
----------------------------------------
1          | John Doe   | HR
1          | John Doe   | IT
1          | John Doe   | Finance
2          | Jane Smith | HR
2          | Jane Smith | IT
2          | Jane Smith | Finance
3          | Sam Brown  | HR
3          | Sam Brown  | IT
3          | Sam Brown  | Finance

在这个示例中，每个 Employees 表中的行与 Departments 表中的每一行组合在一起，生成了所有可能的行组合。

SELF JOIN

定义

SELF JOIN 是一种特殊的 JOIN，它将一个表与其自身进行连接。它通常用于在同一个表中查找相关数据。

语法

sql">SELECT a.columns, b.columns
FROM table a
JOIN table b
ON a.common_column = b.common_column;

示例

假设我们有一个 Employees 表，其中包含员工和他们的上级（经理）的信息。

sql">-- Employees 表
EmployeeID | Name       | ManagerID
-----------------------------------
1          | John Doe   | NULL
2          | Jane Smith | 1
3          | Sam Brown  | 1
4          | Lisa Ray   | 2

使用 SELF JOIN：

sql">SELECT e1.EmployeeID, e1.Name, e2.Name AS ManagerName
FROM Employees e1
LEFT JOIN Employees e2
ON e1.ManagerID = e2.EmployeeID;

结果：

sql">EmployeeID | Name       | ManagerName
-------------------------------------
1          | John Doe   | NULL
2          | Jane Smith | John Doe
3          | Sam Brown  | John Doe
4          | Lisa Ray   | Jane Smith

在这个示例中，我们使用 SELF JOIN 来查找每个员工的经理。

NATURAL JOIN

定义

NATURAL JOIN 是一种特殊的 JOIN，它会自动基于两个表中具有相同名称和数据类型的列进行连接。NATURAL JOIN 不需要显式指定连接条件。

语法

sql">SELECT columns
FROM table1
NATURAL JOIN table2;

示例

假设我们有两个表：Employees 和 Departments，并且它们有一个相同的列 DepartmentID。

sql">-- Employees 表
EmployeeID | Name      | DepartmentID
-------------------------------------
1          | John Doe  | 1
2          | Jane Smith| 2
3          | Sam Brown | 3-- Departments 表
DepartmentID | DepartmentName
-----------------------------
1            | HR
2            | IT
3            | Finance

使用 NATURAL JOIN：

sql">SELECT e.EmployeeID, e.Name, d.DepartmentName
FROM Employees e
NATURAL JOIN Departments d;

结果：

sql">EmployeeID | Name       | DepartmentName
----------------------------------------
1          | John Doe   | HR
2          | Jane Smith | IT
3          | Sam Brown  | Finance

在这个示例中，NATURAL JOIN 自动基于 DepartmentID 列进行连接。

总结

INNER JOIN：返回两个表中匹配的行。
LEFT JOIN：返回左表中的所有行，以及右表中匹配的行。对于左表中没有匹配的行，右表列包含 NULL。
RIGHT JOIN：返回右表中的所有行，以及左表中匹配的行。对于右表中没有匹配的行，左表列包含 NULL。
FULL JOIN：返回左右表中的所有行。对于没有匹配的行，另一表的列包含 NULL。
CROSS JOIN：返回两个表的笛卡尔积，即每个表的每一行与另一个表的每一行组合在一起。
SELF JOIN：将一个表与其自身进行连接，通常用于在同一个表中查找相关数据。
NATURAL JOIN：自动基于两个表中具有相同名称和数据类型的列进行连接。

通过理解这些不同类型的 JOIN，可以更有效地查询和分析关系数据库中的数据。