目录

1. 多表查询的分类

1.1 角度一来讲:分为“ 等值连接” 和“非等值连接”;

1.2 角度二来讲:分为“自连接” 和 “非自连接”;

1.3 角度三来讲:分为“内连接” 和 “外连接”;

2. 等值连接与非等值连接

2.1 等值连接

2.2 非等值连接

3. 自连接与非自连接

3.1 自连接

3.2 非自连接

4. 内连接与外连接

4.1 内连接

4.2 外连接的分类

4.2.1 左外连接

4.2.2 右外连接

4.2.3 全外连接(也叫满外连接)


1. 多表查询的分类

关于数据库的多表查询,我们可以从三个方面对其进行划分,分类方式不分主次,只是它们的分类方式不同。

1.1 角度一来讲:分为“ 等值连接” 和“非等值连接”;

1.2 角度二来讲:分为“自连接” 和 “非自连接”;

1.3 角度三来讲:分为“内连接” 和 “外连接”;

2. 等值连接与非等值连接

2.1 等值连接

等值连接:见名知意,即为通过表A中的某个字段或者某些字段与表B,表C…等等中的表的一些字段进行等值判断查询。举例说明如下:

如上图为员工表 ,表名为 employees

如上图为部门表,表名为 departments

可以看到,两个表中有相同字段 department_id

问题一:假设我们要查询每个员工所在的部门名称,该如何查?

显然单从员工表employees中查是查不到的,但员工表中却存在每个员工所在的部门编号department_id,所以我们可以通过部门表号查询部门名称,如下图片中的查询语句:

从sql 语句中可以看到,查询条件为 employee表中的departmend_id = departmend表中的department_id,用等号进行匹配,即为等值连接。

此外有一点需要特别注意,在我写的sql语句中,对员工表起了别名为 e ,对部门表起了别名为 d,所以,在后续条件过过滤和查询时,均需要使用表的别名来替代表名,否则会出错,因为一旦你给表起了别名,那么数据库就会用你起的别名来覆盖原本的表名,这时如果你已经起了别名,却仍使用原来的表名,则数据库会报错。

如下图所示,当我给我的表起了别名但不用别名的时候,查询就会报错;错误信息显示为:列 employees_employee_id 不存在。

所以一定要记住,一旦你给表起了别名,就需要使用表的别名,不能再使用原来的表名。

2.2 非等值连接

其实对比等值连接,非等值连接也很好理解,既然等值连接的筛选条件是通过字段相等来筛选的,那么非等值连接的筛选方式就不是通过字段相等来筛选的。

这里来说明下面这张表 job_grade 即为员工薪资水平表

现假设我们想知道各个员工的薪资水平等级是多少,该如何查询呢?

可以看到,在薪资水平表中,每个薪资水平等级都是一个范围,这里我们如果继续使用等值连接明显是无法满足我们的要求的,所以就用到了我们的非等值连接。

如下图所示:

可以看出这是我们的删选条件是使用的between…and… 通过一个范围来判断每个员工的薪资等级是多少,就可以达到我们的目的。

3. 自连接与非自连接

3.1 自连接

(1)自连接:其实自连接很好理解,举个最简单的例子,自恋,就是自己喜欢自己,同理,自连接就是自己与自己进行相连,口头表述还是不好,不如上图演示,我们还用上面的员工表。

问题如下:现假设我们要查询每个员工的上司信息,该如何查询?

思路:我们可以这样想,员工的上司,他也是员工,所以它一定也在员工表中,那么他们该怎样产生关联呢?相信各位已经想到了吧。那就是想要查询某个员工的上司的话,我们只需要让他的上司id,即manager_id = 某位员工的id, 筛选条件为 employee.manager_id = employee_id,通过这样查询,就可以得到我们想要的结果了。

那么问题又来了,同一张表,我们该怎么样实现多表查询呢?

很简单,我们只需要把一张表当成两张表就好了呀。

如下图所示:

我们可以把第一张员工表当成员工表,起别名为 e,把第二张表当成上司表,起别名为 m,此时只需要再加上我们刚才分析的删选条件,即可达到我们的目的。

3.2 非自连接

(2)非自连接:理解了自连接,非自连接就很简单明了了啊,自连接是自己与自己相连,非自连接就是自己不与自己相连,那么不与自己相连,那肯定就是和别的表进行相连了啊。

问题如下:假设我们要查询每个员工所在的部门名称,该如何查?

细心的小伙伴可能已经发现了,这个问题其实与上面从角度一来讲等值连接是同一个问题,那么在此就不用我再多说了吧,不太懂的小伙伴可以去重新看一遍上面我展示的等值连接,再细细体会体会。

4. 内连接与外连接

4.1 内连接

(1)内连接:内连接的意思就是把两个表有关联的部分都取出来,不分主表和次表;仍然拿上面员工表和部门表来举例,如果我们要查询某个员工所在的部门,就可以使用内连接来进行查询。

但此时我们需要注意,在员工表中,有的员工是没有部门的,而在部门表中,有的部门是没有员工的,如果我们使用内连接进行查询的话,没有部门的员工和没有员工的部门的相关信息,我们都是查不到的。

用图来说明,如下,内连接查询,我们把一查询到表A和表B的交集,其余剩下的数据我们都查不到,相比这样理解,大家应该更容易明白一些了吧。

那么言归正传,内连接我们应该怎么查询呢?语法又是什么呢?

这里我主要讲述SQL99语法,因为大家和企业极大多数都是用的是SQL99语法。

在SQL99语法中,给出了怎样来进行内连接查询。

