MySQL多表连接查询详解

MySQL多表连接查询详解：INNER JOIN、LEFT JOIN、RIGHT JOIN与FULL JOIN

在MySQL数据库操作中，单表查询往往无法满足复杂的业务需求，多表连接查询是实现数据关联分析的核心手段。常见的多表连接方式包括 INNER JOIN、LEFT JOIN、RIGHT JOIN 和 FULL JOIN，它们各自适用于不同的业务场景，掌握这些连接方式能大幅提升数据查询的灵活性和效率。

一、多表连接的基础概念

多表连接的本质是基于一个或多个共同字段，将多个表的数据关联起来，形成一个包含联合信息的结果集。连接查询的核心是连接条件，通常以两个表的关联字段（如主键与外键）作为匹配依据。

为了方便后续示例演示，我们先创建两个测试表并插入数据：

1. 部门表 dept（父表）

CREATE TABLE `dept` (
`dept_id` INT PRIMARY KEY AUTO_INCREMENT COMMENT '部门ID',
`dept_name` VARCHAR(50) NOT NULL COMMENT '部门名称'
);
INSERT INTO `dept` VALUES (1, '研发部'), (2, '市场部'), (3, '财务部');

2. 员工表 emp（子表）

CREATE TABLE `emp` (
`emp_id` INT PRIMARY KEY AUTO_INCREMENT COMMENT '员工ID',
`emp_name` VARCHAR(50) NOT NULL COMMENT '员工姓名',
`dept_id` INT COMMENT '所属部门ID',
FOREIGN KEY (`dept_id`) REFERENCES `dept`(`dept_id`)
);
INSERT INTO `emp` VALUES (1, '张三', 1), (2, '李四', 1), (3, '王五', 2), (4, '赵六', NULL);

二、内连接：INNER JOIN

INNER JOIN 也叫等值连接，是最常用的连接方式。它只返回两个表中满足连接条件的匹配行，不匹配的行将被过滤掉。

1. 语法格式

SELECT 字段列表
FROM 表1
INNER JOIN 表2
ON 表1.关联字段 = 表2.关联字段;

2. 核心特点

• 只保留两个表的交集数据。
• 可省略 INNER 关键字，直接写 JOIN。

3. 实战示例

查询每个员工的姓名及其所属部门名称：

SELECT e.emp_name, d.dept_name
FROM emp e
INNER JOIN dept d
ON e.dept_id = d.dept_id;

4. 执行结果

三、左连接：LEFT JOIN

LEFT JOIN 也叫左外连接，它以左表为基准，返回左表的所有行，以及右表中满足连接条件的匹配行。如果右表没有匹配的数据，对应的字段将填充为 NULL。

1. 语法格式

SELECT 字段列表
FROM 表1
LEFT JOIN 表2
ON 表1.关联字段 = 表2.关联字段;

2. 核心特点

• 左表数据全部保留，右表匹配不到则补 NULL。
• LEFT JOIN 等价于 LEFT OUTER JOIN，OUTER 可省略。

3. 实战示例

查询所有员工的姓名及其所属部门名称（包括无部门的员工）：

SELECT e.emp_name, d.dept_name
FROM emp e
LEFT JOIN dept d
ON e.dept_id = d.dept_id;

4. 执行结果

四、右连接：RIGHT JOIN

RIGHT JOIN 也叫右外连接，它以右表为基准，返回右表的所有行，以及左表中满足连接条件的匹配行。如果左表没有匹配的数据，对应的字段将填充为 NULL。

1. 语法格式

SELECT 字段列表
FROM 表1
RIGHT JOIN 表2
ON 表1.关联字段 = 表2.关联字段;

2. 核心特点

• 右表数据全部保留，左表匹配不到则补 NULL。
• RIGHT JOIN 等价于 RIGHT OUTER JOIN，OUTER 可省略。

3. 实战示例

查询所有部门的名称及其对应的员工姓名（包括无员工的部门）：

SELECT e.emp_name, d.dept_name
FROM emp e
RIGHT JOIN dept d
ON e.dept_id = d.dept_id;

4. 执行结果

五、全连接：FULL JOIN

FULL JOIN 也叫全外连接，它返回左表和右表的所有行。当某一行在另一个表中没有匹配数据时，对应的字段填充为 NULL。

1. 语法格式

SELECT 字段列表
FROM 表1
FULL JOIN 表2
ON 表1.关联字段 = 表2.关联字段;

2. 核心特点

• 保留两个表的所有数据，等价于 LEFT JOIN 和 RIGHT JOIN 的并集。
• 注意：MySQL 原生不支持 FULL JOIN，需要通过 UNION 关键字联合 LEFT JOIN 和 RIGHT JOIN 的结果来实现。

3. 实战示例

查询所有员工和所有部门的关联数据（包括无部门的员工和无员工的部门）：

— 左连接结果
SELECT e.emp_name, d.dept_name
FROM emp e
LEFT JOIN dept d
ON e.dept_id = d.dept_id
UNION
— 右连接结果（排除重复数据）
SELECT e.emp_name, d.dept_name
FROM emp e
RIGHT JOIN dept d
ON e.dept_id = d.dept_id;

4. 执行结果

六、四种连接方式的核心区别

七、多表连接的最佳实践

总结

多表连接查询是MySQL的核心技能之一，INNER JOIN 适用于获取两表的交集数据，LEFT JOIN 和 RIGHT JOIN 适用于保留某一侧表的全部数据，FULL JOIN 则用于获取两表的完整数据。在实际开发中，需根据业务需求选择合适的连接方式，同时注意优化查询性能，才能高效地实现数据关联分析。