MySQL 联合查询详解：JOIN 类型与多表关联实战

基础回顾

聚合函数

1. COUNT(列名/常量/)：统计记录的条数，推荐使用 COUNT()。
2. SUM(列名)：求和，列必须是数值类型。
3. ROUND(数值，小数点的位数)：格式化小数输出格式。
4. AVG(列名)：求平均值，列必须是数值类型。
5. MAX(列名)：求最大值，列必须是数值类型。
6. MIN(列名)：求最小值，列必须是数值类型。

分组查询

语法：SELECT 要分组的列，聚合函数 (列名) FROM 表名 GROUP BY 要分组的列 HAVING 对分组的结果进行过滤

WHERE 子句与 HAVING 子句的区别

1. WHERE 子句：用于在分组前过滤数据行。
2. HAVING 子句：用于在分组后对分组结果进行筛选，特别适用于聚合函数的条件过滤。
3. HAVING 与 GROUP BY：通常与 GROUP BY 一起使用，便于对分组后的结果进行筛选。
4. WHERE 和 HAVING 的结合使用：先使用 WHERE 子句进行行级筛选，然后再使用 HAVING 进行分组后的筛选。

联合查询概述

使用联合查询的步骤

1. 明确查询需求：确定要从两个（或多个）表中查询哪些数据。明确哪些表包含所需信息，哪些字段之间有关系（笛卡尔积）。
2. 选择 JOIN 类型：根据需求选择合适的 JOIN 类型：
   • INNER JOIN（内连接）：只返回两个表中都有匹配的数据。
   • LEFT JOIN（左连接）：返回左表的所有数据，右表中没有匹配时显示 NULL。
   • RIGHT JOIN（右连接）：返回右表的所有数据，左表中没有匹配时显示 NULL。
   • FULL JOIN（全连接）：（MySQL 需用 UNION 实现）：返回两个表中的所有数据。
   • CROSS JOIN（交叉连接）：返回两个表的笛卡尔积，生成所有可能的组合。
3. 编写 JOIN 查询语句：编写 SQL 查询语句，指定表之间的连接条件（ON 条件），通常是表之间的主键和外键。
4. 添加过滤条件：使用 WHERE 子句进一步限制结果集。
5. 执行并验证查询结果：运行查询，检查返回的结果是否符合需求。

为什么要使用联合查询？

• 避免数据冗余：联合查询帮助从分散的表中合并数据，避免重复存储大量数据。
• 高效的数据管理：当数据分布在多个表中时，通过 JOIN 可以快速检索关联数据。
• 多表关系查询：大部分实际应用场景的数据是相互关联的，联合查询能够从关联表中提取相关数据。
• 简化查询逻辑：在单条 SQL 语句中获取多个表的关联数据，简化了数据提取和处理的逻辑。

连接类型详解

1. 笛卡尔积 (CROSS JOIN)

概念：将两个表中的每一条记录都与另一个表中的每一条记录组合，生成所有可能的组合。如果表 A 有 m 条记录，表 B 有 n 条记录，那么笛卡尔积的结果会包含 m * n 条记录。

示例：

INSERT INTO school VALUES (1,'李白'),(2,'白居易');
INSERT INTO subject VALUES (1,'Math'),(2,'English'),(3,'Science');

SELECT school.name, subject.subject01 
FROM school 
CROSS JOIN subject;

解释：每个 A 表的记录和 B 表的记录组合在一起，生成了 2 × 3 = 6 条记录。 作用：组合所有可能的情况。通常需避免笛卡尔积，因为会生成大量无用的组合数据，造成查询负担。

2. 内连接 (INNER JOIN)

内连接只返回在两个表中都有匹配关系的记录，未匹配的记录不会出现在结果中。

语法：

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

示例：查询每位选了课的学生的姓名和课程名称。

SELECT students.student_name, courses.course_name 
FROM students 
INNER JOIN courses ON students.student_id = courses.student_id;

特点：只返回匹配数据，不包含 NULL。常用于查询两表间有严格关系的数据。

3. 左连接 (LEFT JOIN)

左连接会返回左表中的所有记录，以及右表中与左表匹配的记录。如果右表中没有匹配的记录，对应的右表字段会显示为 NULL。

语法：

SELECT 字段列表 
FROM 左表 
LEFT JOIN 右表 ON 左表。列 = 右表。列;

示例：查询每个员工的姓名和其所在的部门名称。如果员工没有分配部门，那么部门名称应显示为 NULL。

SELECT employees.employee_name, departments.department_name 
FROM employees 
LEFT JOIN departments ON employees.department_id = departments.department_id;

适用场景：需要保留主表（左表）数据完整性的场景，比如显示所有员工及其部门信息，即使员工没有分配部门。

4. 右连接 (RIGHT JOIN)

右连接会返回右表中的所有记录，以及左表中与右表匹配的记录。如果左表中没有匹配的记录，左表的对应字段将显示为 NULL。

语法：

SELECT 字段列表 
FROM 左表 
RIGHT JOIN 右表 ON 左表。列 = 右表。列;

示例：查询所有部门及其员工，有时即使部门没有员工。

SELECT employees.employee_name, departments.department_name 
FROM employees 
RIGHT JOIN departments ON employees.department_id = departments.department_id;

区别：RIGHT JOIN 保留右表的所有记录，而 LEFT JOIN 保留左表的所有记录。

5. 全连接 (FULL JOIN)

FULL JOIN 返回左表和右表中的所有记录。如果一方的表与另一方的记录不匹配，没有匹配的部分就会 NULL 填充。

注意：MySQL 并不直接支持 FULL JOIN，可以通过 LEFT JOIN 和 RIGHT JOIN 的组合，使用 UNION 来模拟实现。

模拟代码：

-- 模拟 FULL JOIN
SELECT employees.employee_name, departments.department_name 
FROM employees 
LEFT JOIN departments ON employees.department_id = departments.department_id 
UNION 
SELECT employees.employee_name, departments.department_name 
FROM employees 
RIGHT JOIN departments ON employees.department_id = departments.department_id;

适用场景：查询两个表的所有记录，无论这些记录是否有匹配的项。例如查看所有员工和部门的信息，即使某些员工没有部门，或者某些部门没有员工。

6. 交叉连接 (CROSS JOIN) 补充

CROSS JOIN 也被称为笛卡尔积。它通常用于生成所有可能的组合，但在实际应用中很少使用，因为它会产生大量的无用数据，通常只在特定的需要生成所有组合的情况下使用。使用 CROSS JOIN 时要特别注意结果集的大小，避免由于数据量过大导致性能问题。