MySQL基础篇——第06章 多表查询
多表查询,也称为关联查询,指两个或更多个表一起完成查询操作
前提条件:这些一起查询的表之间是有关系的(一对一、一对多),它们之间一定是有关联字段,这个关联字段可能建立了外键,也可能没有建立外键。比如:员工表和部门表,这两个表依靠“部门编号”进行关联
1. 一个案例引发的多表连接
1.1 案例说明
案例:查询员工的姓名 last_name
及其部门名称 department_name
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-qzUCdJYL-1652660282985)(MySQL基础篇——第06章 多表查询.assets/image-20220420030155334.png)]
- 错误写法:
SELECT last_name, department_name
FROM employees, departments;
# 2889 rows in set (0.01 sec)
- 分析错误原因:
SELECT COUNT(employee_id) FROM employees; # 107条记录
SELECT COUNT(department_id) FROM departments; # 27条记录
SELECT 107*27 FROM dual; # 2889
- 即每个员工都与每个部门匹配了一遍。我们把上述多表查询中出现的问题称为:笛卡尔积的错误
1.2 笛卡尔积(或交叉连接)的理解
笛卡尔乘积是一个数学运算。假设我有两个集合 X 和 Y,那么 X 和 Y 的笛卡尔积就是 X 和 Y 的所有可能组合,也就是第一个对象来自于 X,第二个对象来自于 Y 的所有可能。组合的个数即为两个集合中元素个数的乘积数
SQL92中,笛卡尔积也称为 交叉连接
,英文是 CROSS JOIN 。在 SQL99 中也是使用 CROSS JOIN
表示交叉连接。它的作用就是可以把任意表进行连接,即使这两张表不相关
SELECT last_name,department_name FROM employees, departments;
SELECT last_name,department_name FROM employees CROSS JOIN departments;
SELECT last_name,department_name FROM employees INNER JOIN departments;
SELECT last_name,department_name FROM employees JOIN departments;
笛卡尔积的错误会在下面条件下产生:
- 省略多个表的连接条件(或关联条件)
- 连接条件(或关联条件)无效
- 所有表中的所有行互相连接
1.3 多表查询(关联查询)的语法
为了避免笛卡尔积, 可以在 WHERE
子句加入有效的关联条件,多表查询(关联查询)的语法为:
SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column1 = table2.column2; # 关联条件
2. 多表查询分类讲解
2.1 关联条件:等值连接 vs 非等值连接
2.1.1 等值连接
-
如果查询语句中出现了多个表中都存在的列(字段),则必须在列(字段)名之前加上表名前缀,指明此字段所在的表
-
从sql优化的角度,对于数据库中表记录的查询和变更,只要涉及多个表,都需要在每个字段前加上表名前缀进行限定
-
可以在
FROM
子句中给表起别名,在SELECT
和WHERE
子句中使用表的别名,从而简化查询;一旦在在
FROM
子句中声明了表的别名,则在SELECT
和WHERE
子句中必须使用表的别名,而不能再使用表的原名 -
列的别名可以使用
""
括起来,但表的别名不能使用""
SELECT emp.employee_id, dept.department_name, emp.department_id
FROM employees AS emp, departments AS dept
WHERE emp.department_id = dept.department_id;
- 如果需要对
n
个表进行多表查询,则在WHERE
子句中,至少需要声明n - 1
个关联条件,且多个关联条件之间用AND
连接
练习:查询出公司员工的employee_id,last_name,department_name,city
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ZI0lSD9t-1652660282986)(MySQL基础篇——第06章 多表查询.assets/image-20220420160904805.png)]
SELECT e.employee_id, e.last_name, d.department_name, l.city
FROM employees e, departments d, locations l
WHERE e.department_id = d.department_id AND d.location_id = l.location_id;
2.1.2 非等值连接
练习:查询出公司员工的last_name,salary, grade_level
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-VgfmH1bg-1652660282986)(MySQL基础篇——第06章 多表查询.assets/image-20220420160824473.png)]
SELECT e.last_name, e.salary, j.grade_level
FROM employees e, job_grades j
WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal;
# WHERE e.salary >= j.lowest_sal AND e.salary <= j.highest_sal;
2.2 自我引用:自连接 vs 非自连接
2.2.1 自连接(自我引用)
练习:查询员工id(employee_id),员工姓名(last_name),管理者id(manager_id)和管理者的姓名
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6QOegFAv-1652660282986)(MySQL基础篇——第06章 多表查询.assets/image-20220420162202326.png)]
worker
和 manager
本质上是同一张表 employees
,只是用取别名的方式虚拟成两张表以代表不同的意义
SELECT worker.employee_id, worker.last_name, worker.manager_id, manager.last_name manager_name
FROM employees worker, employees manager
WHERE worker.manager_id = manager.employee_id;
2.3 内连接 vs 外连接
除了查询满足条件的记录以外,外连接还可以查询某一方不满足条件的记录
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-BOBiUzuF-1652660282987)(MySQL基础篇——第06章 多表查询.assets/image-20220420211058047.png)]
- **内连接:**合并具有同一列(字段)的两个以上的表的行,结果集中不包含一个表与另一个表不匹配的行
- 上面举的例子全是内连接,结果集不包含两张表不匹配的行
- 如在
employees
表和departments
表中,有的员工没有department_id
(department_id=NULL
),也有的部门没有员工
- 外连接:两个表在连接过程中除了返回满足关联条件的行以外还返回左(右)表中不满足条件的行,这种连接被称为左(右)外连接;如果同时返回了左表和右表中不满足条件的行,则被称为满外连接。没有匹配的行时,结果表中相应的列值为NULL
- 如果是左外连接,则连接条件中左边的表被称为
主表
,右边的表称为从表
如果是右外连接,则连接条件中右边的表被称为主表
,左边的表称为从表
2.3.1 SQL92 语法实现外连接(MySQL不支持)
在 SQL92 中采用 (+)
代表 从表
所在的位置。即左或右外连接中,(+)
表示哪个是 从表
Oracle 对 SQL92 支持较好,而 MySQL 则不支持 SQL92 的外连接
而且在 SQL92 中,只有左外连接和右外连接,没有满外连接
# 左外连接:返回左表中不满足条件的行
SELECT employee_id, department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id(+);
# 右外连接:返回右表中不满足条件的行
SELECT employee_id, department_name
FROM employees e, departments d
WHERE e.department_id(+) = d.department_id;
3. SQL99 语法实现多表查询
3.1 基本语法
- 使用
JOIN ... ON
子句创建连接的语法结构:
SELECT table1.column1, table2.column2, table3.column3
FROM table1
JOIN table2 ON table1和table2的连接条件
JOIN table3 ON table2和table3的连接条件
- 语法说明:
- 可以使用
ON
子句指定额外的连接条件 - 这个连接条件是与其它条件分开的
ON
子句使语句具有更高的易读性- 关键字
JOIN
、INNER JOIN
、CROSS JOIN
的含义是一样的,都表示内连接
- 可以使用
3.2 内连接:JOIN
/ INNER JOIN
- 语法:其中
JOIN
关键字也可以写成INNER JOIN
SELECT 字段列表
FROM A表 INNER JOIN B表
ON 关联条件
WHERE 等其他子句;
- 练习1:查询出公司员工的last_name,department_name(两张表关联)
SELECT e.last_name, d.department_name
FROM employees e JOIN departments d
ON e.department_id = d.department_id;
- 练习2:查询出公司员工的last_name,department_name,city(三张表关联)
SELECT e.last_name, d.department_name, l.city
FROM employees e JOIN departments d
ON e.department_id = d.department_id
JOIN locations l
ON d.location_id = l.location_id;
3.3 外连接(OUTER JOIN)
3.3.1 左外连接:LEFT JOIN
- 语法:其中
LEFT JOIN
关键字也可以写成LEFT OUTER JOIN
# 左外连接:返回左表(LEFT JOIN左边的表)中不满足ON子句关联条件的行
SELECT 字段列表
FROM A表 LEFT JOIN B表
ON 关联条件
WHERE 等其他子句;
- 练习:查询公司员工的last_name,department_name,并返回employees表中不属于任一部门(
department_id=NULL
)的员工
SELECT e.last_name, d.department_name
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id;
3.3.2 右外连接:RIGHT JOIN
- 语法:其中
RIGHT JOIN
关键字也可以写成RIGHT OUTER JOIN
# 右外连接:返回右表(RIGHT JOIN右边的表)中不满足ON子句关联条件的行
SELECT 字段列表
FROM A表 RIGHT JOIN B表
ON 关联条件
WHERE 等其他子句;
- 练习:查询公司员工的last_name,department_name,并返回departments表中没有员工的部门(
departments
表中的department_id
没有出现在employees
表的任何记录中)
SELECT e.last_name, d.department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id;
3.3.3 满外连接(FULL OUTER JOIN)
- 满外连接的结果 = 左右表匹配的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据
- SQL99是支持满外连接的。使用
FULL JOIN
或FULL OUTER JOIN
来实现。 - 需要注意的是,MySQL不支持
FULL JOIN
,但是可以使用UNION
关键词来实现
4. UNION
的使用
合并查询结果:利用 UNION
关键字,可以将多条 SELECT
语句的结果组合成单个结果集。合并时,两个表对应的列数和数据类型必须相同,并且相互对应。各个 SELECT
语句之间使用 UNION
或 UNION ALL
关键字分隔
- 语法:
SELECT 字段列表
FROM A表
...
UNION [ALL]
SELECT 字段列表
FROM B表
...
UNION
操作符:返回两个查询的结果集的并集,去除重复记录
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-CxeJNb8n-1652660282988)(MySQL基础篇——第06章 多表查询.assets/image-20220420232001649.png)]
UNION ALL
操作符:返回两个查询的结果集的并集,不去除重复记录
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-XcDtSggq-1652660282988)(MySQL基础篇——第06章 多表查询.assets/image-20220420232152361.png)]
注意:执行
UNION ALL
语句时所需要的资源比UNION
语句少。如果明确知道合并数据后的结果数据不存在重复数据,或者不需要去除重复的数据,则尽量使用UNION ALL
语句,以提高数据查询的效率
5. SQL99 的 7 种 JOIN
连接的实现
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-eTs3qmy4-1652660282988)(MySQL基础篇——第06章 多表查询.assets/image-20220420232550182.png)]
- 中图:内连接
A ∩ B
- 语法:
SELECT 字段列表
FROM 表A JOIN 表B
ON 关联条件;
- 举例:
SELECT employee_id, department_name
FROM employees e JOIN departments d
ON e.department_id = d.department_id;
- 左上图:左外连接
A
- 语法:
SELECT 字段列表
FROM 表A(主表) LEFT JOIN 表B(从表)
ON 关联条件;
- 举例:
SELECT employee_id, department_name
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id;
- 右上图:右外连接
B
- 语法:
SELECT 字段列表
FROM 表A(从表) RIGHT JOIN 表B(主表)
ON 关联条件;
- 举例:
SELECT employee_id, department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id;
- 左中图:
A - A ∩ B
—— 表A中不满足关联条件的记录(行)
-
两张表按照指定关联字段key进行外连接,主表中不满足关联条件的行在从表中的关联字段的值一定是NULL(系统自动填充)
-
语法:
SELECT 字段列表
FROM 表A(主表) LEFT JOIN 表B(从表)
ON 关联条件
WHERE 从表关联字段 IS NULL;
- 举例:
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;
- 右中图:
B - A ∩ B
—— 表B中不满足关联条件的记录(行)
-
两张表按照指定关联字段key进行外连接,主表中不满足关联条件的行在从表中的关联字段的值一定是NULL(系统自动填充)
-
语法:
SELECT 字段列表
FROM 表A(从表) RIGHT JOIN 表B(主表)
ON 关联条件
WHERE 从表关联字段 IS NULL;
- 举例:
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;
- 左下图:满外连接
A ∪ B
—— 两张表中所有记录(行),无论是否满足关联条件,且不重复
- 语法:
# 方式一:左上图 UNION ALL 右中图
SELECT 字段列表
FROM 表A(主表) LEFT JOIN 表B(从表)
ON 关联条件
UNION ALL
SELECT 字段列表
FROM 表A(从表) RIGHT JOIN 表B(主表)
ON 关联条件
WHERE 从表关联字段 IS NULL;
# 方式二:左中图 UNION ALL 右上图
SELECT 字段列表
FROM 表A(主表) LEFT JOIN 表B(从表)
ON 关联条件
WHERE 从表关联字段 IS NULL
UNION ALL
SELECT 字段列表
FROM 表A(从表) RIGHT JOIN 表B(主表)
ON 关联条件;
- 举例:
# 方式一:左上图 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;
# 方式二:左中图 UNION ALL 右上图
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;
- 右下图:左中图
UNION ALL
右中图 —— 两张表中所有不满足关联条件的记录(行)
- 语法:
SELECT 字段列表
FROM 表A(主表) LEFT JOIN 表B(从表)
ON 关联条件
WHERE 从表关联字段 IS NULL
UNION ALL
SELECT 字段列表
FROM 表A(从表) RIGHT JOIN 表B(主表)
ON 关联条件
WHERE 从表关联字段 IS NULL;
- 举例:
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;
6. SQL99 语法新特性
6.1 自然连接 NATURE JOIN
SQL99 在 SQL92 的基础上提供了一些特殊语法,比如 NATURAL JOIN
用来表示自然连接。我们可以把自然连接理解为 SQL92 中的等值连接。它会帮你自动查询两张连接表中所有相同的字段 ,然后进行等值连接
在SQL92标准中,如果两张表有两个字段是关联的:
SELECT employee_id, last_name, department_name
FROM employees e JOIN departments d
ON e.department_id = d.department_id
AND e.manager_id` = d.manager_id;
在 SQL99 中你可以写成:
SELECT employee_id, last_name, department_name
FROM employees e NATURAL JOIN departments d;
6.2 JOIN ... USING ()
连接
当我们进行连接的时候,SQL99还支持使用 USING
指定数据表里的 同名字段
进行等值连接。但是只能配合 JOIN
一起使用。
比如:
SELECT employee_id, last_name, department_name
FROM employees e JOIN departments d
ON e.department_id = d.department_id;
等价于:
SELECT employee_id, last_name, department_name
FROM employees e JOIN departments d
USING (department_id);
- 与自然连接
NATURAL JOIN
不同的是,USING ()
指定了具体的相同的字段名称 JOIN ... USING ()
可以简化JOIN ON
的等值连接USING
只能配合JOIN
一起使用USING
要求两个关联字段在关联表中名称一致,因此不适用于自连接(自我引用);而且USING
只能表示关联字段值相等,不适用于非等值连接
7. 章节小结
表连接的约束条件可以有三种方式:WHERE
, ON
, USING
WHERE
:适用于所有关联查询ON
:只能和JOIN
一起使用,只能写关联条件。虽然关联条件可以并到WHERE
中和其他条件一起写,但分开写可读性更好USING
:只能和JOIN
一起使用,而且要求两个关联字段在关联表中名称一致,而且只能表示关联字段值相等
8. 课后练习
# 1.显示所有员工的姓名,部门号和部门名称。
SELECT e.`last_name`, e.`department_id`, d.`department_name`
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`;
# 2.查询90号部门员工的job_id和90号部门的location_id
SELECT e.`job_id`, d.`location_id`
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.department_id = 90;
# 3.选择所有有奖金的员工的 last_name , department_name , location_id , city
SELECT e.`last_name`, d.`department_name`, d.`location_id`, l.`city`
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
LEFT JOIN locations l
ON d.`location_id` = l.`location_id`
WHERE e.`commission_pct` IS NOT NULL;
# 4.选择city在Toronto工作的员工的 last_name , job_id , department_id , department_name
# 写法一:SQL99
SELECT e.`last_name`, e.`job_id`, e.`department_id`, d.`department_name`
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`
JOIN locations l
ON d.`location_id` = l.`location_id`
WHERE l.`city` = 'Toronto';
# 写法二:SQL92
SELECT e.`last_name`, e.`job_id`, e.`department_id`, d.`department_name`
FROM employees e, departments d, locations l
WHERE e.`department_id` = d.`department_id`
AND d.`location_id` = l.`location_id`
AND l.`city` = 'Toronto';
# 5.查询员工所在的部门名称、部门地址、姓名、工作、工资,其中员工所在部门的部门名称为’Executive’
SELECT d.`department_name`, l.`street_address`, e.`last_name`, j.`job_title`, e.`salary`
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`
JOIN locations l
ON d.`location_id` = l.`location_id`
JOIN jobs j
ON e.`job_id` = j.`job_id`
WHERE d.`department_name` = 'Executive';
# 6.选择指定员工的姓名,员工号,以及他的管理者的姓名和员工号,结果类似于下面的格式
# employees Emp# manager Mgr#
# kochhar 101 king 100
SELECT e.`last_name` "employees", e.`employee_id` "Emp#", m.`last_name` "manager", m.`employee_id` "Mgr#"
FROM employees e LEFT JOIN employees m
ON e.`manager_id` = m.`employee_id`;
# 7.查询哪些部门没有员工
SELECT d.`department_id`
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL; # 从表的关联字段 IS NULL
# 8. 查询哪个城市没有部门
SELECT l.`city`
FROM locations l LEFT JOIN departments d
ON l.`location_id` = d.`location_id`
WHERE d.`location_id` IS NULL;
# 9. 查询部门名为 Sales 或 IT 的员工信息
SELECT e.`employee_id`, e.`last_name`, e.`department_id`
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_name` IN ('Sales', 'IT');
LEFT JOIN employees m
ON e.manager_id
= m.employee_id
;
7.查询哪些部门没有员工
SELECT d.department_id
FROM employees e RIGHT JOIN departments d
ON e.department_id
= d.department_id
WHERE e.department_id
IS NULL; # 从表的关联字段 IS NULL
8. 查询哪个城市没有部门
SELECT l.city
FROM locations l LEFT JOIN departments d
ON l.location_id
= d.location_id
WHERE d.location_id
IS NULL;
9. 查询部门名为 Sales 或 IT 的员工信息
SELECT e.employee_id
, e.last_name
, e.department_id
FROM employees e JOIN departments d
ON e.department_id
= d.department_id
WHERE d.department_name
IN (‘Sales’, ‘IT’);