MySQL数据库——多表查询

L_earning_

已于 2022-03-14 18:09:38 修改

阅读量1.3k

点赞数

分类专栏： MySQL 文章标签：数据库 mysql sql

于 2022-03-14 18:04:14 首次发布

本文链接：https://blog.youkuaiyun.com/L_earning_/article/details/123317938

版权

MySQL 专栏收录该内容

8 篇文章

订阅专栏

本文详细探讨了MySQL中的多表查询，包括等值连接与非等值连接、自连接与非自连接、内连接与外连接的区分，并讲解了在MySQL中如何实现满外连接，以及SQL99新特性如自然连接和using连接。同时，提出了多表查询的最佳实践，强调添加连接条件的重要性以及避免笛卡尔积错误。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

本文采用SQL99语法。所适用的相关例子及数据来源于尚硅谷。

写在前面

为什么需要建立多个数据表？

减少数据冗余
提高运行速度
减轻数据库维护工作量
提高工作效率，多个数据表可以同时被调用等

一、多表查询

1.1 多表查询的分类

1.1.1 等值连接 $v s$ 非等值连接

等值连接

# 查询员工的employee_id,last_name,department_name,city
SELECT emp.employee_id,emp.last_name,dep.department_name,loc.city
FROM employees emp 
JOIN departments dep ON emp.department_id = dep.department_id
JOIN locations loc ON dep.location_id = loc.location_id;

非等值连接

# 查询员工的姓名、工资、工资等级
SELECT e.last_name,e.salary,j.grade_level
FROM employees e 
JOIN job_grades j ON e.salary BETWEEN j.lowest_sal AND j.highest_sal;

1.1.2 非自连接 $v s$ 自连接

非自连接：连接两个不同的表，上述两个例子皆为非自连接
自连接：连接同一张表，对其中的字段反复利用

#查询员工id，员工姓名及其管理者的id和姓名
SELECT e.employee_id,e.last_name 员工姓名,m.employee_id 管理者id,m.last_name 管理者姓名
FROM employees e 
JOIN employees m ON e.manager_id = m.employee_id;

1.1.3 内连接 $v s$ 外连接

内连接：返回两个表中匹配的数据

# 查询员工的员工号,部门名
SELECT e.employee_id,d.department_name
FROM employees e,departments d
WHERE e.department_id = d.department_id;

外连接，返回两个表中匹配的数据，以及左表或右表中不匹配的行
- 又分为左外连接、右外连接以及满外连接
- mysql不支持 full outer join 方式的满外连接

1、左外连接

# 查询所有员工的last_name,department_name
SELECT last_name,department_name
FROM employees e LEFT OUTER JOIN departments d
ON  e.department_id = d.department_id;

2、右外连接

# 查询所有部门
SELECT last_name,department_name
FROM employees e 
RIGHT OUTER JOIN departments d ON  e.department_id = d.department_id;

3、满外连接（ Oracle支持）

SELECT last_name,department_name
FROM employees e 
FULL OUTER JOIN departments d ON  e.department_id = d.department_id;

如何在mysql中实现满外连接？使用union操作符

union 操作符，返回两个查询结果集的并集，会进行去重处理
union all 操作符，返回两个查询结果集的并集。对于重复部分，不去重
能同时用 union 和 union all 时，首选 union all 效率较高

SELECT employee_id, department_name
FROM employees e 
LEFT JOIN departments d ON e.department_id = d.department_id
UNION ALL
SELECT employee_id, department_name
FROM employees e 
RIGHT JOIN departments d ON e.department_id = d.department_id
WHERE e.department_id IS NULL;

4、左外连接基础上，去除并集

SELECT employee_id, department_name
FROM employees e 
LEFT JOIN departments d ON e.department_id = d.department_id
WHERE d.department_id IS NULL;

5、右外连接基础上，去除并集

SELECT employee_id, department_name
FROM employees e 
RIGHT JOIN departments d ON e.department_id = d.department_id
WHERE e.department_id IS NULL;

6、取两个集合分别去除交集的部分的并集

SELECT employee_id, department_name
FROM employees e 
LEFT JOIN departments d ON e.department_id = d.department_id
WHERE d.department_id IS NULL
UNION ALL
SELECT employee_id, department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.department_id
WHERE e.department_id IS NULL;

sql99语法的新特性

8、自然连接 natural join

# 自动查询两张连接表中所有相同的字段，然后进行等值连接
SELECT employee_id, department_name
FROM employees e 
JOIN departments d ON e.department_id = d.department_id
AND e.manager_id = d.manager_id;
# 上下两种效果一致
SELECT employee_id, department_name
FROM employees e NATURAL JOIN departments d;

9、using 连接（不适用于自连接）

# 在using括号中填入同名的字段，简化等值连接
SELECT employee_id, department_name
FROM employees e 
JOIN departments d ON e.department_id = d.department_id;
# 上下两种效果一致
SELECT employee_id, department_name
FROM employees e 
JOIN departments d USING (department_id);

优化：超过三个表禁止用join
注意事项：
- 多表查询的正确方式：添加连接条件。若为添加连接条件，会出现笛卡尔积错误
- 如果查询语句中出现了多个表中都存在的字段，则必须指名此字段所在的表
- 从sql优化的角度，建议多表查询时，每个字段前都指明其所在的表
- 如果给表起了别名，在select和where语句中，必须使用表的别名，而不能再使用表的原名
- 如果有n个表，则需要至少n-1个连接条件