基本概念

在关系型数据库中，表与表之间通常通过某些共同的字段（如主键和外键）关联起来。连接（JOIN）操作就是基于这些关联字段，将两个或多个表中的数据组合在一起。

1. 内连接（INNER JOIN）

概念：

• 只返回两个表中匹配的记录。
• 如果某一行在任一表中没有匹配项，则该行不会出现在结果集中。

使用场景：

• 当你只关心两个表中都存在的相关数据时使用。
• 例如，查找所有有订单的客户。

示例：

假设有两个表：

• ​**customers**​（客户表）

  id	name
  1	Alice
  2	Bob
  3	Charlie

• ​**orders**​（订单表）

  id	customer_id	amount
  101	1	500
  102	1	300
  103	2	400

查询：

sql

SELECT customers.name, orders.amount
FROM customers
INNER JOIN orders ON customers.id = orders.customer_id;

结果：

name	amount
Alice	500
Alice	300
Bob	400

解释：

• 只有 customers 表中 ID 为 1 和 2 的客户在 orders 表中有对应的订单，因此只有这些记录出现在结果中。
• Charlie 没有订单，所以不出现在结果中。

2. 左连接（LEFT JOIN 或 LEFT OUTER JOIN）

概念：

• 返回左表中的所有记录，以及右表中匹配的记录。
• 如果右表中没有匹配项，则结果中对应的右表字段为 NULL。

使用场景：

• 当你需要保留左表中的所有数据，即使右表中没有对应的数据时使用。
• 例如，列出所有客户及其订单（包括没有订单的客户）。

示例：

使用上面的 customers 和 orders 表。

查询：

sql

SELECT customers.name, orders.amount
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id;

结果：

name	amount
Alice	500
Alice	300
Bob	400
Charlie	NULL

解释：

• Alice 和 Bob 在 orders 表中有对应的订单，因此显示他们的订单金额。
• Charlie 没有订单，因此 amount 字段为 NULL。

3. 右连接（RIGHT JOIN 或 RIGHT OUTER JOIN）

概念：

• 返回右表中的所有记录，以及左表中匹配的记录。
• 如果左表中没有匹配项，则结果中对应的左表字段为 NULL。

使用场景：

• 当你需要保留右表中的所有数据，即使左表中没有对应的数据时使用。
• 例如，列出所有订单及其客户（包括没有对应客户的订单，这种情况较少见）。

示例：

使用上面的 customers 和 orders 表。

查询：

sql

SELECT customers.name, orders.amount
FROM customers
RIGHT JOIN orders ON customers.id = orders.customer_id;

结果：

name	amount
Alice	500
Alice	300
Bob	400

解释：

• 所有订单都对应有客户，因此结果与内连接类似。
• 如果 orders 表中有订单的客户 ID 在 customers 表中不存在，则该订单的 name 字段会显示为 NULL。

4. 外连接（FULL JOIN 或 FULL OUTER JOIN）

概念：

• 返回两个表中的所有记录。
• 如果某一表中没有匹配项，则结果中对应的字段为 NULL。

使用场景：

• 当你需要保留两个表中的所有数据，无论它们是否匹配时使用。
• 例如，列出所有客户和所有订单，无论客户是否有订单，或者订单是否有对应的客户。

注意：

• ​MySQL 不直接支持 FULL JOIN，但可以通过组合 LEFT JOIN 和 RIGHT JOIN 并使用 UNION 来实现类似效果。

示例：

使用上面的 customers 和 orders 表。

查询：

sql

SELECT customers.name, orders.amount
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id

UNION

SELECT customers.name, orders.amount
FROM customers
RIGHT JOIN orders ON customers.id = orders.customer_id;

结果：

name	amount
Alice	500
Alice	300
Bob	400
Charlie	NULL

解释：

• 由于 orders 表中的所有订单都对应有客户，因此 FULL JOIN 的结果与 LEFT JOIN 类似。
• 如果有订单没有对应的客户，或者有客户没有订单，这些记录都会出现在结果中，缺失的部分用 NULL 填充。

实际应用中的选择

