面试官问：你是怎么使用多表查询的？快速让你掌握多表查询的秘诀

MySQL基础（六）-----子查询和连接查询

子查询简而言之就是在一个查询里面再嵌套一个或者多个查询语句；

连接查询指的就是将多个表或者单个表连接在一起进行查询

本文涉及的的表还是来自MySQL基础（四）-----简单查询和带附带条件的查询_Spring@W的博客-CSDN博客不知道的可以去查看一下

一、子查询

1、简单的多表查询

目前为止，我们的查询都是作用于单个表的，倘若我们需要从多个表中查询数据。那我们应该如何处理？样例问题，我要查询名字为‘韩信’同学的成绩应该如何查询？

最笨的处理方法：

• 先从学生信息表查询到韩信的学生id；
• 再根据学生id去查询成绩表；

实际效果：

但是如果我们想查询更复杂的情况呢？接下来介绍的方法就可以替我们解决

2、标量子查询

看上述的查询语句，分析可以得到，第二个查询语句的条件其实是用到了第一个语句的结果。为了书写简单，我们把二者合二为一。

sql复制代码SELECT * from student_score WHERE number = (SELECT id from student WHERE `name`='韩信');
           

可以发现结果和分开查询的结果是一致的。这是为什么呢？

• 我们把第二条查询语句用小括号括起来，并作为一个操作数放到了第一条查询语句的搜索条件处，这样就起到了合并两条查询语句的作用；
• 小括号中的查询语句也被称为子查询或者内层查询， 而使用子查询的结果作为搜索条件的查询称为外层查询；
• 如果在一个查询语句中需要用到更多的表，那么可以在一个子查询中继续嵌套另一个子查询，在执行查询语句时，将按照从内到外的顺序依次执行这些查询。
• 所有的子查询都必须用小括号扩起来，否则是违法的！

在上述样例中，子查询的结果只有一个值（也就是韩信的学号），这种子查询被称为标量子查询。

• 因为标量子查询单纯地代表一个值，所以它可以作为表达式的操作数来参与运算；
• 标量子查询除了用在外层查询的搜索条件中，还可以放在查询列表处。（样例如下）

• 由于标量子查询单纯地代表一个值，因此可以与其他操作数通过运算符连接起来，组成更复杂的表达式。我们常用于where子句后面。样例如下

3、列子查询

如果我们要查询MySQL技术专业学生的成绩，我们必须先从学生信息表student中根据专业名称找到学生的id，然后再根据id去查成绩表student_score获得对应的成绩。 分析： 方法一：

方法二：

sql复制代码SELECT * FROM student_score WHERE number IN(SELECT id from student WHERE major='MySQL技术');
           

方法一中的搜索条件用到了第一条查询语句的查询结果，我们在标量子查询的基础上，就可以把第一条语句作为内层查询，第二条作为外层查询，从而得到我们的方法二的语句。

在方法二的语句中，子查询的结果集并不是一个单独的值，而是一个列（id列,它包含2个值，分别是20230103,20230104），所以这个子查询也被称为列子查询。

4、行子查询

有列子查询，当然肯定会有行子查询。只要子查询的结果集中最多只包含一条记录，而且这条记录中有超过一个列的数据（如果该条记录只包含一个列的话，该子查询就成了标量子查询），那么这个子查询就可以称为行子查询。样例如下：

编辑

再子查询中限定最多只能返回一条记录，该子查询就可以被看作为一个行子查询

注意：在想要得到标量子查询或者行子查询，但又不能保证子查询的结果集只有一条记录时，需要使用limit 1子句来限制结果集中记录的数量

之前的篇幅中我们介绍过一个操作数都是单一的值，。但是，在上述例子中的子查询执行后产生的结果集是一个行（包含2个列），所以与其进行等值比较的另一个操作数也得包含2个值，在上述语句中的（number,subject）（注意这个地方必须要用小括号扩起来，否则会产生歧义）。它表达的语义就是，先获取到子查询中的执行结果，然后再执行外层查询，如果成绩表中记录的number等于子查询中的number列，并且subject列等于‘计算机科学与技术’，那么就将该记录加入到结果集。

5、表子查询

如果子查询的结果集中包含多行多列，那么这个子查询也可以称为表子查询

样例：

子查询执行之后的结果集中包含多行多列，因此可以被看作是一个表子查询。

6、EXISTS和NOT EXISTS子查询