内连接查询的关键字为 表A JOIN 表B ON 查询条件

还是和上面同样的问题,假设我们要查询每个员工所在的部门名称,该如何查?

看下图语句:

这里我只是展示了取表A和表B的交集,

其实我们可以在 ON 条件之后继续 JOIN 表C ON 查询条件 ,继续取出 表B和表C 的交集,这里我就不一一展示了,有兴趣的小伙伴何以自己动手练习一下。

4.2 外连接的分类

外连接的话,它其实又分为三种

1. 左外连接

2. 右外连接

3.全外连接

这里我先解释一下,为什么会出现左外连接,右外连接这种状况。还是拿我们的员工表和部门表举例,我们知道,一个员工,它可以有归属部门,那么同样也可以没有归属部门;一个部门,它既可以有很多员工,也可以一个员工都没有,空部门。

基于这种情况的出现,就会出现左外连接右外连接以及全外连接(满外连接);我们想一想,当我们查询某个员工属于某个部门的时候,肯定要在让他在员工表中的 department_id = 部门表中的 department_id,才可以查到,那么回到刚才的问题,如果一个员工没有部门,他在数据库中的 department_id = null,还能查到他吗?如果一个部门没有员工,那么该部门下的所有 employee_id = null,还能查到吗?显然是不能的,因此就会造成左外连接右外连接的这种情况,下面会针对这些问题进行分析。

4.2.1 左外连接

先看左外连接

问题:查询各个员工所在的部门名称,并且查询出没有部门的员工信息

仍拿刚才的员工表和部门表举例,我们刚才内连接取了表A和表B的交集,那么做外连接的话,就是既要查询到表A和表B的交集(即有部门的员工的信息),又要取 员工表中没有部门的员工信息。使用图形来表示如下:

那么左外连接的sql语句又该怎么写呢?

其实很简单,我们只需要在原本内连接的sql语句上做一点改动,内连接的语法是 表A JOIN 表B ON 条件,左外连接的语法则为 表ALEFT JOIN 表B ON 条件,在JOIN 左侧加上左外连接关键词LEFT即可,如下图中SQL语句所示。

4.2.2 右外连接

右外连接:明白了左外连接,右外连接就很好理解了嘛

问题:查询各个员工所在的部门名称,并且查询出没有员工的部门信息。

用图来表示如下:

左外连接的语法各位明白了,比葫芦画瓢,右外连接的其实也很好想的对吧,

就是把 左外连接中的 LEFT 换成英文单词‘右’RIGHT就可以了。左外连接右外连接sql语句在上图已显示,这里不再重复展示。

4.2.3 全外连接(也叫满外连接)

接下来看 全外连接:左外连接和右外连接明白了之后,我们再来看全外连接,其实也就很简单了,就是取并集呗,既要员工部门相关的信息,又要没有部门员工的相关信息,还要没有员工的相关部门信息。用图片展示如下所示:

在表示如何表示全外连接之前,我先来考一考各位,上图有表A和表B两张表,那么他们一共有几种情况呢?

答案是7种,如下所示:

我们将这7种情况命名为 左上,左中,左下,右上,右中,右下,中

然后我们来看,左上其实就是对应我们的左外连接;

右上就是对应我们的右外连接;

中就是对应我们的内连接;

那么剩余的四种情况我们该如何进行演示呢?

先看左中,左中就是在坐上的前提下,不要中间相同的部分,那如果从这个角度老考虑的话,我们是不是只要在原本左外连接语句的基础上,加上一个筛选条件 where 表.字段 is null就可以了啊。

如下所示:

执行Sql语句之后,可以看到,我查询到了一条记录,employee_id 为178号的员工没有归属部门,是不是就达到了我们想要的结果了呢。

同理,如果我们想要得到右中的结果,只需要在右外连接的基础上加上一个过滤条件 where 表.字段 is null就可以了。

如下所示:

从查询结果可以看出,查到的部门均是没有员工的部门。

这个时候我们再看看全外连接,实现全外连接之前,需要先说明两个数据库关键字 UNION和UNION ALL,这两个个关键字均使用来实现全外连接的,那他们有什么不一样呢?

先看UNION,UNION就是直接对两个表取并集,会去除重复的字段;

而UNION ALL也是直接对两个表取并集,但不会去除重复的字段;

从效率上来讲,因为UNION需要去除重复字段,在数据量小的时候还好,一旦数据量较大达到百万甚至千万级别时,因为UNION会去除重复字段需要耗费大量时间,所以效率会低一些,而反观UNION ALL ,因为他不会去除重复字段,所以效率会比UNION高。因此在平常使用时以及开发过程中,推荐使用UNION ALL,少用UNION。

学会了这两个关键字之后,我们在来实现并集是不是就简单多了啊。

这个时候想要实现左下的并集,只需要将实现左上的SQL语句 UNION ALL 实现右中的SQL语句即可,或者将实现左中的SQL语句 UNION ALL 右上的SQL语句;这里我只举一个例子就是用左上和右中,右下以及剩余的情况在看懂了我举得例子之后,相比自己动手也可以完成。

SQL语句例子如下图所展示:

从表中查询结果可以看到,我们既查询到了没有部门的员工信息,也查询到了没有员工的部门信息,以及员工与部门与之对应的员工信息。到此为止,我们所有的目标都已经达到。

希望通过这篇文章,各位可以更加清晰地了解多表查询操作,写此文章也是为了巩固我自己对多表查询的理解,如哪处有错误,还望各位进行指正,让我们一起努力,成功高级架构师!