mysql多表查询如何做？从笛卡尔积到自连接全攻略

作者：袖梨 2026-08-23

mysql多表查询如何做？从笛卡尔积到自连接全攻略需要先看清适用场景和关键步骤，避免只记结论却忽略实际限制。

1. 基本查询回顾

1.1 复杂条件查询

-- 查询工资高于500或岗位为MANAGER的雇员，同时满足姓名首字母为大写JSELECT * FROM EMP WHERE (sal > 500 OR job = 'MANAGER') AND ename LIKE 'J%';

1.2 排序查询

-- 按照部门号升序而雇员的工资降序排序SELECT * FROM EMP ORDER BY deptno, sal DESC;-- 使用年薪进行降序排序SELECT ename, sal * 12 + ifnull(comm, 0) AS '年薪' FROM EMP ORDER BY 年薪 DESC;

1.3 子查询应用

-- 显示工资最高的员工的名字和工作岗位SELECT ename, job FROM EMP WHERE sal = (SELECT MAX(sal) FROM EMP);-- 显示工资高于平均工资的员工信息SELECT ename, sal FROM EMP WHERE sal > (SELECT AVG(sal) FROM EMP);

1.4 分组统计

-- 显示每个部门的平均工资和最高工资SELECT deptno, FORMAT(AVG(sal), 2), MAX(sal) FROM EMP GROUP BY deptno;-- 显示平均工资低于2000的部门号和它的平均工资SELECT deptno, AVG(sal) AS avg_sal FROM EMP GROUP BY deptno HAVING avg_sal < 2000;-- 显示每种岗位的雇员总数，平均工资SELECT job, COUNT(*), FORMAT(AVG(sal), 2) FROM EMP GROUP BY job;

2. 多表查询（重点）

2.1 多表查询的基本概念

实际开发中数据往往来自不同的表，需要进行多表查询。

mysql多表查询怎么做？从笛卡尔积到自连接全攻略

我们使用公司管理系统中的三张表演示：

  1. EMP表：员工信息

  2. DEPT表：部门信息

  3. SALGRADE表：工资等级

2.2 笛卡尔积与连接条件

-- 错误的查询：会产生笛卡尔积（14×4=56条记录）SELECT * FROM EMP, DEPT;-- 正确的多表查询：添加连接条件SELECT EMP.ename, EMP.sal, DEPT.dname FROM EMP, DEPT WHERE EMP.deptno = DEPT.deptno;

2.3 多表查询示例

-- 显示部门号为10的部门名，员工名和工资SELECT ename, sal, dname FROM EMP, DEPT WHERE EMP.deptno = DEPT.deptno AND DEPT.deptno = 10;-- 显示各个员工的姓名，工资，及工资级别SELECT ename, sal, grade FROM EMP, SALGRADE WHERE EMP.sal BETWEEN losal AND hisal;

3. 自连接查询

3.1 自连接概念

自连接是指在同一张表上进行连接查询，通常用于处理层次关系数据。

3.2 自连接示例

案例：显示员工FORD的上级领导的编号和姓名

方法1：使用子查询

SELECT empno, ename FROM emp WHERE emp.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. 给同一张表起不同的别名（如leader、worker）

  2. 通过别名区分不同角色的数据

  3. 性能通常优于子查询

4. 子查询（嵌套查询）

4.1 单行子查询

返回一行记录的子查询

-- 显示SMITH同一部门的员工SELECT * FROM EMP WHERE deptno = (SELECT deptno FROM EMP WHERE ename = 'SMITH');

4.2 多行子查询

返回多行记录的子查询，需要配合特定关键字使用

4.2.1 IN关键字

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

4.2.2 ALL关键字

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

4.2.3 ANY关键字

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

关键字区别：

  1. IN：等于子查询结果中的任意一个

  2. ALL：比子查询结果中的所有值都...

  3. ANY：比子查询结果中的任意一个值都...

4.3 多列子查询

查询返回多个列数据的子查询

-- 查询和SMITH的部门和岗位完全相同的所有雇员，不含SMITH本人SELECT ename FROM EMP WHERE (deptno, job) = (SELECT deptno, job FROM EMP WHERE ename = 'SMITH') AND ename <> 'SMITH';

4.4 在FROM子句中使用子查询

将子查询结果作为临时表使用

案例1：显示每个高于自己部门平均工资的员工

SELECT ename, deptno, sal, FORMAT(asal, 2) FROM EMP, (    SELECT AVG(sal) asal, deptno dt     FROM EMP     GROUP BY deptno) tmp WHERE EMP.sal > tmp.asal AND EMP.deptno = tmp.dt;