有时外层查询并不关心子查询中的结果是什么，只关心子查询的结果集是不是为空集。可以用到EXISTS和NOT EXISTS这两个运算符。

样例：

因为子查询中学生表里面没有‘hahahahahha’专业的同学，所以返回结果为空，所以整个语句查询不到数据。

在包含[NOT] EXISTS子查询的语句中，由于我们只关心子查询的结果集是不是空集，而不关心具体结果是什么，所以子查询的查询列表里面写啥都可以，并不一定非得写‘*’；

7、不相关子查询和相关子查询

在之前的子查询中，子查询都可以独立运行并产生结果，之后再拿结果作为外层查询的条件去执行外层查询，这种子查询称为不相关子查询。

样例：

在样例中的子查询中只使用了学生表而没有使用成绩表，它可以独立运行并产生结果，所以这是一种典型的不相关子查询。

有时候，我们需要在子查询的语句中引用外层查询的列，这样的话子查询就不能当作一个独立的语句去执行，这种子查询被称为相关子查询。

样例：

sql复制代码SELECT id number,sn,`name`,id_number,major FROM student WHERE EXISTS (SELECT * FROM student_score WHERE student.id=student_score.number);
           

注意，当两个表中的列相同时，在子查询的where语句中书写时要区分开，可以在前面加上表名。直接写：相同的列名=相同的列名会造成二义性，会把MySQL服务器搞懵的，不知道这个列到底是哪个表的。正确的写法：表1.相同的列名=表2.相同的列名，即使表名不相同也可以这样写，这种显示地将列所属的表名书写出来的名称称为该列的全限定名。

8、对同一个表的子查询

不光可以对不同的表进行子查询，对同一个表也可以进行子查询。样例如下：

sql复制代码SELECT * FROM student_score WHERE `subject` = 'MySQL技术' AND score >(SELECT AVG(score) FROM student_score WHERE `subject` = 'MySQL技术');
           

二、连接查询

介绍连接查询之前，我们先来重新定义一下关系表。我们把学生表和成绩表合并起来，就起名为student_merge

这样我们查询数据起来很方便，但是它的劣势也是显而易见的。

• 浪费存储空间--我们每一次新加一门学科成绩时都要保存一次学生的信息，重复操作且还是重复存储；
• 维护困难--当修改某个学生的基本信息时，要修改的地方将会是所有包含该学生的记录，很容易导致信息的不一致，增大维护的难度。

所以为了尽可能的减少存储的冗余信息，降低维护难度，我们一开杀就把这个合并表分成了学生表和学生成绩表。但是这两张表之间有某种关系作为纽带，这里的某种关系指的就是两个表都拥有的学生id。

1、连接的概念

我们将表拆分为学生表和成绩表后，的确解决了数据冗余的问题，但是数据的查询却成为了一个问题。到目前为止，所有篇幅中介绍的查询语句中还无法把某个学生的所有信息查询出来。即使是子查询也不行，虽然子查询可以在一个查询语句中设计多个表，但是整个查询语句最终产生的结果集还是用来展示外层查询的结果，子查询的结果只是被当作中间结果来使用。

下面重新建两个测试表并插入数据，供连接查询使用。

sql复制代码CREATE TABLE t1(
	a1 int,
	b1 char(1)
);
CREATE TABLE t2(
	a2 int,
	b2 char(1)
);

INSERT INTO t1 VALUES(1,'a'),(2,'b'),(3,'c');
INSERT INTO t2 VALUES(2,'b'),(3,'c'),(4,'d');
           

目前我们已经创建了t1和t2两个表，并且往两个表中各加入了三条数据。

两个表连接的本质就是把一个表中记录与另一个表中的记录两两想呼喊组合，将组合后的记录加入到最终的结果集。把t1和t2表连接起来的过程如图所示

这个过程看起来就是把t1表的记录和t2表的记录连起来组成新的更大的记录，所以这个查询过程称为连接查询。

在没有任何过滤条件的情况下，两个表连接起来生成的结果集也被称为笛卡儿积。因为表t1中有3条记录，t2中有3条记录，所以这两个表连接后的笛卡儿积就有33=9条记录。

连接查询的语法：

sql复制代码	SELECT * FROM 表名1,表名2;
           

样例：

查询列表处的‘*’代表要读取from子句中的每个表的所有列。上面的语句其实和下面的这些写法是等价的。

