MySQL复合查询与内外连接

从单表查询到多表联查，一文掌握MySQL复杂查询的核心技术

在数据库开发中，单表查询往往无法满足实际业务需求。当数据分布在多张表中时，我们需要使用复合查询、多表连接等高级查询技术。本文将系统讲解MySQL中的复合查询和内外连接，帮助你从入门到精通。

---

📋 目录

1. 数据准备：EMP/DEPT/SALGRADE 三张表
2. 基本查询回顾
3. 多表查询基础
4. 自连接
5. 子查询详解
6. 合并查询
7. 内外连接
8. 总结与常见易错点

---

1. 数据准备：EMP/DEPT/SALGRADE 三张表

本文所有案例基于 Oracle 经典测试模式 SCOTT 中的三张表：EMP（员工表）、DEPT（部门表）和 SALGRADE（工资等级表）。请先在你的 MySQL 中执行以下 SQL 创建测试环境。

1.1 部门表 (DEPT)

-- 创建部门表
CREATE TABLE DEPT (
    DEPTNO INT(2) PRIMARY KEY,
    DNAME VARCHAR(14),
    LOC VARCHAR(13)
);

-- 插入数据
INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO DEPT VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON');

数据预览：

DEPTNO	DNAME	LOC
10	ACCOUNTING	NEW YORK
20	RESEARCH	DALLAS
30	SALES	CHICAGO
40	OPERATIONS	BOSTON

1.2 工资等级表 (SALGRADE)

-- 创建工资等级表
CREATE TABLE SALGRADE (
    GRADE INT,
    LOSAL INT,
    HISAL INT
);

-- 插入数据
INSERT INTO SALGRADE VALUES (1, 700, 1200);
INSERT INTO SALGRADE VALUES (2, 1201, 1400);
INSERT INTO SALGRADE VALUES (3, 1401, 2000);
INSERT INTO SALGRADE VALUES (4, 2001, 3000);
INSERT INTO SALGRADE VALUES (5, 3001, 9999);

数据预览：

GRADE	LOSAL	HISAL
1	700	1200
2	1201	1400
3	1401	2000
4	2001	3000
5	3001	9999

1.3 员工表 (EMP)

-- 创建员工表
CREATE TABLE EMP (
    EMPNO INT(4) PRIMARY KEY,
    ENAME VARCHAR(10),
    JOB VARCHAR(9),
    MGR INT(4),
    HIREDATE DATE,
    SAL DECIMAL(7,2),
    COMM DECIMAL(7,2),
    DEPTNO INT(2)
);

-- 插入完整的14条经典数据
INSERT INTO EMP VALUES (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800.00, NULL, 20);
INSERT INTO EMP VALUES (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600.00, 300.00, 30);
INSERT INTO EMP VALUES (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250.00, 500.00, 30);
INSERT INTO EMP VALUES (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975.00, NULL, 20);
INSERT INTO EMP VALUES (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250.00, 1400.00, 30);
INSERT INTO EMP VALUES (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850.00, NULL, 30);
INSERT INTO EMP VALUES (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450.00, NULL, 10);
INSERT INTO EMP VALUES (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000.00, NULL, 20);
INSERT INTO EMP VALUES (7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000.00, NULL, 10);
INSERT INTO EMP VALUES (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500.00, 0.00, 30);
INSERT INTO EMP VALUES (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100.00, NULL, 20);
INSERT INTO EMP VALUES (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950.00, NULL, 30);
INSERT INTO EMP VALUES (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000.00, NULL, 20);
INSERT INTO EMP VALUES (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300.00, NULL, 10);

📊 员工表完整数据预览：

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7369	SMITH	CLERK	7902	1980-12-17	800.00	NULL	20
7499	ALLEN	SALESMAN	7698	1981-02-20	1600.00	300.00	30
7521	WARD	SALESMAN	7698	1981-02-22	1250.00	500.00	30
7566	JONES	MANAGER	7839	1981-04-02	2975.00	NULL	20
7654	MARTIN	SALESMAN	7698	1981-09-28	1250.00	1400.00	30
7698	BLAKE	MANAGER	7839	1981-05-01	2850.00	NULL	30
7782	CLARK	MANAGER	7839	1981-06-09	2450.00	NULL	10
7788	SCOTT	ANALYST	7566	1987-04-19	3000.00	NULL	20
7839	KING	PRESIDENT	NULL	1981-11-17	5000.00	NULL	10
7844	TURNER	SALESMAN	7698	1981-09-08	1500.00	0.00	30
7876	ADAMS	CLERK	7788	1987-05-23	1100.00	NULL	20
7900	JAMES	CLERK	7698	1981-12-03	950.00	NULL	30
7902	FORD	ANALYST	7566	1981-12-03	3000.00	NULL	20
7934	MILLER	CLERK	7782	1982-01-23	1300.00	NULL	10

