10 分钟, 带你彻底掌握 SQL 多表查询

分手后的思念是犯贱 2023-09-26 16:43 48阅读 0赞

### 1.前言 ###

多表查询，也称为多表连接查询；作为关系型数据库最主要的查询方式，在日常工作中被广泛使用

常见的多表查询操作包含：子查询、内连接、左外连接、右外连接、完全连接、交叉连接

本篇文章将利用一个实例逐一介绍这些操作

### 2. 准备 ###

以 Mysql 数据库为例，创建两张数据表：

student - 学生表

record - 选课记录表

其中，学生表 id 字段对应选课记录表中的 student\_id 字段

Sql 如下：

# 学生表：student
    create table student
    (
        id   int          not null
            primary key,
        name varchar(255) null,
        age  int          null
    )
        comment '学生表';
    
    # 选课记录表：record
    create table record
    (
        id         int          not null
            primary key,
        name       varchar(255) not null,
        student_id int          not null,
        time       datetime     null
    )
        comment '选课记录';

然后，向 2 张表中插入一些数据

# 学生表数据
    1,张三,18
    2,李四,23
    3,王五,30
    4,马六,35
    5,孙七,40
    6,朱八,19
    7,黄九,53
    
    # 记录表数据
    2021001,语文,1,2021-01-18 15:32:47
    2021002,数学,2,2021-01-18 15:33:41
    2021003,英语,3,2021-01-18 15:34:01
    2021004,物理,4,2021-01-18 15:34:33
    2021005,体育,5,2021-01-18 15:34:47
    2021006,化学,8,2021-01-18 15:35:12
    2021007,生物,9,2021-01-18 15:35:39
    2021008,音乐,10,2021-01-18 15:36:00

### 3.1 子查询 ###

子查询，又称之为内查询，是一种嵌套在其他 Sql 查询的 Where 子句中的查询

一般用于对查询结果的进一步限制，返回所需要的数据；子查询可以用在 SELECT、INSERT、UPDATE 和 DELETE 语句中

这里以 SELECT 语句为例，在两张表中使用子查询，筛选出满足条件的记录

# 子查询
    select * from student where id in (select student_id from record where student_id<=3)

查询结果如下：

# 子查询的结果
    1,张三,18
    2,李四,23
    3,王五,30)

需要注意的是，子查询必须包含在圆括号内，并且不能使用 ORDER BY 进行排序

### 3.2 内连接 ###

内连接是通过关键字 inner join 连接两张表，只返回满足 on 条件的，两张表的交集数据

# 内连接
    select * from student s inner join record r on s.id=r.student_id;

查询结果如下：

# 内连接查询结果
    1,张三,18,2021001,语文,1,2021-01-18 15:32:47
    2,李四,23,2021002,数学,2,2021-01-18 15:33:41
    3,王五,30,2021003,英语,3,2021-01-18 15:34:01
    4,马六,35,2021004,物理,4,2021-01-18 15:34:33
    5,孙七,40,2021005,体育,5,2021-01-18 15:34:47

需要注意的是，如果内连接没有通过 on 关键字指定条件的话，查询结果和交叉连接查询结果一样，只是执行效率高于交叉连接

### 3.3 外连接 ###

外连接包含：

左外连接

右外连接

其中，

左外连接：使用关键字 left join，以左表为准，返回左表的所有数据，右表满足 on 条件的数据会全部显示，否则用 null 值去填充

右外连接：和左外连接相反。使用关键 right join，以右表为准，返回右表的所有数据，左表满足 on 条件的数据会全部显示，否则用 null 值去填充

首先，我们来看左连接的实例

# 左外连接
    select * from student s left join record r on s.id=r.student_id;

返回结果如下：

# 左外连接结果
    1,张三,18,2021001,语文,1,2021-01-18 15:32:47
    2,李四,23,2021002,数学,2,2021-01-18 15:33:41
    3,王五,30,2021003,英语,3,2021-01-18 15:34:01
    4,马六,35,2021004,物理,4,2021-01-18 15:34:33
    5,孙七,40,2021005,体育,5,2021-01-18 15:34:47
    6,朱八,19,NULL,NULL,NULL,NULL
    7,黄九,53,NULL,NULL,NULL,NULL

然后，我们再来看看右连接

# 右外连接
    select * from student s right join record r on s.id=r.student_id;