• 写法一：这种写法是将t1、t2表中的列名都显式的写出来，也就是使用了列的全限定名；

• 写法二：由于t1、t2表中的列名并不重复，所以没有可能让服务器发懵的二义性，因此在查询列表上直接使用列名也是可以的；

• 写法三：这种写法的意思就是查询t1表的全部的列和t2表的全部的列。

2、连接查询的过滤条件

当表的数据多了后，笛卡儿积的结果是非常巨大的，所以我们必须要在进行连接查询的时候指定过滤条件。

连接查询中的过滤条件分为两种

• 涉及单表的条件：这种只涉及单表的过滤条件已经提到过很多次了，前面篇幅中也一直称为搜索条件，比如t1.a1>1只是针对t1表的过滤条件等；
• 涉及两表的条件：这种过滤条件我们之前没见过，比如t1.a1=t2.a2、t1.b1>t2.b2等，这些条件找那个涉及两个表

样例分析：

过滤条件：

• t1.a1 > 1
• t1.a1 = t2.a2
• t2.b2 < 'd'

查询过程分析：

• 首先确定第一个需要查询的表，这个表为驱动表。这里假设使用t1作为驱动表，那么就需要在t1表中查找满足t1.a1>1的记录。符合条件的有2条记录；
• 针对满足t1.a1>1条件的驱动表中的两条记录，都需要到t2表中查找匹配的记录（所谓匹配的记录，指的是符合过滤条件的记录）。因为是根据t1表中的记录去查找t2表中的记录，所以t2表也可以被称为被驱动表。符合条件1的记录有两条，所以要查t2表两次，查两次的条件分别为:a1=a2=2时，t1.a1=t2.a2 AND t2.b2<'d'；a1=a2=3时，t1.a1=t2.a2 AND t2.b2<'d'；

从上述的过程分析中可以得到，两表连接查询中，驱动表只需要查询一次，而被驱动表可能会被查询多次。

3、内连接和外连接

假设一个需求，我们要查询学生的基本新，还要查询学生的成绩信息。

这个需求就需要两表查询了。连接过程就是从学生表中取出记录，在成绩表里面去查找学生id相同的记录，过滤条件就是student.id=student.number。样例：

ini复制代码SELECT student.id number,sn,`name`,department,major,`subject`,score FROM student,student_score WHERE student.id=student_score.number;
           

从结果我们可以看到，除了20230105和20230106两个同学，其他的同学信息都展示出来了，这是因为这两位同学可能因为某些原因没有参加考试，所以没有成绩；

但是我们想要查看全部的记录，这个结果的本质就是：驱动表中的记录即使在被驱动表中没有匹配的记录，也需要加到结果集中；

因此我们有了内连接和外连接：

• 对于内连接的两个表，如果驱动表中的记录在被驱动表中找不到匹配的记录，则该记录不会加入到最后的结果集中。上面提到的连接都是内连接；
• 对于外连接的两个表，即使驱动表中的记录在被驱动表中没有匹配记录，也仍然需要加入到结果集中。

可是仍然存在问题，对于外连接来说，有时候我们并不想把驱动表的全部记录都加入到最后的结果集中。这个时候就需要用到on子句。

where子句和on子句过滤条件拥有不同的含义：

• where子句中的过滤条件--我们之前一直吧过滤条件放在where子句汇总，不论是内连接还是外连接，凡是不符合where子句中过滤条件的记录都不会被加入到最后的结果集；
• on子句中的过滤条件--对于外连接的驱动表的记录来说，如果无法在被驱动表中找到匹配on子句中过滤条件的记录，那么该记录仍然会被加入到结果集中，对应的被驱动表记录的各个字段使用NULL值填充

注意：这个on子句的特殊作用只有在外连接查询中才会得以体现，如果把on子句放到内连接中，MySQL会把它和where子句一样对待。内连接中的where子句和on子句是等价的。

3.1、连接语法

在MySQL中，根据选取的驱动表的不同，外连接可以细分为左（外）连接和右（外）连接两种。

• 左（外）连接的语法

模板：

sql复制代码SELECT * FROM 表1 LEFT [OUTER] JOIN 表2 ON 过滤条件 [WHERE 过滤条件];
           

其中，中括号里的outer单词是可以省略的。对于左（外）连接来说说，我们把放在LEFT [OUTER] JOIN左侧的表称为外表或者驱动表，右侧的表称为内表或者被驱动表。（表1是外表或者驱动表，表2是内表或者被驱动表）通常会将涉及量表的过滤条件放到on子句中（并非绝对的，具体怎么过滤根据用户个人选择）