⚠️ 数据易错点提醒（非常重要！）

1. 大小写敏感：MySQL默认字符串比较不区分大小写，但为了兼容其他数据库，建议一律使用全大写（如 'SMITH'）。
2. TURNER 的 COMM 是 0：TURNER 的奖金是 0（零），而不是 NULL。计算年薪时注意区分。
3. KING 没有上级：KING 的 MGR 字段是 NULL，自连接时会自动忽略。

---

2. 基本查询回顾

在深入学习复合查询之前，我们先回顾一些常用的单表查询技巧。

2.1 条件查询与排序

-- 查询工资高于500或岗位为MANAGER，且姓名首字母为J的员工
SELECT * FROM EMP 
WHERE (sal > 500 OR job = 'MANAGER') AND ename LIKE 'J%';

💡 代码解析：

• LIKE 'J%'：使用通配符 % 匹配以 J 开头的任意字符串。
• 运算符优先级：AND 优先级高于 OR，但使用括号可以明确优先级。

-- 按部门号升序、工资降序排序
SELECT * FROM EMP 
ORDER BY deptno ASC, sal DESC;

⚠️ 易错点：ORDER BY 中多个字段的排序方向要分别指定，ORDER BY deptno, sal DESC 表示 deptno 升序（默认），sal 降序。

2.2 使用表达式和别名

-- 计算年薪并降序排序
SELECT 
    ename, 
    sal * 12 + IFNULL(comm, 0) AS '年薪' 
FROM EMP 
ORDER BY 年薪 DESC;

💡 关键知识点：

• IFNULL(comm, 0)：如果 comm 为 NULL，则当作 0 处理。
• 别名可以在 ORDER BY 中使用，但不能在 WHERE 中使用（SQL 执行顺序决定的）。

2.3 聚合函数与分组统计

-- 显示每个部门的平均工资和最高工资
SELECT 
    deptno, 
    FORMAT(AVG(sal), 2) AS avg_sal,
    MAX(sal) AS max_sal
FROM EMP 
GROUP BY deptno;

-- 显示平均工资低于2000的部门
SELECT deptno, AVG(sal) AS avg_sal
FROM EMP 
GROUP BY deptno 
HAVING avg_sal < 2000;

📊 WHERE vs HAVING 对比：

关键字	过滤时机	能否使用聚合函数	适用场景
WHERE	分组前	❌ 不能	过滤原始记录
HAVING	分组后	✅ 可以	过滤分组结果

---

3. 多表查询基础

3.1 什么是笛卡儿积

当查询涉及多张表时，如果不加连接条件，就会产生笛卡儿积——左表的每条记录与右表的每条记录组合。

📐 图示说明：

3.2 正确的多表查询

实际开发中，我们通过连接条件来过滤笛卡儿积，只保留有意义的组合。

-- 显示雇员名、工资以及所在部门名称
SELECT 
    EMP.ename, 
    EMP.sal, 
    DEPT.dname
FROM EMP, DEPT
WHERE EMP.deptno = DEPT.deptno;

💡 最佳实践：

• 当多表有同名字段时，使用 表名.字段名 明确指定。
• 可以为表起别名简化书写：FROM EMP e, DEPT d WHERE e.deptno = d.deptno。

-- 显示部门号为10的部门名、员工名和工资
SELECT 
    e.ename, 
    e.sal, 
    d.dname
FROM EMP e, DEPT d
WHERE e.deptno = d.deptno AND d.deptno = 10;

-- 显示员工的姓名、工资及工资级别
SELECT 
    e.ename, 
    e.sal, 
    s.grade
FROM EMP e, SALGRADE s
WHERE e.sal BETWEEN s.losal AND s.hisal;

---

4. 自连接

自连接是指在同一张表上进行连接查询，通常用于查询表中的层级关系。

4.1 自连接的应用场景

员工表中，empno 是员工编号，mgr 是上级领导的编号。要查询某个员工的上级信息，就需要自连接。

-- 查询FORD的上级领导编号和姓名

-- 方法1：子查询
SELECT empno, ename 
FROM EMP 
WHERE empno = (SELECT mgr FROM EMP WHERE ename = 'FORD');

-- 方法2：自连接（推荐）
SELECT 
    leader.empno, 
    leader.ename
FROM EMP leader, EMP worker
WHERE leader.empno = worker.mgr 
  AND worker.ename = 'FORD';

🧠 执行逻辑解析：

1. 将 EMP 表分别命名为 leader（领导表）和 worker（员工表）。
2. 连接条件：leader.empno = worker.mgr（领导的编号 = 员工的上级编号）。
3. 筛选条件：worker.ename = 'FORD'。

📌 自连接要点：

