【Mysql】图解左、右、内与全连接

朴灿烈づ我的快乐病毒、 2022-12-13 01:39 154阅读 0赞

### 一、前言 ###

使用学生表与成绩表来演示Mysql中的各种连接查找

学生表的建表语句如下：

CREATE TABLE student(
      id int(11) NOT NULL AUTO_INCREMENT COMMENT '自增序号',
      st_id int(11) DEFAULT NULL COMMENT '学生id',
      st_name varchar(255) DEFAULT NULL COMMENT '学生姓名',
      PRIMARY KEY (`id`) USING BTREE
    ) ENGINE = InnoDB;

成绩表的建表语句如下：

CREATE TABLE score(
      id int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
      st_id int(11) DEFAULT NULL COMMENT '学生id',
      subject varchar(255) DEFAULT NULL COMMENT '学科名称',
      grade int(11) DEFAULT NULL COMMENT '学科成绩',
      PRIMARY KEY (`id`) USING BTREE
    ) ENGINE = InnoDB ;

初始数据如下：

![20201009153123583.png][]![20201009153156935.png][]

--------------------

### 二、内连接 ###

按照关联字段取出两个表中的记录，保留的是两个表的交集。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzNTkxOTAz_size_16_color_FFFFFF_t_70][]

例如：

SELECT
    	student.st_id,
    	student.st_name,
    	score.subject,
    	score.grade 
    FROM
    	student
    	INNER JOIN score ON student.st_id = score.st_id;

执行结果：

![20201009160357263.png][]

对于关联字段st\_id，左表与右表都有1001与1002。

--------------------

### 三、左连接 ###

按照关联字段取出两个表中的记录，保留左表所有的记录，以及满足连接条件的右表记录，右表中不满足连接条件的会被置为null。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzNTkxOTAz_size_16_color_FFFFFF_t_70 1][]

例如：

SELECT
    	student.st_id,
    	student.st_name,
    	score.subject,
    	score.grade 
    FROM
    	student
    	LEFT JOIN score ON student.st_id = score.st_id;

执行结果：

![20201009163907699.png][]

对于关联字段st\_id，展示左表所有的记录。由于右表缺少1003，则1003这行的subject与grade的值被置为null。

--------------------

### 四、右连接 ###

按照关联字段取出两个表中的记录，保留右表所有的记录，以及满足连接条件的左表记录，左表中不满足连接条件的会被置为null。正好与左连接相反。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzNTkxOTAz_size_16_color_FFFFFF_t_70 2][]

例如：

SELECT
    	student.st_id,
    	student.st_name,
    	score.subject,
    	score.grade 
    FROM
    	student
    	RIGHT JOIN score ON student.st_id = score.st_id;

执行结果：

![20201009164607689.png][]

对于关联字段st\_id，展示右表所有的记录。由于左表缺少1005，即执行结果的最后一行的st\_id与st\_name的值被置为null。

--------------------

### 五、全连接 ###

按照关联字段取出两个表中的记录，保留左右两表中所有的记录，不满足连接条件的均被置为null。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzNTkxOTAz_size_16_color_FFFFFF_t_70 3][]

当然，oracle可以直接使用full join来完成全连接，而mysql则需要借助union。

例如：

select student.st_id,student.st_name,score.subject,score.grade 
    from student left join score on student.st_id=score.st_id
    union
    select student.st_id,student.st_name,score.subject,score.grade 
    from student right join score on student.st_id=score.st_id;

执行结果：

![20201009174056253.png][]

可以看到，已经取出了两个表中的所有数据。

--------------------

### 六、获取交集以外的部分 ###

按照关联字段取出两个表中的记录，保留交集以外的数据。去除交集相当于全连接-内连接。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzNTkxOTAz_size_16_color_FFFFFF_t_70 4][]

例如：

select student.st_id,student.st_name,score.subject,score.grade 
    from student left join score on student.st_id=score.st_id 
    where score.st_id is null
    union
    select student.st_id,student.st_name,score.subject,score.grade 
    from student right join score on student.st_id=score.st_id 
    where student.st_id is null;

执行结果：

![20201010112442615.png][]

第一条数据属于student表，第二条数据属于score表。

[20201009153123583.png]: https://img-blog.csdnimg.cn/20201009153123583.png
[20201009153156935.png]: /images/20221123/1e56510a27354692ae32a384e87ac95e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzNTkxOTAz_size_16_color_FFFFFF_t_70]: /images/20221123/92e8d361ab2e49d28db1be37fc1152ae.png
[20201009160357263.png]: /images/20221123/0c3f98dc3d32483db1664dd9ad614036.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzNTkxOTAz_size_16_color_FFFFFF_t_70 1]: /images/20221123/86d605ee09224998a47ee7e12a5f4520.png
[20201009163907699.png]: /images/20221123/4fe0f4e22b9d49b6b433886abac141b0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzNTkxOTAz_size_16_color_FFFFFF_t_70 2]: /images/20221123/4ce99a5abf3b4b0ba7575a661edb7afd.png
[20201009164607689.png]: /images/20221123/97853207542c425bbdd40d8711ea497a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzNTkxOTAz_size_16_color_FFFFFF_t_70 3]: /images/20221123/04a897d925e1435d9de74d92f7a1237e.png
[20201009174056253.png]: /images/20221123/598f20b6c4754eba8cbf26a4a8cbef9d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzNTkxOTAz_size_16_color_FFFFFF_t_70 4]: /images/20221123/565c354e0030494ea16fd14545bdfcf2.png
[20201010112442615.png]: /images/20221123/c0527fb8cb1444b98e0158aee139f242.png