注意：对于左（外）连接和右（外）连接来说，on子句中的过滤条件是不能省略的

解决需求：

sql复制代码SELECT student.id number,sn,`name`,department,major,`subject`,score FROM student LEFT JOIN student_score on student.id=student_score.number;
           

• 右（外）连接的语法

右（外）连接的语法和左（外）连接的语法是一样的，只是把left改成了right而已

sql复制代码SELECT * FROM 表1 RIGHT [OUTER] JOIN 表2 ON 过滤条件 [WHERE 过滤条件];
           

只不过驱动表是RIGHT [OUTER] JOIN右侧的表，被驱动表是左侧的表

3.2、内连接的语法

再次强调一下，内连接和外链接的根本区别就是在驱动表的记录不符合on子句中的过滤条件时，内连接不会把该驱动表的记录加入到最后的结果集；而外连接会。

其实最简单的内连接的语法就是直接把需要连接的多个表都放在from子句后面，除此之外，MySQL还提供了其他多种进行内连接查询的语法。

模板：

sql复制代码SELECT * FROM 表1 [INNER | CROSS] JOIN 表2 [ON 过滤条件] [WHERE 过滤条件];
           

以下的几个内连接写法都是等价的：

vbnet复制代码SELECT * FROM 表1,表2 [WHERE 过滤条件];
SELECT * FROM 表1 JOIN 表2 [ON 过滤条件] [WHERE 过滤条件];
SELECT * FROM 表1 INNER JOIN 表2 [ON 过滤条件] [WHERE 过滤条件];
SELECT * FROM 表1 CROSS JOIN 表2 [ON 过滤条件] [WHERE 过滤条件];
           

在这些写法里面，还是推荐inner join的形式书写内连接，正好和外连接的left join 和right join区分开了。不过由于在内连接中on子句和where子句是等价的，所以内连接中不要求强制写明on子句。

大家可以根据自己掌握程度，去试试所有的连接语法。

4、多表连接

只要你愿意，你可以连接任意数量的表。在创建一个t3表，做个示范

sql复制代码CREATE TABLE t3(
	a3 int,
	b3 char(1)
);

INSERT INTO t3 VALUES(3,'c'),(4,'d'),(5,'e');
           

样例：

sql复制代码SELECT * FROM t1 INNER JOIN t2 INNER JOIN t3 WHERE t1.a1=t2.a2 AND t1.a1=t3.a3;
           

注意，内连接的表位置可以互换，不会影响结果，但是外连接不行。内连接中驱动表和被驱动表可以互换，在外连接中驱动表和被驱动表不可轻易互换。

5、表的别名

表的别名和列的别名一样，都是用空白字符或者as隔开。在表名特别长的情况下，为表定义别名可以让语句更加清晰一些。表的别名可以用在order by 、group by 等子句上。

样例：

sql复制代码SELECT s1.id number,sn,`name`,department,major,`subject`,score FROM student s1 LEFT JOIN student_score s2 on s1.id=s2.number;
           

6、自连接

不光可以多个不同表之间连接，同一个表也可以进行连接，但是要注意一个点就是，操作同一个表连接时，要给表起别名，否则会报错。样例：

由于被连接的表其实源自同一个表，所以这种连接称为自连接。自连接也可以用于外连接也可以用于内连接，后面可以加上过滤条件。这里就不展示了，感兴趣的可以自己去尝试写一写，在评论区讨论一下。

7、连接查询与子查询的转换

有的查询需求既可以使用连接查询解决，也可以使用子查询解决。都是等价的。实际开发中请大家根据自己的习惯进行书写。

样例：

sql复制代码/**连接查询**/
SELECT s2.* FROM student s1 INNER JOIN student_score s2 WHERE s1.id=s2.number and s1.major='计算机科学与技术';
/**子查询**/
SELECT * FROM student_score WHERE number in(SELECT id number FROM student WHERE major='计算机科学与技术');
           

MySQL服务器正在内部可能会将子查询转换为连接查询来处理，当然也可能用到别的方式来处理。

MySQL基础（六）-----子查询和连接查询。就分享到这个地方，后续会更新MySQL基础（七）-----并集查询和数据的插入、删除、更新。

今天的分享就到此结束了，如果觉得对您有帮助，麻烦给个三连！