MySQL中如何使用 INNER JOIN、LEFT JOIN、RIGHT JOIN 和 聚合数据 的详细讲解

在常见的Java后端项目中，需要数据库存储用户数据，这时开发人员会根据数据的种类进行多张表存储，通过主要数据的ID为线索，关联其他表的数据。

一、JOIN关键字

在使用MySQL查询，会存在业务要将多张表的数据组合在一起展示，这就会使用到 JOIN 关键字，这个关键字的目的主要是将多张表的数据组合到一起展示。

常见有三种类型，如下表所示，这个关键字就是将需要字段的表按顺序组合成一张表展示。

JOIN类型	展示结果包含字段	数据行不匹配时
INNER JOIN	两表都匹配的行	不显示
LEFT JOIN	左表所有行 + 匹配的右表行	右表列显示NULL
RIGHT JOIN	右表所有行 + 匹配的左表行	左表列显示NULL

1、在使用 JOIN 关键字时有以下几点注意事项：

• INNER JOIN 适用于只关心有完整关联数据的场景。
• LEFT JOIN 适用于需要保留主表所有记录的场景（更常用）。
• RIGHT JOIN 通常可以避免，通过调整表顺序改用 LEFT JOIN 实现。
• JOIN顺序很重要：从主表开始，逐步连接相关表。
• 连接条件：确保每个JOIN都有明确的ON条件。
• 性能考虑：大表JOIN时，确保连接字段有索引。
• NULL处理：LEFT JOIN 可能导致右表字段为 NULL ，使用 IFNULL 或 COALESCE 处理。
• 聚合与JOIN：先 JOIN 再 GROUP BY ，避免在 JOIN 前聚合。

2、在使用时，LEFT JOIN 比 RIGHT JOIN 更常用：

• SQL查询通常有主从表的概念。
• 从左到右的阅读顺序更自然。
• 查询优化器对 LEFT JOIN 处理更好（重点）。

---

1、数据库表

下面我们建三张表来测试这几个关键字的作用，以下代码都是使用这三张表数据。

员工表：tb_staff

货品表：tb_goods

库存表：tb_count

---

2、INNER JOIN（内连接）

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

1、两表内连接：只显示两表中都有匹配记录的行。

SELECT a.name as staff_name, a.sex, b.name as goods_name, b.type as good_type
FROM tb_staff a
INNER JOIN tb_goods b ON a.id = b.staff_id;

2、三表内连接：如果某员工（钱七）没有管理商品，则该员工不会出现在结果中。

SELECT a.name as staff_name, a.sex, b.name as goods_name, b.type as good_type, c.count as count
FROM tb_staff a
INNER JOIN tb_goods b ON a.id = b.staff_id
INNER JOIN tb_count c ON b.id = c.goods_id

---

3、LEFT JOIN（左连接）

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

1、两表左连接：保证左表的所有记录都会出现，员工钱七出现，右表无匹配时显示为 NULL 。

SELECT a.name as staff_name, a.sex, b.name as goods_name, b.type as good_type
FROM tb_staff a
LEFT JOIN tb_goods b ON a.id = b.staff_id

2、三表左连接：保证左表的所有记录都会出现，员工钱七出现，右表无匹配时显示为 NULL 。

SELECT a.name as staff_name, a.sex, b.name as goods_name, b.type as good_type, c.count
FROM tb_staff a
LEFT JOIN tb_goods b ON a.id = b.staff_id
LEFT JOIN tb_count c ON b.id = c.goods_id

---

4、RIGHT JOIN（右连接）

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

1、两表右连接：保证右表的所有记录都会出现，左表无匹配时显示为 NULL 。

SELECT a.name as staff_name, a.sex, b.name as goods_name, b.type as good_type
FROM tb_staff a
RIGHT JOIN tb_goods b ON a.id = b.staff_id

2、三表右连接：保证右表的所有记录都会出现，左表无匹配时显示为 NULL 。

SELECT a.name as staff_name, a.sex, b.name as goods_name, b.type as good_type, c.count
FROM tb_staff a
RIGHT JOIN tb_goods b ON a.id = b.staff_id
RIGHT JOIN tb_count c ON b.id = c.goods_id

---

5、混合使用

再多表查询中可能需要将多张表混合使用，这里不多举例。

业务：查找管理货品的员工和被管理的货的库存

SELECT a.name as staff_name, a.sex, b.name as goods_name, b.type as good_type, c.count as count
FROM tb_staff a
INNER JOIN tb_goods b ON a.id = b.staff_id
LEFT JOIN tb_count c ON b.id = c.goods_id

使用方法有很多，根据业务需求自行探索。

二、聚合操作

在MySQL中，聚合操作是指将多行数据合并计算为单个汇总值的操作。聚合通常用于统计和数据分析，比如计算总和、平均值、最大值等，聚合是SQL数据分析的核心功能，合理使用可以大幅提高数据查询和分析的效率。

用于聚合的主要关键字有以下两种。

• GROUP BY：指定分组的列
• 聚合函数：执行实际计算的关键函数

函数	描述	示例
COUNT()	计算行数	COUNT(*) 计算所有行数
SUM()	计算数值列的总和	SUM(salary) 计算工资总和
AVG()	计算数值列的平均值	AVG(price) 计算平均价格
MAX()	找出最大值	MAX(age) 找出最大年龄
MIN()	找出最小值	MIN(score) 找出最低分数
GROUP_CONCAT()	将多行值连接成字符串	GROUP_CONCAT(name) 合并所有名字

---

1、基础聚合示例

1、计算员工总数:

SELECT COUNT(*) as staff_count FROM tb_staff;

2、统计每个员工管理的货品数量和货品的库存数量：

SELECT a.name as staff_name, a.sex, COUNT(b.type) as goods_num, SUM(c.count)
FROM tb_staff a
INNER JOIN tb_goods b ON a.id = b.staff_id
LEFT JOIN tb_count c ON b.id = c.goods_id
GROUP BY a.name

---

2、高级聚合示例

1、HAVING子句：对聚合结果进行过滤，只统计库存大于2000的。

SELECT a.name as staff_name, a.sex, COUNT(b.type) as goods_num, SUM(c.count) as count
FROM tb_staff a
INNER JOIN tb_goods b ON a.id = b.staff_id
LEFT JOIN tb_count c ON b.id = c.goods_id
GROUP BY a.name
HAVING count > 2000;

2、WITH ROLLUP：生成小计和总计行。

SELECT a.name as staff_name, COUNT(b.type) as goods_num, SUM(c.count) as count
FROM tb_staff a
INNER JOIN tb_goods b ON a.id = b.staff_id
LEFT JOIN tb_count c ON b.id = c.goods_id
GROUP BY a.name
WITH ROLLUP

本文存在的不足和问题，欢迎各位指正。