5.3.3 连接查询

在实际应用中，查询所涉及的数据经常存在于多个表中，这时就涉及两个或两个以上表的查询。当一个查询同时涉及连接两个以上的表时，称为连接查询。它是关系数据库中最主要的查询。连接查询在FROM子句中要写出所有有关的表名，在SELECT和WHERE子句中可引用任意有关表的属性名。当不同的表有相同的列名时，为了区分，要在列名前加注表名（表名.属性名）。主要的连接查询有等值连接和非等值连接、自然连接、自身连接（同一个表的连接）、多元复合条件连接、外连接和内连接。

1. 等值连接与非等值连接

连接查询中用于连接两个表的条件称为连接条件或连接谓词。其一般格式为

[＜表1＞.]＜列名1＞ ＜比较运算符＞ [＜表2＞.]＜列名2＞

连接条件中进行连接运算的两个列名必须是同类型的，它的名称可以不同，但必须是可比较的数据类型。当连接条件中比较的两个列名相同时，必须在其列名前加上所属表的名字和一个圆点（.）以示区别。表的连接除了可以用=外，还可以用比较运算符＜＞、＞、＞=、＜、＜=，以及BETWEEN、LIKE、IN等谓词。当比较运算符为=时，称为等值连接，其他的称为非等值连接。

从概念上讲，数据库管理系统执行连接操作的过程是：首先在表1中找到第一个元组，然后从头开始扫描表2，逐一查找满足连接条件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。表2全部查找完后，再查找表1中的第二个元组，然后再从头开始扫描表2，逐一查找满足连接条件的元组，找到后就将表1中的第二个元组与该元组拼接起来，形成结果表中一个元组。重复上述操作，直到表1中的全部元组都处理完毕为止。在对表进行连接时，最常用的连接条件是等值连接，也就是使两个表中对应列相等所进行的连接。通常一个列是所在表的主码（关键字），另一列是所在表的主码或外码（外关键字）。只有这样的等值连接才有实际意义。

【例5-32】查询所有学生所选的课程信息，写出相关语句。

解：SELECT student.*, enroll.*

FROM student,enroll

WHERE student.sno=enroll.sno /*将enroll和student表中同一学生的元组连接起来*/

结果如图5-31所示。

学生信息在student表中，学生选课信息在enroll表中，这两个表之间的联系通过公共属性sno来实现。

在例5-32中，SELECT子句和WHERE子句中的属性名前都加上了表名前缀，因为在这两个表中都有sno这个属性，这是为了避免混淆。如果属性名在参加连接的各表中是唯一的，则可以省略表名前缀。

例5-32是一个非常典型的等值连接，所有的属性均被列出，包括重复的属性。如果在等值连接中去掉目标列中的重复列，则为自然连接。将例5-32改用自然连接完成，语句如下：

SELECT student.sno,sname,sex,age,department,bplace, cno,grade

FROM student,enroll

WHERE student.sno=enroll.sno

这里，由于sname、sex、age、department、bplace、cno、grade在这两个表中是唯一的，所以引用时可以去掉表名前缀。而sno在这两个表中都出现了，因此引用时必须加上表名前缀。

在例5-32中，如果不带连接谓词，则所做的操作就是两个表的笛卡儿积，即两个表中元组的交叉乘积，其连接结果是一些没有意义的元组，所以这种运算实际很少使用。

等值连接时要注明该属性所在的表，即用“表名.属性名”表示。在用等值连接的条件外，还可根据需要加一些筛选条件，它可以从连接后生成的中间表中选择出所需的行。筛选条件可以是由比较运算符连接两个数值、字符或日期表达式的比较式，也可以是由这些比较式通过逻辑运算符连接的逻辑表达式，这样的连接方式称为复合条件连接。这种方式不是完全孤立使用的，它常常与其他连接配合使用。

【例5-33】找出成绩在90分以上的学生，并列出学号、姓名和成绩，写出相关语句。

解：SELECT student.sno,sname, grade

FROM student,enroll WHERE student.sno=enroll.sno AND grade＞=90

结果如图5-32所示。

【例5-34】查询所有学生所选课程的成绩，并列出课程名、学生学号和姓名，写出相关语句。

解：SELECT student.sno,sname,cname,grade

FROM student,course,enroll

WHERE student.sno=enroll.sno

AND enroll.cno=course.cno

结果如图5-33所示。

在例5-33和例5-34中，WHERE子句中有多个连接条件，这样的查询称为复合条件连接。每条语句并不是完全孤立的，复合条件连接可以用于所有的连接查询语句中。

2. 自身连接

连接操作不仅可以在多个表之间进行，也可以是一个表与其自己进行连接，称为表的自身连接。语句书写时可用AS给表进行重命名。AS用于FROM子句中，可用于给表重命名。

【例5-35】查询与胡峰在同一个系学习的学生，写出相关语句。

