数据库多表联合查询——笛卡尔积、自然连接、左外连接、右外连接、全外连接等介绍

前言

面试的时候被面试官问到关于数据库多表查询的方式，其实以前都是学过的，但我当时只记得笛卡尔积（也没记起来这个术语，只知道是逗号分隔表名）和自然连接，有点惭愧。现在再重新整理并记录下来。

使用的表例子

来源于 Abraham Silberschatz 等著的《数据库系统概念（第 6 版）》中大学数据库的例子，可以参考这个链接：https://blog.csdn.net/m0_37961948/article/details/88426110

笛卡尔积

如果不限定任何条件，则表 1 中的每个元组和表 2 中的每个元组都会进行组合。这样的组合通常是没有意义的，一般会用 where 子句来限定查询的条件。比如我们想要查询每个教师的姓名和他教授的所有课程 ID，我们就可以这样写

select name,course_id
from instructor,teaches
where instructor.ID=teaches.ID

如果不加 where 子句进行限定，那么查询出来就会有老师对应到其他老师教授的课程的结果。

自然连接

自然连接只考虑在两个表中都出现的属性上取值相同的元组对。回到 instructor 和 teaches 关系的例子上，instructor 和 teaches 的自然连接计算中只考虑这样的元组对：来自 instructor 的元组和来自 teaches 的元组在共同属性 ID 上的取值相同。SQL 语句这样写：

select name,course_id
from instructor natural join teaches;

数据库会自动对两个表的共用属性进行匹配操作。这里要注意的是，如果限定好要查询的属性比如 name 和 course_id，那么这一种方法是等价于用笛卡尔积的方法的，但如果是查询所有属性，用*的话，两者之间会存在区别。使用笛卡尔积会把两个表的共有属性都显示一次，也就是说会有两列重复的 ID 列，而自然连接则会去重，只显示一列 ID，如图：

自然连接运算的结果是关系。从概念上讲，from 子句种的 instructor natural join teaches 表达式可以替换成执行该自然连接后所得到的关系，相当于一张虚表，然后在这张虚表上执行 select 语句。

在一个 SQL 查询的 from 子句中，可以用自然连接将多个关系结合在一起，如下所示：

select A1,A2,...,An
from r1 natural join r2 natural join ... natural join rm
where P;

更为一般地说，from 子句可以为如下形式：

from E1,E2,...,En

其中 Ei 可以是单个关系，也可以是一个包含自然连接的表达式。假设我们要查询老师的名字和他们教授课程的名称，用 SQL 语句可以这样写：

select name,title
from instructor natural join teaches, course
where teaches.course_id=course.course_id;

where 子句中的 teaches.course_id 指的是自然连接后的 course_id，之所以要写 teaches 是因为它最终来自于 teaches 表。

但是下面的 SQL 语句不会计算出相同的结果：

select name,title
from instructor natural join teaches natural join course;

注意 instructor 和 teaches 的自然连接包含属性(ID,name,dept_name,salary,course_id,sec_id)，而 course 表包含的属性是(course_id,title,dept_name,credits)。作为者二者自然连接的结果，需要元组既要在 dept_name 上相等，又要在 course_id 上取值相同。问题在于 course 表中的 dept_name 指的是课程是为哪个系开设的，前面的 dept_name 指的是老师所在的系，而老师可以在其他系开设课程，就比如我们大学里面上的政治课都是马克思主义学院的老师上的，但是这门课是为我们软件学院开设的。因此连用两个自然连接会导致查询不到老师在其他系开设的课程。

using

为了避免不必要的相等属性带来的错误，SQL 允许用户来指定需要列相等，关键词是 using，使用 using 时，直接写 join 而不是 natural join。我们可以用这样的 SQL 语句来代替上面的：

select name,title
from (instructor natural join course) join course using (course_id);

r1 join r2 using(A1,A2)与 r1,r2 的自然连接类似，但只要 r1.A1=r2.A1 且 r1.A2=r2.A2 就能成立，即使 r1,r2 都有名为 A3 的属性，也不需要 t1.A3=t2.A3 成立。

on

