数据库学习笔记-七种join的理论和实践
2020-03-10 本文已影响0人
许瑞锐
[TOC]
七种join的理论
左连接
左表的全部，顺便带上右表和左表有关联的记录
image-20200309111215888
select a.*,b.* from a left join b on a.id=b.a_id
右连接
右表的全部，顺便带上左边和右表有关联的记录
image-20200309112152543.png
select a.*,b.* from a right join b on a.id=b.a_id
内连接
左右表都有的记录，就是左右表的交集
image-20200309112344362.png
select a.*,b.* from a inner join b on a.id=b.a_id
左外连接
左表独有的记录,去除两者的交集
image-20200309113514978.png
select a.*,b.* from a inner join b on b.a_id = a.id where b.a_id is null
右外连接
右表独有的记录，去除两者的交集
image-20200309113722250.png
select a.*,b.* from a right join b on a.id = b.a_id where a_id is null
全连接
全部表的记录再加上共同的记录
image-20200309114547380.png
oracle : select a.*,b.* from a full outer join b on a.id = b.a_id
myqsl: select a.*,b.* from a left join b on a.id=b.a_id
union
select a.*,b.* from a right join a on a.id=b.a_id
全外连接
左右表的各自独有的记录
image-20200309114202727.png
oracle: select a.*,b.* from a full outer join b on a.id=b.a_id where a.id is null or b.a_id is null
七种join的实践
首先建表，建立两张表，分别是学生表和教师表，学生表中存储教师表的主键id
学生表如下：
DROP TABLE IF EXISTS `stuent`;
CREATE TABLE `stuent` (
`id` int(10) NOT NULL COMMENT '学生表id',
`stuent_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '学生姓名',
`teacher_id` int(10) NULL DEFAULT NULL COMMENT '教师id',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of stuent
-- ----------------------------
INSERT INTO `stuent` VALUES (1, '张三', 1);
INSERT INTO `stuent` VALUES (2, '李四', 2);
INSERT INTO `stuent` VALUES (3, '许二', 1);
INSERT INTO `stuent` VALUES (4, '黄五', 2);
INSERT INTO `stuent` VALUES (5, '江湖', 1);
SET FOREIGN_KEY_CHECKS = 1;
教师表如下：
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
`id` int(10) NOT NULL AUTO_INCREMENT COMMENT '教师表自增id',
`teacher_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '教师姓名',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES (1, '李老师');
INSERT INTO `teacher` VALUES (2, '黄老师');
INSERT INTO `teacher` VALUES (3, '谢老师');
SET FOREIGN_KEY_CHECKS = 1;
左连接
这个没啥好讲的，直接连接，主要是注意on条件：
select s.*,t.* from stuent s left join teacher t on t.id = s.teacher_id order by s.id asc
结果如下：
+----+-------------+------------+------+--------------+
| id | stuent_name | teacher_id | id | teacher_name |
+----+-------------+------------+------+--------------+
| 1 | 张三 | 1 | 1 | 李老师 |
| 2 | 李四 | 2 | 2 | 黄老师 |
| 3 | 许二 | 1 | 1 | 李老师 |
| 4 | 黄五 | 2 | 2 | 黄老师 |
| 5 | 江湖 | 1 | 1 | 李老师 |
+----+-------------+------------+------+--------------+
右连接
同上
select s.*,t.* from stuent s right join teacher t on t.id = s.teacher_id order by s.id asc
结果如下：
+------+-------------+------------+----+--------------+
| id | stuent_name | teacher_id | id | teacher_name |
+------+-------------+------------+----+--------------+
| NULL | NULL | NULL | 3 | 谢老师 |
| 1 | 张三 | 1 | 1 | 李老师 |
| 2 | 李四 | 2 | 2 | 黄老师 |
| 3 | 许二 | 1 | 1 | 李老师 |
| 4 | 黄五 | 2 | 2 | 黄老师 |
| 5 | 江湖 | 1 | 1 | 李老师 |
+------+-------------+------------+----+--------------+
需要注意的是，为什么右连接比左连接多一条记录呢，因为连接(join)根据方向而变来选择主表，left join就是选择左表为主表，左表只有五条记录，所有最后连接的结果是五条记录。然而右表有六条记录，所以右连接最后结果会有六条记录，但是这条多出来的记录在左表又没有相关联的记录，所以有关学生的字段全是null是空的。
内连接
内连接MySQL有特殊的关键字，直接使用就行：
select s.*,t.* from stuent s inner join teacher t on t.id = s.teacher_id order by s.id asc
结果如下：
+----+-------------+------------+----+--------------+
| id | stuent_name | teacher_id | id | teacher_name |
+----+-------------+------------+----+--------------+
| 1 | 张三 | 1 | 1 | 李老师 |
| 2 | 李四 | 2 | 2 | 黄老师 |
| 3 | 许二 | 1 | 1 | 李老师 |
| 4 | 黄五 | 2 | 2 | 黄老师 |
| 5 | 江湖 | 1 | 1 | 李老师 |
+----+-------------+------------+----+--------------+
MySQL根据on关键字后面的条件选择出了两张表的交集，也确实是这些数据
左外连接
左外连接要求查出左表的全部记录-左右表之间的交集，这个应该怎么查呢，直接加上一个条件，设置右表对应的id为空，既在左连接的前提下，选择出右表id为空的数据，既减去了右表的数据：
select s.*,t.* from stuent s left join teacher t on t.id = s.teacher_id where t.id is NULL order by s.id asc
查出来的结果集是空的，因为所有数据的有关联，左连接既是交集，所以左连接减去交集会变成空的集合。这个时候我们增加一条学生的数据：
INSERT INTO `stuent` VALUES (6, '张大');
再执行左外连接的命令返回的结果集如下：
+----+-------------+------------+------+--------------+
| id | stuent_name | teacher_id | id | teacher_name |
+----+-------------+------------+------+--------------+
| 6 | 张大 | NULL | NULL | NULL |
+----+-------------+------------+------+--------------+
右外连接
和左外连接理论相同，反过来即可：
select s.*,t.* from stuent s right join teacher t on t.id = s.teacher_id where s.id is NULL order by s.id asc
返回的结果集合如下：
+------+-------------+------------+----+--------------+
| id | stuent_name | teacher_id | id | teacher_name |
+------+-------------+------------+----+--------------+
| NULL | NULL | NULL | 3 | 谢老师 |
+------+-------------+------------+----+--------------+
全连接
全连接oracle有关键字，但是MySQL没有，所以需要一点小技巧。全连接=左连接+右连接，对于这种需求左连接和右连接中间的加号我们用一个union关键字代替，甚至可以去除重复记录：
select s.*,t.* from stuent s left join teacher t on t.id = s.teacher_id union select s.*,t.* from stuent s right join teacher t on t.id = s.teacher_id
返回的结果集合如下：
+------+-------------+------------+------+--------------+
| id | stuent_name | teacher_id | id | teacher_name |
+------+-------------+------------+------+--------------+
| 1 | 张三 | 1 | 1 | 李老师 |
| 3 | 许二 | 1 | 1 | 李老师 |
| 5 | 江湖 | 1 | 1 | 李老师 |
| 2 | 李四 | 2 | 2 | 黄老师 |
| 4 | 黄五 | 2 | 2 | 黄老师 |
| NULL | NULL | NULL | 3 | 谢老师 |
+------+-------------+------------+------+--------------+
全外连接
全外连接 = 左外连接 + 右外连接：
select s.*,t.* from stuent s left join teacher t on t.id = s.teacher_id where t.id is NULL union select s.*,t.* from stuent s right join teacher t on t.id = s.teacher_id where s.id is NULL
返回的结果集合如下：
+------+-------------+------------+------+--------------+
| id | stuent_name | teacher_id | id | teacher_name |
+------+-------------+------------+------+--------------+
| NULL | NULL | NULL | 3 | 谢老师 |
+------+-------------+------------+------+--------------+