案例2：查找每个部门工资最高的人

SELECT EMP.ename, EMP.sal, EMP.deptno, ms FROM EMP, (    SELECT MAX(sal) ms, deptno     FROM EMP     GROUP BY deptno) tmp WHERE EMP.deptno = tmp.deptno AND EMP.sal = tmp.ms;

案例3：显示每个部门的信息和人员数量

方法1：使用多表连接

SELECT DEPT.dname, DEPT.deptno, DEPT.loc, COUNT(*) AS '部门人数' FROM EMP, DEPT WHERE EMP.deptno = DEPT.deptno GROUP BY DEPT.deptno, DEPT.dname, DEPT.loc;

方法2：使用子查询（推荐）

SELECT DEPT.deptno, dname, mycnt, loc FROM DEPT, (    SELECT COUNT(*) mycnt, deptno     FROM EMP     GROUP BY deptno) tmp WHERE DEPT.deptno = tmp.deptno;

5. 合并查询

5.1 UNION操作符

取得两个结果集的并集，自动去掉重复行

-- 将工资大于2500或职位是MANAGER的人找出来SELECT ename, sal, job FROM EMP WHERE sal > 2500UNIONSELECT ename, sal, job FROM EMP WHERE job = 'MANAGER';

5.2 UNION ALL操作符

取得两个结果集的并集，不会去掉重复行

-- 将工资大于2500或职位是MANAGER的人找出来（包含重复记录）SELECT ename, sal, job FROM EMP WHERE sal > 2500UNION ALLSELECT ename, sal, job FROM EMP WHERE job = 'MANAGER';

5.3 UNION vs UNION ALL

特性

UNION

UNION ALL

去重

自动去掉重复行

保留所有行

性能

较慢（需要去重）

较快

排序

结果集自动排序

不保证顺序

使用场景

需要唯一结果时

需要完整结果时

6. 实战技巧与性能优化

6.1 查询执行顺序理解

-- 理解SQL执行顺序SELECT deptno, AVG(sal) as avg_sal        -- 5. 选择字段FROM EMP                                 -- 1. 数据源WHERE sal > 1000                         -- 2. 条件过滤GROUP BY deptno                          -- 3. 分组HAVING avg_sal > 2000                    -- 4. 分组后过滤ORDER BY avg_sal DESC;                  -- 6. 排序

6.2 性能优化建议

  1. 连接条件优先：多表查询时先写连接条件，再写过滤条件

  2. 合理使用索引：连接字段和常用查询字段建立索引

  3. 避免SELECT*：只选择需要的字段

  4. 子查询优化：能用连接查询尽量不用子查询

  5. 分页查询：大数据量时使用LIMIT分页

6.3 复杂查询调试技巧

-- 分步调试复杂查询-- 步骤1：先验证子查询结果SELECT deptno FROM EMP WHERE ename = 'SMITH';-- 步骤2：再验证主查询SELECT * FROM EMP WHERE deptno = 20;-- 步骤3：组合成完整查询SELECT * FROM EMP WHERE deptno = (SELECT deptno FROM EMP WHERE ename = 'SMITH');

7. 实战OJ题目示例

7.1 牛客网典型题目

-- 查找所有员工入职时候的薪水情况SELECT e.emp_no, s.salary FROM employees e, salaries s WHERE e.emp_no = s.emp_no AND e.hire_date = s.from_date ORDER BY e.emp_no DESC;-- 获取所有非manager的员工emp_noSELECT emp_no FROM employees WHERE emp_no NOT IN (    SELECT emp_no FROM dept_manager);-- 获取所有员工当前的managerSELECT e.emp_no, m.emp_no as manager_no FROM dept_emp e, dept_manager m WHERE e.dept_no = m.dept_no AND e.to_date = '9999-01-01' AND m.to_date = '9999-01-01';

8. 总结

8.1 查询类型选择指南

场景

推荐查询方式

理由

简单单表查询

基本SELECT

性能最好

多表关联查询

多表连接

直观易懂

层次关系查询

自连接

性能优于子查询

存在性检查

EXISTS子查询

效率高

结果集合并

UNION/UNION ALL

根据去重需求选择

8.2 最佳实践

  1. 明确需求：先分析需要什么数据，来自哪些表

  2. 选择最优方案：根据数据量和关系选择查询方式

  3. 分步验证：复杂查询先验证各部分结果

  4. 性能测试：大数据量时测试查询性能

  5. 代码可读性：合理使用别名和格式化

掌握复合查询是MySQL数据库开发的核心技能，通过大量实践可以熟练运用各种查询技巧，编写出高效、可维护的SQL语句。

9. 总结

相关文章

精彩推荐