多表查询
案列说明
笛卡尔积的理解
|
1
2
3
4
5
|
select id,department_namefrom employees,departments;#错的select id,department_id,department_namefrom employees CROSS JOIN departments;#错的 |
每个员工和每个部门匹配了一遍（查出的条目数=id数*department数）
错误原因：缺少连接条件
笛卡尔积的解决
编写连接条件: 表1.列 = 表二.列（若多个表连接，至少要用n-1个连接条件）
|
1
2
3
|
select id,employees.name,department_name from employees,departmentsWHERE employees.name = departments.name; |
注：如果要显示的列在要查询的表中名字一样，则要表明，是出自哪个表， eg: employees.name
建议在多表查询时，标明显示的是哪个表的信息 （优化）
优化：可以在FROM后使用表的别名，但是一旦使用别名，后续就一定要都用别名
多表查询的分类
等值连接和非等值连接
- 等值连接：上述的带有=的
- 非等值连接：没有=的
|
1
2
3
|
select t1.id,t1.name,t2.gradefrom employees t1,departments t2WHERE ti.salary BETWEEN t2.lowest_salary AND t2.highest_salary ;#非等值 |
自连接和非自连接
- 非自链接：表1和表2连接
- 自链接：表1和自己连接
|
1
2
3
4
|
#显示员工（t1）和其管理者（t2）的基本信息select t1.id,t1.name,t2.id，t2.namefrom employees t1,employees t2#一个表看作两个表WHERE t1.manage_id = t2.id ;#自连接 |
内连接和外连接
- 内连接：合并含有同一列的表，结果不包括一个表与另一个表不匹配打的行
- 外连接：合并含有同一列的表，结果除了内连接的结果还查询不匹配的行
外连接的分类：左外连接（左表多，补右边），右外连接（右表多，补左边），满外连接
SQL92:使用（+）创建连接
内连接：见上
外连接：左表有数据不匹配，在右表加（+）；反之，在左表加（+），但是MySQL不支持
|
1
|
WHERE t1.department_id = t2.department_id(+)#左连接 |
SQL99：使用JOIN...ON的方式
内连接
|
1
2
3
4
5
|
select t1.id,t1.name,t2.department_name,t3.environmentfrom employees t1 JOIN departments t2ON t1.department_id = t2.department_idJOIN locations t3#加入第二个人表ON t2.department_location = t3.department_location; |
外连接
使用OUTER JOIN...ON...
- 左外连接：LEFT OUTER JOIN
- 右外连接：RIGHT OUTER JOIN
- 满外连接：FULL OUTER JOIN（MySQL不支持）
|
1
2
3
|
select t1.name,t2.department_name#左外连接from employees t1 LEFT OUTER（可省略） JOIN departments t2ON t1.department_id = t2.department_id； |
UMION的使用
合并查询结果
|
1
2
3
|
SELECT colum... FROM table1UNION (ALL)SELECT colum... FROM table2 |
- UNION操作符
两个查询结果的并集，去重（效率低）
- UNION ALL操作符（推荐）
两个查询结果的并集，不去重（效率高）
7种SQL JOINS的实现
中图（内连接）：
|
1
2
3
|
select t1.name,t2.department_namefrom employees t1 JOIN departments t2ON t1.department_id = t2.department_id； |
左上图(左外连接)：
|
1
2
3
|
select t1.name,t2.department_namefrom employees t1 LEFT JOIN departments t2ON t1.department_id = t2.department_id； |
右上图(右外连接)：
|
1
2
3
|
select t1.name,t2.department_namefrom employees t1 RIGHT JOIN departments t2ON t1.department_id = t2.department_id； |
左中图：
|
1
2
3
4
|
select t1.name,t2.department_namefrom employees t1 LEFT JOIN departments t2ON t1.department_id = t2.department_idWHERE t2.department_id IS NULL; |
右中图：
|
1
2
3
4
|
select t1.name,t2.department_namefrom employees t1 RIGHT JOIN departments t2ON t1.department_id = t2.department_idWHERE t1.department_id IS NULL; |
左下图(满外连接)：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
#方式一：左上图 UNION ALL 右中图select t1.name,t2.department_namefrom employees t1 LEFT JOIN departments t2ON t1.department_id = t2.department_idUNION ALLselect t1.name,t2.department_namefrom employees t1 RIGHT JOIN departments t2ON t1.department_id = t2.department_idWHERE t1.department_id IS NULL;#方式二：左中图 UNION ALL 右上图select t1.name,t2.department_namefrom employees t1 LEFT JOIN departments t2ON t1.department_id = t2.department_idWHERE t2.department_id IS NULLUNION ALLselect t1.name,t2.department_namefrom employees t1 RIGHT JOIN departments t2ON t1.department_id = t2.department_id； |
右下图：
|
1
2
3
4
5
6
7
8
9
10
|
#左中图 UNION ALL 右中图select t1.name,t2.department_namefrom employees t1 LEFT JOIN departments t2ON t1.department_id = t2.department_idWHERE t2.department_id IS NULLUNION ALLselect t1.name,t2.department_namefrom employees t1 RIGHT JOIN departments t2ON t1.department_id = t2.department_idWHERE t1.department_id IS NULL; |
SQL语法新特性
自然连接
使用关键字：NATURAL JOIN（不灵活），自动查询表中所有相同字段，然后进行等值连接
USING连接(不适用于自连接)
使用关键字：USING（同名字段），将表中相同名字的字段自动等值连接
|
1
2
3
4
5
6
7
|
select t1.name,t2.department_namefrom employees t1 JOIN departments t2ON t1.department_id = t2.department_id;等价于select t1.name,t2.department_namefrom employees t1 JOIN departments t2USING(department_id); |
到此这篇关于MySQL多表查询的文章就介绍到这了,更多相关MySQL多表查询内容请搜索服务器之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持服务器之家！
原文链接：https://www.cnblogs.com/wht-de-bk/archive/2022/03/04/15966054.html