on 的用法和 using 类似，不过需要自己出表名。使用 on 时同样是直接写 join 而不是 natural join。区别是 on 中指定的匹配属性会出现两次，就和我们一开始介绍的使用笛卡尔积配合 where 一样，而 using 中指定的属性只会出现一次，和自然连接一样。on 的 SQL 语句如下：

select *
from student join takes student.ID=takes.ID;

这个语句的查询结果是学生和各自所修课程的对应关系，它和以下查询是等价的：

select *
from student,takes
where student.ID = takes.ID;

on 条件可以表示任何 SQL 为此，它可以表示比自然连接更为丰富的连接条件。但上面的例子中 on 可以用 where 来替换，这样看来，on 似乎是一个冗余的 SQL 特征。

但是引入 on 有两个优点。首先，对于下面马上要介绍的外连接来说，on 条件的表现和 where 是不同的；其次，如果在 on 子句中指定连接条件，在 where 子句中指定其他条件，这样的 SQL 语句更容易让人读懂。

外连接

假设我们要查询一个包含所有学生的列表，显示他们的 ID、name、dept_name 和 tot_cred，以及他们选修的课程。下面的查询好像检索出了所需的信息：

select *
from student natural join takes;

但它存在问题。假设有一些学生，他们没有选修任何课程。那么这些学生在 student 表中对应的元组和 takes 表中的任何元组配对，都不会满足自然连接的条件。也就是说他们会在查询结果中丢失。

外连接可以防止这种丢失情况，它会保留没有被匹配上的元组。

实际上有三种形式的外连接：

• 左外连接(left outer join)：保留出现在左外连接运算之前（左边）的关系中的元组
• 右外连接(left outer join)：保留出现在右外连接运算之后（右边）的关系中的元组
• 全外连接(full outer join)：对两个关系中的元组都保留

左外连接

要改进刚才的查询可以这样写：

select *
from student natural left outer join takes;

这样没有选修课程的学生也会出现在查询结果中，对于课程相关属性它的值均为 null。可以利用这个性质找出所有没有选修课程的学生

select *
from student natural left outer join takes
where course_id is null;

右外连接

右外连接和左外连接是对称的，上面的查询可以写成

select *
from takes natural right outer join student;

不过属性的出现顺序不同，前一种是先出现 student 表属性，后一种是先出现 takes 表属性

全外连接

全外连接是左外连接与右外连接的组合。在自然连接结果计算出来之后，左侧关系中不匹配右侧关系任何元组的元组被添上空值并加入结果中，右侧关系中不匹配左侧关系任何元组的元组也被添上空值并加到结果中

我们举一个稍微复杂点的例子来说明全外连接，假设我们要执行如下查询：“显示 Comp. Sci.系所有学生以及他们在 2009 年春季选修的的所有课程的列表，一门课都没选的学生和没有人选的课程都要显示出来。”我们可以这样写：

select *
from(select *
    from student
    where dept_name='Comp. Sci')
natural full outer join
(select *
from takes
where semester='Spring' and year=2009);

on 子句可以和外连接一起使用。下述查询与 student natural left outer join takes 的查询是相同的，只不过属性 ID 在结果中出现两次。

select *
from student left outer join takes on students.ID=takes.ID;

我们在前面提到 on 和 where 在外连接中的表现是不同的。原因是外连接只为那些对相应内连接结果没有贡献的元组补上空值并加入结果。on 条件是外连接声明的一部分，但 where 子句却不是。假设我们将上面采用 on 的查询换成采用 where，并使用 on 条件 true：

select *
from student left outer join takes on true
where students.ID=takes.ID;

第一种查询中 on 属于左外连接运算的整体，左外连接的性质决定了它会将没有选修课程课程的学生保留下来。第二种语句可以理解为先产生左外连接结果，之后再用 where 子句去对这个结果中的元组作匹配，此时没有选修课程的学生对应的元组中 takes.ID 为 null，不满足 students.ID=takes.ID 的要求，所以会丢弃他们。

连接类型和条件

为了把常规连接和外连接区别开来，SQL 中把常规连接也称作内连接。当连接子句使用 inner join 时说明是内连接，不过 inner 是可选的，直接写 join 默认就是 inner join。也就是说：

select *
from student join takes using(ID);

等价于

select *
from student inner join takes using(ID);

类似地，natural inner join 等价于 natural join