SQL语言 - 连接查询
概述
连接查询包含以下几种类型：
- 连接查询
- 内连接：相当于查询A、B交集部分数据
- 外连接：
- 左外连接：查询左表所有数据，以及两张表交集部分数据
- 右外连接：查询右表所有数据，以及两张表交集部分数据
- 自连接：当前表与自身的连接查询，自连接必须使用表别名
内连接查询
内连接查询的是两张表交集的部分
隐式内连接：
SELECT 字段列表 FROM 表1, 表2 WHERE 条件 ...;
显式内连接：
SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 连接条件 ...;
显式性能比隐式高
例子：
-- 查询员工姓名，及关联的部门的名称
-- 隐式
select e.name,d.name from emp e , dept d where e.dept_id = d.id;
-- 显式
select e.name, d.name from emp e inner join dept d on e.dept_id = d.id;
1
2
3
4
5
2
3
4
5
mysql> select e.name, d.name from emp e inner join dept d on e.dept_id = d.id;
+-----------+-----------+
| name | name |
+-----------+-----------+
| 张无忌 | 研发部 |
| 杨逍 | 研发部 |
| 韦一笑 | 研发部 |
| 常遇春 | 研发部 |
| 小昭 | 研发部 |
| 赵敏 | 市场部 |
| 鹿杖客 | 市场部 |
| 鹤笔翁 | 市场部 |
| 方东白 | 市场部 |
| 灭绝 | 财务部 |
| 周芷若 | 财务部 |
| 丁敏君 | 财务部 |
| 张三丰 | 销售部 |
| 俞莲舟 | 销售部 |
| 宋远桥 | 销售部 |
| 金庸 | 总经办 |
+-----------+-----------+
16 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
外连接查询
左外连接：
查询左表所有数据，以及两张表交集部分数据
SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 条件 ...;
相当于查询表1的所有数据，包含表1和表2交集部分数据
右外连接：
查询右表所有数据，以及两张表交集部分数据
SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 条件 ...;
例子：
-- 左
select e.*, d.name from emp as e left join dept as d on e.dept_id = d.id;
select d.name, e.* from dept d left join emp e on e.dept_id = d.id; -- 这条语句与下面的语句效果一样,左右连接可以相互转换
-- 右
select d.name, e.* from emp as e right join dept as d on e.dept_id = d.id;
1
2
3
4
5
2
3
4
5
mysql> select e.*, d.name from emp as e left join dept as d on e.dept_id = d.id;
+----+-----------+------+--------------------+--------+------------+-----------+---------+-----------+
| id | name | age | job | salary | entrydate | managerid | dept_id | name |
+----+-----------+------+--------------------+--------+------------+-----------+---------+-----------+
| 1 | 金庸 | 66 | 总裁 | 20000 | 2000-01-01 | NULL | 5 | 总经办 |
| 2 | 张无忌 | 20 | 项目经理 | 12500 | 2005-12-05 | 1 | 1 | 研发部 |
| 3 | 杨逍 | 33 | 开发 | 8400 | 2000-11-03 | 2 | 1 | 研发部 |
| 4 | 韦一笑 | 48 | 开发 | 11000 | 2002-02-05 | 2 | 1 | 研发部 |
| 5 | 常遇春 | 43 | 开发 | 10500 | 2004-09-07 | 3 | 1 | 研发部 |
| 6 | 小昭 | 19 | 程序员鼓励师 | 6600 | 2004-10-12 | 2 | 1 | 研发部 |
| 7 | 灭绝 | 60 | 财务总监 | 8500 | 2002-09-12 | 1 | 3 | 财务部 |
| 8 | 周芷若 | 19 | 会计 | 48000 | 2006-06-02 | 7 | 3 | 财务部 |
| 9 | 丁敏君 | 23 | 出纳 | 5250 | 2009-05-13 | 7 | 3 | 财务部 |
| 10 | 赵敏 | 20 | 市场部总监 | 12500 | 2004-10-12 | 1 | 2 | 市场部 |
| 11 | 鹿杖客 | 56 | 职员 | 3750 | 2006-10-03 | 10 | 2 | 市场部 |
| 12 | 鹤笔翁 | 19 | 职员 | 3750 | 2007-05-09 | 10 | 2 | 市场部 |
| 13 | 方东白 | 19 | 职员 | 5500 | 2009-02-12 | 10 | 2 | 市场部 |
| 14 | 张三丰 | 88 | 销售总监 | 14000 | 2004-10-12 | 1 | 4 | 销售部 |
| 15 | 俞莲舟 | 38 | 销售 | 4600 | 2004-10-12 | 14 | 4 | 销售部 |
| 16 | 宋远桥 | 40 | 销售 | 4600 | 2004-10-12 | 14 | 4 | 销售部 |
| 17 | 陈友谅 | 42 | NULL | 2000 | 2011-10-12 | 1 | NULL | NULL |
+----+-----------+------+--------------------+--------+------------+-----------+---------+-----------+
17 rows in set (0.01 sec)
mysql> select d.name, e.* from dept d left join emp e on e.dept_id = d.id;
+-----------+------+-----------+------+--------------------+--------+------------+-----------+---------+
| name | id | name | age | job | salary | entrydate | managerid | dept_id |
+-----------+------+-----------+------+--------------------+--------+------------+-----------+---------+
| 研发部 | 2 | 张无忌 | 20 | 项目经理 | 12500 | 2005-12-05 | 1 | 1 |
| 研发部 | 3 | 杨逍 | 33 | 开发 | 8400 | 2000-11-03 | 2 | 1 |
| 研发部 | 4 | 韦一笑 | 48 | 开发 | 11000 | 2002-02-05 | 2 | 1 |
| 研发部 | 5 | 常遇春 | 43 | 开发 | 10500 | 2004-09-07 | 3 | 1 |
| 研发部 | 6 | 小昭 | 19 | 程序员鼓励师 | 6600 | 2004-10-12 | 2 | 1 |
| 市场部 | 10 | 赵敏 | 20 | 市场部总监 | 12500 | 2004-10-12 | 1 | 2 |
| 市场部 | 11 | 鹿杖客 | 56 | 职员 | 3750 | 2006-10-03 | 10 | 2 |
| 市场部 | 12 | 鹤笔翁 | 19 | 职员 | 3750 | 2007-05-09 | 10 | 2 |
| 市场部 | 13 | 方东白 | 19 | 职员 | 5500 | 2009-02-12 | 10 | 2 |
| 财务部 | 7 | 灭绝 | 60 | 财务总监 | 8500 | 2002-09-12 | 1 | 3 |
| 财务部 | 8 | 周芷若 | 19 | 会计 | 48000 | 2006-06-02 | 7 | 3 |
| 财务部 | 9 | 丁敏君 | 23 | 出纳 | 5250 | 2009-05-13 | 7 | 3 |
| 销售部 | 14 | 张三丰 | 88 | 销售总监 | 14000 | 2004-10-12 | 1 | 4 |
| 销售部 | 15 | 俞莲舟 | 38 | 销售 | 4600 | 2004-10-12 | 14 | 4 |
| 销售部 | 16 | 宋远桥 | 40 | 销售 | 4600 | 2004-10-12 | 14 | 4 |
| 总经办 | 1 | 金庸 | 66 | 总裁 | 20000 | 2000-01-01 | NULL | 5 |
| 人事部 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
+-----------+------+-----------+------+--------------------+--------+------------+-----------+---------+
17 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
左连接可以查询到没有dept的emp，右连接可以查询到没有emp的dept
自连接
自连接查询
自连接查询，顾名思义，就是自己连接自己，也就是把一张表连接查询多次。
语法：
SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ...;
自连接查询，可以是内连接查询，也可以是外连接查询
例子：
-- 查询员工及其所属领导的名字
select a.name , b.name from emp a , emp b where a.managerid = b.id;
-- 没有领导的也查询出来
select a.name '员工', b.name '领导' from emp a left join emp b on a.managerid =b.id;
1
2
3
4
2
3
4
mysql> select a.name , b.name from emp a , emp b where a.managerid = b.id;
+-----------+-----------+
| name | name |
+-----------+-----------+
| 张无忌 | 金庸 |
| 杨逍 | 张无忌 |
| 韦一笑 | 张无忌 |
| 常遇春 | 杨逍 |
| 小昭 | 张无忌 |
| 灭绝 | 金庸 |
| 周芷若 | 灭绝 |
| 丁敏君 | 灭绝 |
| 赵敏 | 金庸 |
| 鹿杖客 | 赵敏 |
| 鹤笔翁 | 赵敏 |
| 方东白 | 赵敏 |
| 张三丰 | 金庸 |
| 俞莲舟 | 张三丰 |
| 宋远桥 | 张三丰 |
| 陈友谅 | 金庸 |
+-----------+-----------+
16 rows in set (0.00 sec)
mysql> select a.name '员工', b.name '领导' from emp a left join emp b on a.managerid =b.id;
+-----------+-----------+
| 员工 | 领导 |
+-----------+-----------+
| 金庸 | NULL |
| 张无忌 | 金庸 |
| 杨逍 | 张无忌 |
| 韦一笑 | 张无忌 |
| 常遇春 | 杨逍 |
| 小昭 | 张无忌 |
| 灭绝 | 金庸 |
| 周芷若 | 灭绝 |
| 丁敏君 | 灭绝 |
| 赵敏 | 金庸 |
| 鹿杖客 | 赵敏 |
| 鹤笔翁 | 赵敏 |
| 方东白 | 赵敏 |
| 张三丰 | 金庸 |
| 俞莲舟 | 张三丰 |
| 宋远桥 | 张三丰 |
| 陈友谅 | 金庸 |
+-----------+-----------+
17 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
联合查询 union, union all
对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集。
语法：
SELECT 字段列表 FROM 表A ...
UNION [ALL]
SELECT 字段列表 FROM 表B ...
1
2
3
2
3
- 对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。
- union all 会将全部的数据直接合并在一起，union 会对合并之后的数据去重。
- 联合查询比使用or效率高，不会使索引失效
-- 将薪资低于 5000 的员工 , 和 年龄大于 50 岁的员工全部查询出来.
select * from emp where salary < 5000
union all
select * from emp where age > 50;
-- union all查询出来的结果，仅仅进行简单的合并，并未去重。
select * from emp where salary < 5000
union
select * from emp where age > 50;
-- 等价于
select * from emp where salary < 5000 or age > 50;
1
2
3
4
5
6
7
8
9
10
2
3
4
5
6
7
8
9
10
mysql> select * from emp where salary < 5000 union select * from emp where age > 50;
+----+-----------+------+--------------+--------+------------+-----------+---------+
| id | name | age | job | salary | entrydate | managerid | dept_id |
+----+-----------+------+--------------+--------+------------+-----------+---------+
| 11 | 鹿杖客 | 56 | 职员 | 3750 | 2006-10-03 | 10 | 2 |
| 12 | 鹤笔翁 | 19 | 职员 | 3750 | 2007-05-09 | 10 | 2 |
| 15 | 俞莲舟 | 38 | 销售 | 4600 | 2004-10-12 | 14 | 4 |
| 16 | 宋远桥 | 40 | 销售 | 4600 | 2004-10-12 | 14 | 4 |
| 17 | 陈友谅 | 42 | NULL | 2000 | 2011-10-12 | 1 | NULL |
| 1 | 金庸 | 66 | 总裁 | 20000 | 2000-01-01 | NULL | 5 |
| 7 | 灭绝 | 60 | 财务总监 | 8500 | 2002-09-12 | 1 | 3 |
| 14 | 张三丰 | 88 | 销售总监 | 14000 | 2004-10-12 | 1 | 4 |
+----+-----------+------+--------------+--------+------------+-----------+---------+
8 rows in set (0.00 sec)
mysql> select * from emp where salary < 5000 or age > 50;
+----+-----------+------+--------------+--------+------------+-----------+---------+
| id | name | age | job | salary | entrydate | managerid | dept_id |
+----+-----------+------+--------------+--------+------------+-----------+---------+
| 1 | 金庸 | 66 | 总裁 | 20000 | 2000-01-01 | NULL | 5 |
| 7 | 灭绝 | 60 | 财务总监 | 8500 | 2002-09-12 | 1 | 3 |
| 11 | 鹿杖客 | 56 | 职员 | 3750 | 2006-10-03 | 10 | 2 |
| 12 | 鹤笔翁 | 19 | 职员 | 3750 | 2007-05-09 | 10 | 2 |
| 14 | 张三丰 | 88 | 销售总监 | 14000 | 2004-10-12 | 1 | 4 |
| 15 | 俞莲舟 | 38 | 销售 | 4600 | 2004-10-12 | 14 | 4 |
| 16 | 宋远桥 | 40 | 销售 | 4600 | 2004-10-12 | 14 | 4 |
| 17 | 陈友谅 | 42 | NULL | 2000 | 2011-10-12 | 1 | NULL |
+----+-----------+------+--------------+--------+------------+-----------+---------+
8 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29