解：SELECT s1.sno,s1.sname,s1.department

FROM student s1,student s2

WHERE s1.department=s2.department AND s2.sname= '胡峰'

结果如图5-34所示。

我们看到显示结果中仍然有胡峰同学的名字，若不要显示他的名字，则可将查询语句改为

SELECT s1.sno, s1.sname, s1.department

FROM student s1,student s2

WHERE s1.department=s2.department AND s2.sname='胡峰'

AND s1.sname＜＞'胡峰'

3. 外连接

在通常的连接操作中，只有满足连接条件的元组才能作为结果输出，而外连接在正常的连接操作中应将在正常连接时舍弃的元组也显示出来，并在新增加的属性上添加空值NULL。外连接操作以指定表为连接主体，将主体表中不满足连接条件的元组一并输出。

外连接可以避免连接时数据的丢失，还可以加快查询速度。外连接有全外连接（FULL OUTER JOIN）、左外连接（LEFT OUTER JOIN）和右外连接（RIGHT OUTER JOIN）。

全外连接在结果表中保留左右两关系的所有元组。左外连接在结果表中保留左关系的所有元组。右外连接在结果表中保留右关系的所有元组。

【例5-36】查询90分以上的学生信息，未满90分的学生信息也要列出，写出相关语句。

解：SELECT student.sno,sname,grade FROM student

LEFT OUTER JOIN enroll ON

student.sno=enroll.sno AND grade＞90

结果如图5-35所示。

例5-36中，不管student表与enroll表中的sno列是否匹配，LEFT OUTER JOIN均会在结果列中包含student表中的所有元组，即将这个结果中显示90分以上的学生信息一并列出，不满90分的学生列出学生的基本信息，在成绩栏显示空值NULL。

如果只想看到90分以下的具体分数，而不想看到具体是哪个学生的分数，可以用右外连接，用下列语句实现。

SELECT student.sno,sname,grade FROM student

RIGHT OUTER JOIN enroll ON

student.sno=enroll.sno and grade＞90

结果如图5-36所示。

4. 内连接

内连接也称为连接，是最早的一种连接。内连接只返回结果集中所有相匹配的数据，而舍弃不匹配的数据，也就是说，在这种查询中，DBMS只返回来自源表中的相关的行，即查询的结果表包含的两源表行必须满足ON子句中的搜索条件。作为对照，如果在源表中的行在另一表中没有对应（相关）的行，则该行就被过滤掉，不会包括在结果表中。因为内连接是从结果表中删除其他被连接表中没有匹配行的所有行，所以内连接可能会丢失信息。这与WHERE子句的连接方式是相同的。

内连接的语句格式如下：

SELECT ＜目标列表达式＞

FROM ＜表名或查询结果＞[INNER] JOIN ＜表名或查询结果＞ ON ＜连接条件＞

WHERE ＜限定条件＞

【例5-37】选择籍贯为山东学生的全部成绩，用内连接完成，写出相关语句。

解：SELECT s.*,e.sno,e.cno,e.grade

FROM student AS s INNER JOIN enroll AS e

ON s.sno=e.sno WHERE s.bplace='山东

结果如图5-37所示。

也可以用之前介绍的连接方式完成，语句如下：

SELECT s.*,e.sno, e.cno,e.grade

FROM student AS s ,enroll AS e

where s.sno=e.sno AND s.bplace='山东'

【例5-38】从student表中查询计算机系所有学生的学号、姓名和考试总成绩，并按照考试总成绩降序进行排序，写出相关语句。

解：SELECT sname,b.zcj

FROM student a INNER JOIN

(SELECT sno,sum(grade) zcj FROM enroll GROUP BY sno)

AS b on b.sno=a.sno WHERE department='计算机' ORDER BY zcj DESC

结果如图5-38所示。

也可以用之前介绍的WHERE子句的方式完成，语句如下：

SELECT sname,b.zcj

FROM student a ,

(SELECT sno,sum(grade) zcj FROM enroll GROUP BY sno) AS b WHERE b.sno=a.sno AND department='计算机' ORDER BY zcj DESC

当然，使用INNER JOIN也可以实现多表的内连接，但是INNER JOIN一次只能连接两个表，要连接多个表，必须进行多次连接。

【例5-39】查询所有学生所选课程的成绩，列出课程名、学生学号和姓名，写出相关语句。

解：SELECT student.sno,sname, cname,grade

FROM student INNER JOIN enroll ON student.sno=enroll.sno

INNER JOIN course ON enroll.cno=course.cno

例5-39与例5-34完全相同，只是在例5-34中采用WHERE子句的方式实现，这里采用INNER JOIN的方式实现。

可以看到，ANSI SQL内连接语法是通过INNER JOIN关联两个表，使用ON子句来定义等值条件的，并通过WHERE子句来定义查询条件，这种方式也是目前T-SQL普遍使用的方式。