跳到主要内容

SQL 连接概述

在关系型数据库中，数据通常分布在多个表中。为了从这些表中提取有意义的信息，我们需要将多个表的数据组合在一起。SQL表连接（JOIN）就是实现这一目标的关键工具。通过连接，我们可以根据某些条件将两个或多个表中的行组合在一起。

什么是SQL连接？​

SQL连接是一种用于从多个表中检索数据的操作。它通过匹配两个表中的列值来组合行。连接操作的核心是定义表之间的关系，通常是通过外键（Foreign Key）来实现的。

连接的类型​

SQL支持多种类型的连接，每种连接都有其特定的用途：

  1. 内连接（INNER JOIN）：返回两个表中匹配的行。
  2. 左连接（LEFT JOIN）：返回左表中的所有行，以及右表中匹配的行。如果右表中没有匹配的行，则结果中右表的列将包含NULL。
  3. 右连接（RIGHT JOIN）：返回右表中的所有行，以及左表中匹配的行。如果左表中没有匹配的行，则结果中左表的列将包含NULL。
  4. 全外连接（FULL OUTER JOIN）：返回左表和右表中的所有行。如果某一行在另一个表中没有匹配的行，则结果中对应的列将包含NULL。
  5. 交叉连接（CROSS JOIN）：返回两个表的笛卡尔积，即左表中的每一行与右表中的每一行组合。

内连接（INNER JOIN）​

内连接是最常用的连接类型。它只返回两个表中匹配的行。

语法​

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

示例​

假设我们有两个表：employees 和 departments。

sql
-- employees 表
+----+----------+------------+
| id | name     | department_id |
+----+----------+------------+
| 1  | Alice    | 101        |
| 2  | Bob      | 102        |
| 3  | Charlie  | 103        |
+----+----------+------------+

-- departments 表
+-----+------------+
| id  | name       |
+-----+------------+
| 101 | HR         |
| 102 | Engineering|
| 103 | Marketing  |
+-----+------------+

我们可以使用内连接来获取每个员工的姓名及其所属部门的名称：

sql
SELECT employees.name, departments.name AS department
FROM employees
INNER JOIN departments
ON employees.department_id = departments.id;

输出​

sql
+----------+------------+
| name     | department |
+----------+------------+
| Alice    | HR         |
| Bob      | Engineering|
| Charlie  | Marketing  |
+----------+------------+

左连接（LEFT JOIN）​

左连接返回左表中的所有行，即使右表中没有匹配的行。

语法​

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

示例​

假设我们有一个新的员工 Dave，他没有分配部门：

sql
-- employees 表
+----+----------+------------+
| id | name     | department_id |
+----+----------+------------+
| 1  | Alice    | 101        |
| 2  | Bob      | 102        |
| 3  | Charlie  | 103        |
| 4  | Dave     | NULL       |
+----+----------+------------+

使用左连接来获取所有员工的姓名及其所属部门的名称：

sql
SELECT employees.name, departments.name AS department
FROM employees
LEFT JOIN departments
ON employees.department_id = departments.id;

输出​

sql
+----------+------------+
| name     | department |
+----------+------------+
| Alice    | HR         |
| Bob      | Engineering|
| Charlie  | Marketing  |
| Dave     | NULL       |
+----------+------------+

右连接（RIGHT JOIN）​

右连接返回右表中的所有行，即使左表中没有匹配的行。

语法​

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

示例​

假设我们有一个新的部门 Finance，但没有员工被分配到这个部门：

sql
-- departments 表
+-----+------------+
| id  | name       |
+-----+------------+
| 101 | HR         |
| 102 | Engineering|
| 103 | Marketing  |
| 104 | Finance    |
+-----+------------+

使用右连接来获取所有部门的名称及其所属员工的姓名：

sql
SELECT employees.name, departments.name AS department
FROM employees
RIGHT JOIN departments
ON employees.department_id = departments.id;

输出​

sql
+----------+------------+
| name     | department |
+----------+------------+
| Alice    | HR         |
| Bob      | Engineering|
| Charlie  | Marketing  |
| NULL     | Finance    |
+----------+------------+

全外连接（FULL OUTER JOIN）​

全外连接返回左表和右表中的所有行。如果某一行在另一个表中没有匹配的行，则结果中对应的列将包含NULL。

语法​

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

示例​

使用全外连接来获取所有员工和部门的信息：

sql
SELECT employees.name, departments.name AS department
FROM employees
FULL OUTER JOIN departments
ON employees.department_id = departments.id;

输出​

sql
+----------+------------+
| name     | department |
+----------+------------+
| Alice    | HR         |
| Bob      | Engineering|
| Charlie  | Marketing  |
| Dave     | NULL       |
| NULL     | Finance    |
+----------+------------+

交叉连接（CROSS JOIN）​

交叉连接返回两个表的笛卡尔积，即左表中的每一行与右表中的每一行组合。

语法​

sql
SELECT columns
FROM table1
CROSS JOIN table2;

示例​

使用交叉连接来获取所有员工和部门的组合：

sql
SELECT employees.name, departments.name AS department
FROM employees
CROSS JOIN departments;

输出​

sql
+----------+------------+
| name     | department |
+----------+------------+
| Alice    | HR         |
| Alice    | Engineering|
| Alice    | Marketing  |
| Alice    | Finance    |
| Bob      | HR         |
| Bob      | Engineering|
| Bob      | Marketing  |
| Bob      | Finance    |
| Charlie  | HR         |
| Charlie  | Engineering|
| Charlie  | Marketing  |
| Charlie  | Finance    |
| Dave     | HR         |
| Dave     | Engineering|
| Dave     | Marketing  |
| Dave     | Finance    |
+----------+------------+

实际应用场景​

场景1：员工与部门信息​

假设你是一家公司的人力资源经理，你需要生成一份报告，列出所有员工及其所属部门的名称。你可以使用内连接来实现这一目标。

场景2：未分配部门的员工​

假设你需要找出所有尚未分配部门的员工。你可以使用左连接，并过滤掉那些在右表中有匹配的行。

sql
SELECT employees.name
FROM employees
LEFT JOIN departments
ON employees.department_id = departments.id
WHERE departments.id IS NULL;

场景3：所有部门及其员工​

假设你需要列出所有部门，即使某些部门没有员工。你可以使用右连接来实现这一目标。

总结​

SQL连接是关系型数据库中非常重要的操作，它允许我们从多个表中组合数据。通过掌握不同类型的连接（内连接、左连接、右连接、全外连接和交叉连接），你可以灵活地处理各种数据查询需求。

提示

在实际应用中，选择合适的连接类型非常重要。内连接通常用于获取匹配的数据，而左连接和右连接则用于获取包含未匹配数据的完整结果集。

附加资源与练习​

  1. 练习1：创建一个包含 orders 和 customers 表的数据库，并使用内连接来获取每个订单的客户信息。
  2. 练习2：使用左连接来获取所有客户及其订单信息，即使某些客户没有订单。
  3. 练习3：使用全外连接来获取所有客户和订单的组合，包括没有订单的客户和没有客户的订单。

通过练习这些连接操作，你将更好地理解SQL连接的工作原理及其在实际应用中的价值。