• 必须使用表别名，否则表名会冲突。
• 自连接本质上是将一张表视为两张独立的表。
• 适用于树形结构数据（组织架构、分类层级等）。

---

5. 子查询详解

子查询（嵌套查询）是指在一个 SQL 语句中嵌入另一个 SELECT 语句。

5.1 单行子查询

返回一行一列的子查询，常与 =、>、< 等比较运算符配合使用。

-- 查询与SMITH同部门的员工
SELECT * FROM EMP 
WHERE deptno = (SELECT deptno FROM EMP WHERE ename = 'SMITH');

⚠️ 注意事项：如果子查询返回多行，使用 = 会报错，应使用 IN。

5.2 多行子查询

返回多行单列的子查询，与 IN、ALL、ANY 配合使用。

IN 关键字

-- 查询和10号部门岗位相同的员工（不含10号部门自己）
SELECT ename, job, sal, deptno
FROM emp
WHERE job IN (SELECT DISTINCT job FROM emp WHERE deptno = 10)
  AND deptno <> 10;

ALL 关键字

-- 查询工资比部门30所有员工都高的员工
SELECT ename, sal, deptno
FROM EMP
WHERE sal > ALL (SELECT sal FROM EMP WHERE deptno = 30);

ANY 关键字

-- 查询工资比部门30任意一个员工高的员工
SELECT ename, sal, deptno
FROM EMP
WHERE sal > ANY (SELECT sal FROM EMP WHERE deptno = 30);

📊 对比理解：

关键字	含义	等价转换
> ALL	大于所有值	大于最大值
> ANY	大于任意一个值	大于最小值
= ANY	等于任意一个值	等价于 IN

5.3 多列子查询

返回多行多列的子查询，与 = 配合使用时要求子查询返回单行多列。

-- 查询和SMITH部门、岗位完全相同的员工（不含SMITH）
SELECT ename 
FROM EMP 
WHERE (deptno, job) = (SELECT deptno, job FROM EMP WHERE ename = 'SMITH')
  AND ename <> 'SMITH';

💡 执行步骤：

1. 内层查询先执行，返回 SMITH 的 (deptno, job) = (20, 'CLERK')。
2. 外层查询等价于：WHERE (deptno, job) = (20, 'CLERK')。

5.4 FROM 子句中的子查询

将子查询结果作为临时表，在 FROM 子句中使用。

-- 查询每个部门中工资高于本部门平均工资的员工
SELECT 
    e.ename, 
    e.deptno, 
    e.sal, 
    FORMAT(tmp.avg_sal, 2) AS dept_avg
FROM EMP e,
     (SELECT deptno, AVG(sal) AS avg_sal FROM EMP GROUP BY deptno) tmp
WHERE e.deptno = tmp.deptno 
  AND e.sal > tmp.avg_sal;

🧠 子查询作为临时表的优势：

• 将复杂的聚合结果复用。
• 减少多次查询的开销。
• 提高代码的可读性。

-- 查找每个部门工资最高的人
SELECT 
    e.ename, 
    e.sal, 
    e.deptno,
    tmp.max_sal
FROM EMP e,
     (SELECT deptno, MAX(sal) AS max_sal FROM EMP GROUP BY deptno) tmp
WHERE e.deptno = tmp.deptno 
  AND e.sal = tmp.max_sal;

-- 显示每个部门的信息和人员数量
SELECT 
    d.deptno,
    d.dname,
    d.loc,
    tmp.cnt AS employee_count
FROM DEPT d,
     (SELECT deptno, COUNT(*) AS cnt FROM EMP GROUP BY deptno) tmp
WHERE d.deptno = tmp.deptno;

⚠️ 注意：如果部门没有员工，上面的查询不会显示该部门。如需显示所有部门，应使用外连接（见第 7 节）。

---

6. 合并查询

使用 UNION 和 UNION ALL 合并多个 SELECT 的结果集。

6.1 UNION

去重合并，自动去除重复行。

-- 查询工资大于2500或职位是MANAGER的员工
SELECT * FROM EMP WHERE sal > 2500
UNION
SELECT * FROM EMP WHERE job = 'MANAGER';

💡 执行逻辑：

1. 分别执行两个 SELECT。
2. 合并结果集。
3. 去除重复记录。

6.2 UNION ALL

不去重合并，保留所有记录（包括重复）。

-- 合并查询，保留重复项
SELECT * FROM EMP WHERE sal > 2500
UNION ALL
SELECT * FROM EMP WHERE job = 'MANAGER';

📊 UNION vs UNION ALL 对比：

特性	UNION	UNION ALL
去重	✅ 自动去重	❌ 保留重复
性能	较慢（需要排序去重）	较快（直接合并）
使用场景	需要避免重复数据	确认无重复或需要保留重复
语法要求	各 SELECT 列数和类型必须一致	各 SELECT 列数和类型必须一致

