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);
使用上面刚刚创建好的表以及数据做测试
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 |
+----+------+--------+
tbl_dept表所有记录
select * from tbl_dept;
+----+----------+--------+
| id | deptName | locAdd |
+----+----------+--------+
| 1 | RD | 11 |
| 2 | RD | 12 |
| 3 | RD | 13 |
| 4 | RD | 14 |
| 5 | RD | 15 |
+----+----------+--------+
内连接(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 |
+----+------+--------+----+----------+--------+
左连接(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 |
+----+------+--------+------+----------+--------+
右连接(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 |
+------+------+--------+----+----------+--------+
左外连接(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 |
+----+------+--------+------+----------+--------+
右外连接(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 |
+------+------+--------+----+----------+--------+
完全连接(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 |
+------+------+--------+------+----------+--------+
全外连接
- 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 |
+------+------+--------+------+----------+--------+