MySQL—7种join_tblemp表

7种join示意图

建表语句

CREATE TABLE `tbl_emp` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
`deptId` int(11) DEFAULT NULL,
PRIMARY KEY (`id`) ,
KEY `fk_dept_id`(`deptId`)
)ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8;

CREATE TABLE `tbl_dept` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`deptName` varchar(30) DEFAULT NULL,
`locAdd` varchar(40) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8;
#插入数据

insert into tbl_dept(deptName,locAdd) values('RD',11);
insert into tbl_dept(deptName,locAdd) values('RD',12);
insert into tbl_dept(deptName,locAdd) values('RD',13);
insert into tbl_dept(deptName,locAdd) values('RD',14);
insert into tbl_dept(deptName,locAdd) values('RD',15);

insert into tbl_emp(Name,deptId) values('z3',1);
insert into tbl_emp(Name,deptId) values('z4',1);
insert into tbl_emp(Name,deptId) values('z5',1);

insert into tbl_emp(Name,deptId) values('w5',2);
insert into tbl_emp(Name,deptId) values('w6',2);

insert into tbl_emp(Name,deptId) values('s7',3);
insert into tbl_emp(Name,deptId) values('s8',4);
insert into tbl_emp(Name,deptId) values('s9',51);
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

使用上面刚刚创建好的表以及数据做测试

tbl_emp表所有记录

select * from tbl_emp;
+----+------+--------+
| id | name | deptId |
+----+------+--------+
|  1 | z3   |      1 |
|  2 | z4   |      1 |
|  3 | z5   |      1 |
|  4 | w5   |      2 |
|  5 | w6   |      2 |
|  6 | s7   |      3 |
|  7 | s8   |      4 |
|  8 | s9   |     51 |
+----+------+--------+
1
2
3
4
5
6
7
8
9
10
11
12
13

tbl_dept表所有记录

select * from tbl_dept;
+----+----------+--------+
| id | deptName | locAdd |
+----+----------+--------+
|  1 | RD       | 11     |
|  2 | RD       | 12     |
|  3 | RD       | 13     |
|  4 | RD       | 14     |
|  5 | RD       | 15     |
+----+----------+--------+
1
2
3
4
5
6
7
8
9
10

内连接(inner join)

• A，B共有部分

#两个表中共有的部分
select * from tbl_emp A inner join tbl_dept B on A.deptId = B.id;

+----+------+--------+----+----------+--------+
| id | name | deptId | id | deptName | locAdd |
+----+------+--------+----+----------+--------+
|  1 | z3   |      1 |  1 | RD       | 11     |
|  2 | z4   |      1 |  1 | RD       | 11     |
|  3 | z5   |      1 |  1 | RD       | 11     |
|  4 | w5   |      2 |  2 | RD       | 12     |
|  5 | w6   |      2 |  2 | RD       | 12     |
|  6 | s7   |      3 |  3 | RD       | 13     |
|  7 | s8   |      4 |  4 | RD       | 14     |
+----+------+--------+----+----------+--------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14

左连接(left join)

• A，B共有和A全部

#查询两表共有部分以及A表中全部，如果B表中没有相应数据与之匹配，则结果为Null
select * from tbl_emp A left join tbl_dept B on A.deptId = B.id;

+----+------+--------+------+----------+--------+
| id | name | deptId | id   | deptName | locAdd |
+----+------+--------+------+----------+--------+
|  1 | z3   |      1 |    1 | RD       | 11     |
|  2 | z4   |      1 |    1 | RD       | 11     |
|  3 | z5   |      1 |    1 | RD       | 11     |
|  4 | w5   |      2 |    2 | RD       | 12     |
|  5 | w6   |      2 |    2 | RD       | 12     |
|  6 | s7   |      3 |    3 | RD       | 13     |
|  7 | s8   |      4 |    4 | RD       | 14     |
|  8 | s9   |     51 | NULL | NULL     | NULL   |
+----+------+--------+------+----------+--------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

右连接(right join)

• A，B共有和B全部

#查询两表共有部分以及B表中全部，如果B表中没有相应数据与之匹配，则结果为Null
select * from tbl_emp A right join tbl_dept B on A.deptId = B.id;

+------+------+--------+----+----------+--------+
| id   | name | deptId | id | deptName | locAdd |
+------+------+--------+----+----------+--------+
|    1 | z3   |      1 |  1 | RD       | 11     |
|    2 | z4   |      1 |  1 | RD       | 11     |
|    3 | z5   |      1 |  1 | RD       | 11     |
|    4 | w5   |      2 |  2 | RD       | 12     |
|    5 | w6   |      2 |  2 | RD       | 12     |
|    6 | s7   |      3 |  3 | RD       | 13     |
|    7 | s8   |      4 |  4 | RD       | 14     |
| NULL | NULL |   NULL |  5 | RD       | 15     |
+------+------+--------+----+----------+--------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

左外连接(left outer join)

• A自己独有部分

#查询A表中有，B表中没有的数据
select * from tbl_emp A left outer join tbl_dept B on A.deptId = B.id where B.id is null;

+----+------+--------+------+----------+--------+
| id | name | deptId | id   | deptName | locAdd |
+----+------+--------+------+----------+--------+
|  8 | s9   |     51 | NULL | NULL     | NULL   |
+----+------+--------+------+----------+--------+

1
2
3
4
5
6
7
8
9

右外连接(right outer join)

B自己独有部分

#查询B表中有，A表中没有的数据
select * from tbl_emp A right outer join tbl_dept B on A.deptId = B.id where A.deptId is null;

+------+------+--------+----+----------+--------+
| id   | name | deptId | id | deptName | locAdd |
+------+------+--------+----+----------+--------+
| NULL | NULL |   NULL |  5 | RD       | 15     |
+------+------+--------+----+----------+--------+
1
2
3
4
5
6
7
8

完全连接(union)

• A的独有、B的独有和A、B的共有，即左连接和右连接的数据进行拼接，然后去重

MySQL中不支持full outer join的写法，所以需要借助union

#查询左连接与右连接所有的数据，然后去重的结果
select * from tbl_emp A left join tbl_dept B on A.deptId = B.id union select * from tbl_emp A right join tbl_dept B on A.deptId = B.id;

+------+------+--------+------+----------+--------+
| id   | name | deptId | id   | deptName | locAdd |
+------+------+--------+------+----------+--------+
|    1 | z3   |      1 |    1 | RD       | 11     |
|    2 | z4   |      1 |    1 | RD       | 11     |
|    3 | z5   |      1 |    1 | RD       | 11     |
|    4 | w5   |      2 |    2 | RD       | 12     |
|    5 | w6   |      2 |    2 | RD       | 12     |
|    6 | s7   |      3 |    3 | RD       | 13     |
|    7 | s8   |      4 |    4 | RD       | 14     |
|    8 | s9   |     51 | NULL | NULL     | NULL   |
| NULL | NULL |   NULL |    5 | RD       | 15     |
+------+------+--------+------+----------+--------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

全外连接

• A，B各自独有部分拼接

#查询左外连接和右外连接的数据
select * from tbl_emp A left join tbl_dept B on A.deptId = B.id where B.id is null union select * from tbl_emp A right join tbl_dept B on A.deptId = B.id where A.deptId is null;

+------+------+--------+------+----------+--------+
| id   | name | deptId | id   | deptName | locAdd |
+------+------+--------+------+----------+--------+
|    8 | s9   |     51 | NULL | NULL     | NULL   |
| NULL | NULL |   NULL |    5 | RD       | 15     |
+------+------+--------+------+----------+--------+
1
2
3
4
5
6
7
8
9