侧边栏壁纸
博主头像
乌拉队长博主等级

你只管努力,其余的交给命运

  • 累计撰写 122 篇文章
  • 累计创建 34 个标签
  • 累计收到 31 条评论

目 录CONTENT

文章目录

MySQL内连接、左连接、右连接、交叉连接、全连接、差集、自连接的区别

乌拉队长
2022-02-12 / 0 评论 / 0 点赞 / 779 阅读 / 7,475 字

MySQL内连接、左连接、右连接、交叉连接、自连接的区别

废话不多说,直接上才艺

初始化数据

定义两个表:学生信息表student和学生分数表sc。
定义如下:

# student表
CREATE TABLE `student` (
  `id` int(20) NOT NULL AUTO_INCREMENT COMMENT '学生id',
  `name` varchar(20) DEFAULT NULL COMMENT '学生姓名',
  `sex` varchar(10) DEFAULT NULL COMMENT '学生性别',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 COMMENT='学生表' 

# sc表
CREATE TABLE `sc` (
  `id` int(20) NOT NULL AUTO_INCREMENT COMMENT '学生成绩id',
  `score` int(20) DEFAULT NULL COMMENT '学生分数',
  `sid` int(20) DEFAULT NULL COMMENT '学生id',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1 COMMENT='学生成绩表'

表结构如下:

# student表
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(20)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | YES  |     | NULL    |                |
| sex   | varchar(10) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+

# sc表
+-------+---------+------+-----+---------+----------------+
| Field | Type    | Null | Key | Default | Extra          |
+-------+---------+------+-----+---------+----------------+
| id    | int(20) | NO   | PRI | NULL    | auto_increment |
| score | int(20) | YES  |     | NULL    |                |
| sid   | int(20) | YES  |     | NULL    |                |
+-------+---------+------+-----+---------+----------------+

向表中插入数据,两个表中的数据如下:

mysql> select * from student;
+----+------+------+
| id | name | sex  |
+----+------+------+
|  1 | 张三 | 男   |
|  2 | 李四 | 男   |
|  3 | 王五 | NULL |
|  4 | NULL | 女   |
|  5 | 赵六 | 女   |
+----+------+------+


mysql> select * from sc;
+----+-------+------+
| id | score | sid  |
+----+-------+------+
|  1 |    70 |    1 |
|  2 |    80 |    3 |
|  3 |    95 |    2 |
|  4 |    99 | NULL |
|  5 |  NULL |    4 |
|  6 |  NULL |    8 |
|  7 |    60 |   12 |
+----+-------+------+

然后我们开始说主要内容:

内连接(inner join, 即交集)

内连接:只返回两个表中能够匹配的数据,如果数据a只存在于表t1而不存在于表t2,则不被返回
(使用inner join时,也可以省略inner直接使用join进行内连接,两个效果相同)

# 内连接只返回student与sc表中满足where匹配条件的数据,
# 而student中的“赵六”信息在sc表中不存在,因此,没有被返回。
# 同理,sc表中的id为4、6、7的数据由于其sid在student表中不存在,
# 因此也没有被返回。
mysql> select * from student t1 inner join sc t2 where t1.id=t2.sid;
+----+------+------+----+-------+------+
| id | name | sex  | id | score | sid  |
+----+------+------+----+-------+------+
|  1 | 张三 | 男   |  1 |    70 |    1 |
|  2 | 李四 | 男   |  3 |    95 |    2 |
|  3 | 王五 | NULL |  2 |    80 |    3 |
|  4 | NULL | 女   |  5 |  NULL |    4 |
+----+------+------+----+-------+------+


# 内连接与表的顺序无关,因此调换两个表的先后顺序不影响最终的结果,
# 只是数据显示的先后顺序有变化。
mysql> select * from sc t1 inner join student t2 where t1.sid=t2.id;
+----+-------+------+----+------+------+
| id | score | sid  | id | name | sex  |
+----+-------+------+----+------+------+
|  1 |    70 |    1 |  1 | 张三 | 男   |
|  3 |    95 |    2 |  2 | 李四 | 男   |
|  2 |    80 |    3 |  3 | 王五 | NULL |
|  5 |  NULL |    4 |  4 | NULL | 女   |
+----+-------+------+----+------+------+

左连接(left join或left outer join)

左连接:即左外连接,默认left join=left outer join,左连接以左表为基准表,返回左表中所有查询键值的数据,右表中如果键值不存在则以null值填充

# 左连接只返回student表中与条件所匹配的数据,因为student表中
# id为5的“赵六”数据在sc表中不存在,因此返回值以NULL填充。
mysql> select t1.id,t1.name,t2.score from student t1 left outer join sc t2 on t1.id=t2.sid;
+----+------+-------+
| id | name | score |
+----+------+-------+
|  1 | 张三 |    70 |
|  2 | 李四 |    95 |
|  3 | 王五 |    80 |
|  4 | NULL |  NULL |
|  5 | 赵六 |  NULL |
+----+------+-------+


# 使用left join替换left outer join查询结果相同
mysql> select t1.id,t1.name,t2.score from student t1 left join sc t2 on t1.id=t2.sid;
+----+------+-------+
| id | name | score |
+----+------+-------+
|  1 | 张三 |    70 |
|  2 | 李四 |    95 |
|  3 | 王五 |    80 |
|  4 | NULL |  NULL |
|  5 | 赵六 |  NULL |
+----+------+-------+

但是,外连接即左连接和右连接的查询结果与表的顺序有关,左连接以靠前的表为基准表,右连接以靠后的表为基准表,下面以左连接为例进行说明,右连接同理,后面不再赘述。

# 如果将sc表放在left join前面,则查询结果以sc表为基准表,
# 因此,对于student表中没有的数据,在查询结果中的name栏均以null填充
mysql> select t1.id,t2.name,t1.score from sc t1 left join student t2 on t1.sid=t2.id;
+----+------+-------+
| id | name | score |
+----+------+-------+
|  1 | 张三 |    70 |
|  2 | 王五 |    80 |
|  3 | 李四 |    95 |
|  4 | NULL |    99 |
|  5 | NULL |  NULL |
|  6 | NULL |  NULL |
|  7 | NULL |    60 |
+----+------+-------+

右连接(right join)

右连接:即右外连接,以右表为基准表,返回右表中所有查询键值的数据,如果左表中键值不存在,则以null值填充

# 右连接,以靠后的表为基准表,因此,student表中查不到的数据均以null填充
mysql> select t1.id,t1.name,t2.score from student t1 right join sc t2 on t1.id=t2.sid;
+------+------+-------+
| id   | name | score |
+------+------+-------+
|    1 | 张三 |    70 |
|    3 | 王五 |    80 |
|    2 | 李四 |    95 |
| NULL | NULL |    99 |
|    4 | NULL |  NULL |
| NULL | NULL |  NULL |
| NULL | NULL |    60 |
+------+------+-------+

交叉连接(cross join)

交叉连接:即笛卡尔积,如果没有查询条件的情况下,默认返回的数据条数=t1表中的数据行数*t2表中的数据行数。 (交叉连接和内连接一样,与表的顺序无关,表的顺序只影响查询结果的显示先后)

# 交叉连接相当于:
# 将t1表中的第一行数据分别与t2表中的每行数据拼接,
# 再将t1表中的第二行数据分别与t2表中的每行数据拼接,
# 以此类推...
# 并返回最终结果。
mysql> select * from student t1 cross join sc t2;
+----+------+------+----+-------+------+
| id | name | sex  | id | score | sid  |
+----+------+------+----+-------+------+
|  1 | 张三 | 男   |  1 |    70 |    1 |
|  2 | 李四 | 男   |  1 |    70 |    1 |
|  3 | 王五 | NULL |  1 |    70 |    1 |
|  4 | NULL | 女   |  1 |    70 |    1 |
|  5 | 赵六 | 女   |  1 |    70 |    1 |
|  1 | 张三 | 男   |  2 |    80 |    3 |
|  2 | 李四 | 男   |  2 |    80 |    3 |
|  3 | 王五 | NULL |  2 |    80 |    3 |
|  4 | NULL | 女   |  2 |    80 |    3 |
|  5 | 赵六 | 女   |  2 |    80 |    3 |
|  1 | 张三 | 男   |  3 |    95 |    2 |
|  2 | 李四 | 男   |  3 |    95 |    2 |
|  3 | 王五 | NULL |  3 |    95 |    2 |
|  4 | NULL | 女   |  3 |    95 |    2 |
|  5 | 赵六 | 女   |  3 |    95 |    2 |
|  1 | 张三 | 男   |  4 |    99 | NULL |
|  2 | 李四 | 男   |  4 |    99 | NULL |
|  3 | 王五 | NULL |  4 |    99 | NULL |
|  4 | NULL | 女   |  4 |    99 | NULL |
|  5 | 赵六 | 女   |  4 |    99 | NULL |
|  1 | 张三 | 男   |  5 |  NULL |    4 |
|  2 | 李四 | 男   |  5 |  NULL |    4 |
|  3 | 王五 | NULL |  5 |  NULL |    4 |
|  4 | NULL | 女   |  5 |  NULL |    4 |
|  5 | 赵六 | 女   |  5 |  NULL |    4 |
|  1 | 张三 | 男   |  6 |  NULL |    8 |
|  2 | 李四 | 男   |  6 |  NULL |    8 |
|  3 | 王五 | NULL |  6 |  NULL |    8 |
|  4 | NULL | 女   |  6 |  NULL |    8 |
|  5 | 赵六 | 女   |  6 |  NULL |    8 |
|  1 | 张三 | 男   |  7 |    60 |   12 |
|  2 | 李四 | 男   |  7 |    60 |   12 |
|  3 | 王五 | NULL |  7 |    60 |   12 |
|  4 | NULL | 女   |  7 |    60 |   12 |
|  5 | 赵六 | 女   |  7 |    60 |   12 |
+----+------+------+----+-------+------+


# 如果交叉连接添加了“查询条件”(比如下面例子中的t1.id=t2.sid),
# 则相当于从上述无条件情况下的查询结果中筛选满足"查询条件"的数据并返回.
mysql> select * from student t1 cross join sc t2 on t1.id=t2.sid;
+----+------+------+----+-------+------+
| id | name | sex  | id | score | sid  |
+----+------+------+----+-------+------+
|  1 | 张三 | 男   |  1 |    70 |    1 |
|  2 | 李四 | 男   |  3 |    95 |    2 |
|  3 | 王五 | NULL |  2 |    80 |    3 |
|  4 | NULL | 女   |  5 |  NULL |    4 |
+----+------+------+----+-------+------+

全连接(union, 即并集)

全连接: 即将两个查询的结果合并到一起再返回。
(SQL中全连接关键词为full join,而MySQL中没有全连接,因此可以使用union关键词来实现全连接查询。)

# 可以将全连接看作对两个查询语句
# (即:select * from student t1和select * from sc t2)的结果进行合并
mysql> select * from student t1 union select * from sc t2;
+----+------+------+
| id | name | sex  |
+----+------+------+
|  1 | 张三 | 男   |
|  2 | 李四 | 男   |
|  3 | 王五 | NULL |
|  4 | NULL | 女   |
|  5 | 赵六 | 女   |
|  1 | 70   | 1    |
|  2 | 80   | 3    |
|  3 | 95   | 2    |
|  4 | 99   | NULL |
|  5 | NULL | 4    |
|  6 | NULL | 8    |
|  7 | 60   | 12   |
+----+------+------+

注意:

  1. union会自动去除重复的记录,如果要返回全部结果可以使用union all
  2. 多条查询语句的查询的列数必须是一致的
  3. 多条查询语句的查询的列的类型几乎相同
# 不去重  --  使用union all返回全部查询结果
mysql> select t1.id id from student t1 union all select t2.sid id from sc t2;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    1 |
|    3 |
|    2 |
| NULL |
|    4 |
|    8 |
|   12 |
+------+


# union默认去重
mysql> select t1.id id from student t1 union select t2.sid id from sc t2;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
| NULL |
|    8 |
|   12 |
+------+

差集(not in)

差集:返回只存在于第一个查询结果中的数据。

# sc表中sid为8和12的数据在student表中没有对应的记录
mysql> select * from sc t1 where t1.sid not in (select t2.id from student t2);
+----+-------+------+
| id | score | sid  |
+----+-------+------+
|  6 |  NULL |    8 |
|  7 |    60 |   12 |
+----+-------+------+

自连接

自连接本身不属于连接方式的一种,但是有时候我们可能会遇到一些需求,比如:
查询学生成绩表中比sid=12的学生(此处我们不考虑student表,假设sid=12的学生是存在的)成绩高的所有数据。
一般情况下我们需要如下步骤:

1)查询sid=12的学生的成绩是多少(查询结果为60)

2)查询sc表中所有score>60的数据

