MySQL 七种JOIN连接详解

在数据库操作中，JOIN 是一个非常重要的概念，用于从多个表中检索数据。本文将详细介绍 MySQL 中的各种 JOIN 类型，并通过具体的例子进行说明。

表结构和假数据

为了更好地理解各种 JOIN 类型，我们首先创建两个简单的表并插入一些假数据。

`employees` 表

employee_id	name	department_id
1	张三	1
2	李四	2
3	王五	3
4	赵六	4

`departments` 表

department_id	department_name
1	销售部
2	市场部
3	技术部
5	人力资源部

创建表和插入数据

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(50),
    department_id INT
);

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50)
);

INSERT INTO employees (employee_id, name, department_id) VALUES
(1, '张三', 1),
(2, '李四', 2),
(3, '王五', 3),
(4, '赵六', 4);

INSERT INTO departments (department_id, department_name) VALUES
(1, '销售部'),
(2, '市场部'),
(3, '技术部'),
(5, '人力资源部');

1. 内连接（`INNER JOIN`）

内连接返回两个表中字段匹配关系的记录。只有当两个表中的记录满足连接条件时，这些记录才会出现在结果集中。

SQL 示例

SELECT emp.name, dept.department_name
FROM employees emp
INNER JOIN departments dept
ON emp.department_id = dept.department_id;

结果

name	department_name
张三	销售部
李四	市场部
王五	技术部

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

左外连接返回左表（employees 表）的所有记录，即使在右表（departments 表）中没有匹配的记录。如果右表中没有匹配的记录，那么结果集中的右表字段将填充 NULL。

SQL 示例

SELECT emp.name, dept.department_name
FROM employees emp
LEFT JOIN departments dept
ON emp.department_id = dept.department_id;

结果

name	department_name
张三	销售部
李四	市场部
王五	技术部
赵六	NULL

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

右外连接返回右表（departments 表）的所有记录，即使在左表（employees 表）中没有匹配的记录。如果左表中没有匹配的记录，那么结果集中的左表字段将填充 NULL。

SQL 示例

SELECT emp.name, dept.department_name
FROM employees emp
RIGHT JOIN departments dept
ON emp.department_id = dept.department_id;

结果

name	department_name
张三	销售部
李四	市场部
王五	技术部
NULL	人力资源部

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

MySQL 不直接支持全外连接，但可以通过 LEFT JOIN 和 RIGHT JOIN 的 UNION 来实现。全外连接返回两个表中的所有记录，如果某一边没有匹配的记录，则使用 NULL 填充。

SQL 示例

SELECT emp.name, dept.department_name
FROM employees emp
LEFT JOIN departments dept
ON emp.department_id = dept.department_id
UNION
SELECT emp.name, dept.department_name
FROM employees emp
RIGHT JOIN departments dept
ON emp.department_id = dept.department_id;

结果

name	department_name
张三	销售部
李四	市场部
王五	技术部
赵六	NULL
NULL	人力资源部

5. 左连接加过滤（`LEFT JOIN` + `WHERE`）

这种情况下，我们使用左连接，但在 WHERE 子句中添加条件，以过滤出右表中没有匹配记录的行。

SQL 示例

SELECT emp.name, dept.department_name
FROM employees emp
LEFT JOIN departments dept
ON emp.department_id = dept.department_id
WHERE dept.department_name IS NULL;

结果

name	department_name
赵六	NULL

6. 右连接加过滤（`RIGHT JOIN` + `WHERE`）

这种情况下，我们使用右连接，但在 WHERE 子句中添加条件，以过滤出左表中没有匹配记录的行。

SQL 示例

SELECT emp.name, dept.department_name
FROM employees emp
RIGHT JOIN departments dept
ON emp.department_id = dept.department_id
WHERE emp.name IS NULL;

结果

name	department_name
NULL	人力资源部

7. 全外连接加过滤（`FULL JOIN` + `WHERE`）

这种情况下，我们使用全外连接，但在 WHERE 子句中添加条件，以过滤出两个表中都没有匹配记录的行。由于 MySQL 不直接支持 FULL JOIN，我们需要使用 LEFT JOIN 和 RIGHT JOIN 的 UNION 来实现。

SQL 示例

SELECT emp.name, dept.department_name
FROM employees emp
LEFT JOIN departments dept
ON emp.department_id = dept.department_id
WHERE dept.department_name IS NULL
UNION
SELECT emp.name, dept.department_name
FROM employees emp
RIGHT JOIN departments dept
ON emp.department_id = dept.department_id
WHERE emp.name IS NULL;