• ​INNER JOIN：

  • 当你只关心两个表中都存在的数据时使用。
  • 例如，查找有订单的客户。

• ​LEFT JOIN：

  • 当你需要保留左表中的所有数据，即使右表中没有对应的数据时使用。
  • 例如，列出所有员工及其部门信息，包括那些没有分配部门的员工。

• ​RIGHT JOIN：

  • 当你需要保留右表中的所有数据，即使左表中没有对应的数据时使用。
  • 例如，列出所有部门及其员工，包括那些没有员工的部门（这种情况较少见）。

• ​FULL JOIN：

  • 当你需要保留两个表中的所有数据，无论它们是否匹配时使用。
  • 例如，列出所有客户和所有订单，无论是否有对应关系。

更多示例

假设有以下两个表：

• ​**departments**​（部门表）

  dept_id	dept_name
  1	人力资源部
  2	市场部
  3	技术部

• ​**employees**​（员工表）

  emp_id	emp_name	dept_id
  101	Alice	1
  102	Bob	2
  103	Charlie	2
  104	David	3

使用 LEFT JOIN 查找所有部门及其员工

查询：

sql

SELECT departments.dept_name, employees.emp_name
FROM departments
LEFT JOIN employees ON departments.dept_id = employees.dept_id;

结果：

dept_name	emp_name
人力资源部	Alice
市场部	Bob
市场部	Charlie
技术部	David

注意：如果某个部门没有员工，emp_name 将为 NULL。

使用 RIGHT JOIN 查找所有员工及其部门

查询：

sql

SELECT departments.dept_name, employees.emp_name
FROM departments
RIGHT JOIN employees ON departments.dept_id = employees.dept_id;

结果：

dept_name	emp_name
人力资源部	Alice
市场部	Bob
市场部	Charlie
技术部	David

解释：由于所有员工都有对应的部门，结果与内连接类似。

使用 FULL JOIN 查找所有部门和员工

查询​（在 MySQL 中需要使用 UNION 实现）：

sql

SELECT departments.dept_name, employees.emp_name
FROM departments
LEFT JOIN employees ON departments.dept_id = employees.dept_id

UNION

SELECT departments.dept_name, employees.emp_name
FROM departments
RIGHT JOIN employees ON departments.dept_id = employees.dept_id;

结果：

dept_name	emp_name
人力资源部	Alice
市场部	Bob
市场部	Charlie
技术部	David

注意：在当前数据中，所有部门和员工都有对应关系，因此 FULL JOIN 的效果与 INNER JOIN 类似。如果某些部门没有员工，或某些员工没有部门，这些记录将会出现在结果中，缺失的部分用 NULL 填充。

总结

• ​INNER JOIN：只返回两个表中都存在的相关数据。
• ​LEFT JOIN：返回左表的所有数据及右表中匹配的数据，右表无匹配则为 NULL。
• ​RIGHT JOIN：返回右表的所有数据及左表中匹配的数据，左表无匹配则为 NULL。
• ​FULL JOIN：返回两个表中的所有数据，匹配的和不匹配的都包括，缺失部分用 NULL 填充。

选择哪种连接类型取决于你的具体需求：

• ​需要所有左表数据，无论右表是否有匹配 → ​LEFT JOIN
• ​需要所有右表数据，无论左表是否有匹配 → ​RIGHT JOIN
• ​只需要两个表中都有的数据 → ​INNER JOIN
• ​需要两个表中的所有数据，无论是否有匹配 → ​FULL JOIN​（在 MySQL 中需用 UNION 实现）

另一个小练习

假设有以下两个表：

• ​**students**​（学生表）

  student_id	name
  1	小明
  2	小红
  3	小刚

• ​**scores**​（成绩表）

  score_id	student_id	score
  101	1	85
  102	1	90
  103	2	78

查询所有学生及其成绩（包括没有成绩的学生）

查询：

sql

SELECT students.name, scores.score
FROM students
LEFT JOIN scores ON students.student_id = scores.student_id;

结果：

name	score
小明	85
小明	90
小红	78
小刚	NULL

解释：

• 小明有两个成绩，小红有一个成绩，小刚没有成绩，因此 score 为 NULL。