代码如下:

# 第一步:查询sid=12的学生的成绩是多少
mysql> select score from sc where sid=12;
+-------+
| score |
+-------+
|    60 |
+-------+


# 第二步:查询sc表中所有score>60的数据
mysql> select * from sc where score>60;
+----+-------+------+
| id | score | sid  |
+----+-------+------+
|  1 |    70 |    1 |
|  2 |    80 |    3 |
|  3 |    95 |    2 |
|  4 |    99 | NULL |
+----+-------+------+

这种方式需要对中间的结果进行人为干预,因此不适合自动化处理和操作。

因此,我们可以采用自连接的方式实现同样的操作。

mysql> select t2.* from sc t1, sc t2 where t1.sid=12 and t2.score>t1.score;
+----+-------+------+
| id | score | sid  |
+----+-------+------+
|  1 |    70 |    1 |
|  2 |    80 |    3 |
|  3 |    95 |    2 |
|  4 |    99 | NULL |
+----+-------+------+

自然连接(natural join)

自然连接: 自然连接自动筛选两个表中相同字段的等值记录并返回。

# 两个表中都有id字段,因此自然连接返回两个表中id相等的记录
mysql> select * from student t1 natural join sc t2 ;
+----+------+------+-------+------+
| id | name | sex  | score | sid  |
+----+------+------+-------+------+
|  1 | 张三 | 男   |    70 |    1 |
|  2 | 李四 | 男   |    80 |    3 |
|  3 | 王五 | NULL |    95 |    2 |
|  4 | NULL | 女   |    99 | NULL |
|  5 | 赵六 | 女   |  NULL |    4 |
+----+------+------+-------+------+


# 上述自然连接等价于如下等值连接
# 注意:join on的条件可以写多个,在本例的两个表中只有一个id字段是两个表共有的
mysql> select * from student t1 inner JOIN sc t2 ON t1.id=t2.id;
+----+------+------+----+-------+------+
| id | name | sex  | id | score | sid  |
+----+------+------+----+-------+------+
|  1 | 张三 | 男   |  1 |    70 |    1 |
|  2 | 李四 | 男   |  2 |    80 |    3 |
|  3 | 王五 | NULL |  3 |    95 |    2 |
|  4 | NULL | 女   |  4 |    99 | NULL |
|  5 | 赵六 | 女   |  5 |  NULL |    4 |
+----+------+------+----+-------+------+

也就是说:自然连接是等值连接的简写形式。
这在两个表有非常多相同字段且需要他们都相等时写起来比较方便,不用一个一个写等价条件了,但是在相同字段较少时,建议还是使用等值连接,因为等值连接的写法比较简洁明了。

0

评论区