数据库学习笔记-七种join的理论和实践

[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 | 谢老师       |
+------+-------------+------------+------+--------------+