返回结果如下：

# 右外连接结果
    1,张三,18,2021001,语文,1,2021-01-18 15:32:47
    2,李四,23,2021002,数学,2,2021-01-18 15:33:41
    3,王五,30,2021003,英语,3,2021-01-18 15:34:01
    4,马六,35,2021004,物理,4,2021-01-18 15:34:33
    5,孙七,40,2021005,体育,5,2021-01-18 15:34:47
    NULL,NULL,NULL,2021006,化学,8,2021-01-18 15:35:12
    NULL,NULL,NULL,2021007,生物,9,2021-01-18 15:35:39
    NULL,NULL,NULL,2021008,音乐,10,2021-01-18 15:36:00

### 3.4 完全连接 ###

完全连接，是通过关键字 full join 连接两张表，返回左表和右表的所有数据，并使用 null 值填充缺失的数据

# 完全连接
    select * from student s full join record r on s.id = r.student_id;

需要注意的是，Mysql 并不支持完全连接，我们可以使用左连接 + union + 右连接的方式去模拟完全连接

select * from student left join record on student.id = record.student_id
    union
    select * from student right join record on student.id = record.student_id;

查询结果如下：

# 完全连接结果
    1,张三,18,2021001,语文,1,2021-01-18 15:32:47
    2,李四,23,2021002,数学,2,2021-01-18 15:33:41
    3,王五,30,2021003,英语,3,2021-01-18 15:34:01
    4,马六,35,2021004,物理,4,2021-01-18 15:34:33
    5,孙七,40,2021005,体育,5,2021-01-18 15:34:47
    6,朱八,19,NULL,NULL,NULL,NULL
    7,黄九,53,NULL,NULL,NULL,NULL
    NULL,NULL,NULL,2021006,化学,8,2021-01-18 15:35:12
    NULL,NULL,NULL,2021007,生物,9,2021-01-18 15:35:39
    NULL,NULL,NULL,2021008,音乐,10,2021-01-18 15:36:00

### 3.5 交叉连接 ###

交叉连接，又称之为笛卡尔积，使用关键字 cross join 连接两张表进行查询

如果不使用 where 加入限制条件，则返回两张表行数的乘积；如果加入限制条件，则返回满足条件表达式的数据并合成一行

以加入限制条件的交叉连接查询为例

# 交叉连接
    select * from xag.student as s cross join xag.record as r where s.id=r.student_id;

查询结果如下：

# 交叉连接结果
    1,张三,18,2021001,语文,1,2021-01-18 15:32:47
    2,李四,23,2021002,数学,2,2021-01-18 15:33:41
    3,王五,30,2021003,英语,3,2021-01-18 15:34:01
    4,马六,35,2021004,物理,4,2021-01-18 15:34:33
    5,孙七,40,2021005,体育,5,2021-01-18 15:34:47

需要注意的是，交叉连接查询如果带有限制条件，它会先生成两张表行数成绩生成查询结果集，然后再通过限制条件去过滤；因此，在数据量大的时候，查询速度会很慢

### 4. 最后 ###

相比单表查询，多表查询可以覆盖更多业务场景，大大提升我们的工作效率！实际工作当中，可以根据需要选择性的去使用！

如果你觉得文章还不错，请大家 点赞、分享、留言 下，因为这将是我持续输出更多优质文章的最强动力！

如果你不想一个人野蛮生长，找不到系统的资料，问题得不到帮助，坚持几天便放弃的感受的话，可以加入我们的QQ群：**746506216**，大家可以一起讨论交流，里面会有各种软件测试资料和技术交流。

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

### 资源分享 ###

**下方这份完整的软件测试视频学习教程已经上传CSDN官方认证的二维码，朋友们如果需要可以自行免费领取 `【保证100%免费】`**

![在这里插入图片描述][b9a9eea1ed5c42429c5e383b00ba6e0c.png_pic_center]

![在这里插入图片描述][d96b58a511f84668b59f3f620964da07.png_pic_center]

[b9a9eea1ed5c42429c5e383b00ba6e0c.png_pic_center]: https://img-blog.csdnimg.cn/b9a9eea1ed5c42429c5e383b00ba6e0c.png#pic_center
[d96b58a511f84668b59f3f620964da07.png_pic_center]: https://img-blog.csdnimg.cn/d96b58a511f84668b59f3f620964da07.png#pic_center