---

7. 内外连接

7.1 内连接（INNER JOIN）

内连接是最常用的连接，只返回两个表中匹配条件的记录。

语法：

SELECT 字段
FROM 表1 
INNER JOIN 表2 ON 连接条件
WHERE 其他条件;

💡 注意：INNER 可以省略，JOIN 默认就是内连接。

-- 查询SMITH的部门名称
-- 方式1：传统写法
SELECT e.ename, d.dname
FROM EMP e, DEPT d
WHERE e.deptno = d.deptno AND e.ename = 'SMITH';

-- 方式2：标准内连接
SELECT e.ename, d.dname
FROM EMP e
INNER JOIN DEPT d ON e.deptno = d.deptno
WHERE e.ename = 'SMITH';

📌 内连接特点：

• 只显示匹配的记录。
• 不匹配的记录不会出现在结果中。
• 连接条件写在 ON 子句中，过滤条件写在 WHERE 中（可读性更好）。

7.2 外连接

当需要显示所有左表或右表记录时，使用外连接。

7.2.1 左外连接（LEFT JOIN）

左侧表完全显示，右表没有匹配时用 NULL 填充。

📚 经典案例：学生与成绩表

-- 创建学生表和成绩表
CREATE TABLE stu (
    id INT, 
    name VARCHAR(30)
);
INSERT INTO stu VALUES (1, 'jack'), (2, 'tom'), (3, 'kitty'), (4, 'nono');

CREATE TABLE exam (
    id INT, 
    grade INT
);
INSERT INTO exam VALUES (1, 56), (2, 76), (11, 8);

-- 查询所有学生的成绩，没有成绩的学生也要显示
SELECT *
FROM stu
LEFT JOIN exam ON stu.id = exam.id;

📊 查询结果：

id	name	id	grade
1	jack	1	56
2	tom	2	76
3	kitty	NULL	NULL
4	nono	NULL	NULL

7.2.2 右外连接（RIGHT JOIN）

右侧表完全显示，左表没有匹配时用 NULL 填充。

-- 显示所有成绩，即使没有对应学生
SELECT *
FROM stu
RIGHT JOIN exam ON stu.id = exam.id;

📊 查询结果：

id	name	id	grade
1	jack	1	56
2	tom	2	76
NULL	NULL	11	8

💡 重要应用场景：

-- 列出所有部门及其员工信息（包括没有员工的部门）
SELECT d.dname, e.*
FROM dept d
LEFT JOIN emp e ON d.deptno = e.deptno;

7.3 内连接 vs 外连接 总结

连接类型	关键字	结果特点	使用场景
内连接	INNER JOIN	只显示匹配的记录	查询相关联的数据
左外连接	LEFT JOIN	左表全部显示	以左表为主，补充右表信息
右外连接	RIGHT JOIN	右表全部显示	以右表为主，补充左表信息

🎯 选择建议：

• 需要严格匹配 → 内连接。
• 需要显示主表全部数据 → 左/右外连接。
• 习惯上更多使用左外连接（因为可读性更好）。

---

8. 总结与常见易错点

8.1 核心知识点梳理

1. 查询执行顺序：FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT
2. 连接的本质：笛卡儿积 + 连接条件过滤。
3. 子查询使用时机：
   • 查询条件依赖于另一个查询结果。
   • 需要将聚合结果作为条件。
   • 需要构建临时表。
4. 外连接的应用场景：
   • 需要显示所有主表数据。
   • 关联数据可能不存在时。
   • 报表统计中经常使用。

8.2 常见易错点 ⚠️

易错点	正确做法
多表查询不加连接条件	始终使用 WHERE 或 ON 指定连接条件
自连接忘记使用别名	必须为同一张表设置不同别名
子查询返回多行用 =	使用 IN、ALL 或 ANY
在 WHERE 中使用别名	别名在 ORDER BY 中使用，WHERE 中不可用
LEFT JOIN 后使用 WHERE 过滤右表	过滤条件放在 ON 中保留左表全部数据

8.3 性能优化建议 🚀

1. 优先使用连接而非子查询：连接通常比子查询效率更高。
2. 使用 EXISTS 替代 IN：当子查询结果集较大时。
3. 注意索引：连接条件字段应建立索引。
4. 避免 SELECT *：只查询需要的字段。

---

📖 延伸阅读：建议在真实项目中多加练习，将理论知识与实际业务场景结合，才能真正掌握复合查询的精髓。

💪 练习建议：

1. 使用 LeetCode 数据库题库进行刷题训练。
2. 设计一个包含多表的业务场景，手动编写查询语句。
3. 对比同一查询的不同写法，分析执行计划。

---

希望本文能帮助你全面掌握 MySQL 复合查询与内外连接！如有疑问，欢迎留言交流。 📚