第8章连接查询与集合查询

1、第第8 8章章 连接查询与集合查询连接查询与集合查询在对数据库的查询过程中，有些时候检索一张表中的在对数据库的查询过程中，有些时候检索一张表中的数据记录往往不能满足开发人员或者客户的需要。例如，查数据记录往往不能满足开发人员或者客户的需要。例如，查询学生的选课成绩信息。而学生选课信息和课程成绩信息分询学生的选课成绩信息。而学生选课信息和课程成绩信息分别在两个不同的数据表中。其中，在课程信息表（别在两个不同的数据表中。其中，在课程信息表（T_curriculum）中包括课程的编号、课程的名字、课程的学）中包括课程的编号、课程的名字、课程的学分、课时以及授课的教师等学生选课信息，而学生的编号、分、

2、课时以及授课的教师等学生选课信息，而学生的编号、选课的课程编号以及课程成绩等信息在成绩信息表（选课的课程编号以及课程成绩等信息在成绩信息表（T_result）中，此时为了在查询的结果中显示学生选课信息）中，此时为了在查询的结果中显示学生选课信息和所选课程的相关信息，就需要同时检索课程信息表（和所选课程的相关信息，就需要同时检索课程信息表（ T_curriculum）和成绩信息表（）和成绩信息表（ T_result）。这就需要进行）。这就需要进行连接查询的操作。连接查询的操作。第第8 8章章 连接查询与集合查询连接查询与集合查询连接查询主要包括内连接、交叉连接、自连接、以及连接查询主要包括内连接

3、、交叉连接、自连接、以及外连接查询四种类型。本章主要介绍这四种连接查询的使用外连接查询四种类型。本章主要介绍这四种连接查询的使用方法，在本章的最后还将介绍三种主要的集合查询。方法，在本章的最后还将介绍三种主要的集合查询。等值连接和非等值连接查询的使用方法等值连接和非等值连接查询的使用方法使用使用ON子句建立相等连接子句建立相等连接使用使用USING子句建立相等连接子句建立相等连接交叉连接的使用方法交叉连接的使用方法自连接查询的使用方法自连接查询的使用方法外连接查询的使用方法外连接查询的使用方法集合查询的使用方法集合查询的使用方法8.1 8.1 内连接查询内连接查询很多时候，需要将几个表连接起来

4、并查询满足条件的很多时候，需要将几个表连接起来并查询满足条件的记录，这个时候就需要使用内连接。内连接包括等值连接和记录，这个时候就需要使用内连接。内连接包括等值连接和非等值连接。除了使用非等值连接。除了使用WHERE子句中的等号运算符（子句中的等号运算符（=）建立等值连接外，还可以使用建立等值连接外，还可以使用ON子句和子句和USING子句建立建子句建立建立等值连接。这一节将介绍几种内连接的查询方法。立等值连接。这一节将介绍几种内连接的查询方法。8.1.1 8.1.1 等值连接等值连接等值连接是指将指定的连接条件通过使用等号运算符等值连接是指将指定的连接条件通过使用等号运算符（=）连接起来，并

5、返回符合连接条件的数据行。其语法格）连接起来，并返回符合连接条件的数据行。其语法格式如下：式如下：SELECT 表名表名1.字段字段, 表名表名2.字段字段 .FROM 表名表名1,表名表名2WHERE 表名表名1.字段字段1=表名表名2.字段字段2其中，其中，SELECT 语句中表名语句中表名1.字段和表名字段和表名2.字段表示指字段表示指定数据表定数据表1和数据表和数据表2中要查询的列；中要查询的列；FROM 语句中表名语句中表名1和表名和表名2表示指定连接的数据表的名字；表示指定连接的数据表的名字；WHERE子句中表子句中表名名1.字段字段1=表名表名2.字段字段2表示用于指定连接条件的

6、列。这里的表示用于指定连接条件的列。这里的字段字段1中的列和字段中的列和字段2中的列必须是两个表之间相互关联的列中的列必须是两个表之间相互关联的列。8.1.2 8.1.2 非等值连接非等值连接非等值连接是指使用除等号运算符（非等值连接是指使用除等号运算符（=）以外的其他运）以外的其他运算符将指定条件连接起来而执行的查询操作。其他运算符包算符将指定条件连接起来而执行的查询操作。其他运算符包括、括、=（大于等于）、（大于等于）、（大于）、（大于）、（小于）、小于）、!=（不等于）等，还可以使用（不等于）等，还可以使用BETWEENAND运运算符。算符。8.1.3 8.1.3 使用使用ONON子句建

7、立相等连接子句建立相等连接在在SQL语句中除了语句中除了WHERE子句中使用等号运算符（子句中使用等号运算符（=）实现等值连接的操作之外，还可以使用）实现等值连接的操作之外，还可以使用ON子句建立相等子句建立相等连接条件。其语法规则如下：连接条件。其语法规则如下：SELECT 表名表名1.字段字段, 表名表名2.字段字段 .FROM 表名表名1 JOIN 表名表名2ON表名表名1.字段字段1=表名表名2.字段字段其中关键字其中关键字JOIN表示将表表示将表1和表和表2连接起来，连接起来，ON子句用子句用来指定连接条件的列。这里的字段来指定连接条件的列。这里的字段1中的列和字段中的列和字段2中的

8、列必中的列必须是两个表之间相互关联的列。须是两个表之间相互关联的列。8.1.4 8.1.4 使用使用USINGUSING子句建立相等连接子句建立相等连接在进行连接操作时，有时只希望将两张表中相互关联在进行连接操作时，有时只希望将两张表中相互关联的列建立一个等值连接。此时，可以使用的列建立一个等值连接。此时，可以使用USING子句建立相子句建立相等连接来简化使用等号运算符（等连接来简化使用等号运算符（=）建立的等值连接操作。）建立的等值连接操作。其语法规范如下：其语法规范如下：SELECT 表名表名1.字段字段, 表名表名2.字段字段 .FROM 表名表名1 JOIN 表名表名2USING (字

9、段字段1)其中关键字其中关键字JOIN表示将表表示将表1和表和表2连接起来，连接起来，USING子子句中使用括号将字段句中使用括号将字段1括起来，字段括起来，字段1就是两个表中建立等值就是两个表中建立等值连接相互关联的列。连接相互关联的列。8.2 8.2 交叉连接交叉连接交叉连接返回的结果是一个笛卡尔积。所谓笛卡尔积交叉连接返回的结果是一个笛卡尔积。所谓笛卡尔积实际就是两个集合相乘的结果。假设集合实际就是两个集合相乘的结果。假设集合A中有中有n个元素，个元素，集合集合B中有中有m个元素，如果最后返回的结果是个元素，如果最后返回的结果是n*m，那么这，那么这个结果就是集合个结果就是集合A和集合和

10、集合B的笛卡尔积。的笛卡尔积。8.3 8.3 自连接查询自连接查询前面讲到的连接都是在表与表之间进行的。连接查询前面讲到的连接都是在表与表之间进行的。连接查询除了可以在不同的表之间进行，也可以对同一张表实现连接除了可以在不同的表之间进行，也可以对同一张表实现连接操作，这种连接查询的方式称为自连接。所谓自连接，就是操作，这种连接查询的方式称为自连接。所谓自连接，就是指一个数据表与其自身进行连接。其语法规则如下：指一个数据表与其自身进行连接。其语法规则如下：SELECT A.字段字段, A.字段字段 .FROM 表名表名1 A,表名表名1 BWHERE A.字段字段=B.字段字段由于连接的是同一张

11、表，所以在由于连接的是同一张表，所以在FROM语句中需要为语句中需要为表定义不同的别名，这里为表表定义不同的别名，这里为表1分别定义了表的别名为分别定义了表的别名为A和和B。SELECT语句中可以使用语句中可以使用A.字段的形式也可以使用字段的形式也可以使用B.字段字段的形式查询需要的记录。这里的的形式查询需要的记录。这里的SELECT 语句中使用的是语句中使用的是A.字段的形式。字段的形式。8.3 8.3 自连接查询自连接查询例如，在课程信息表中选择学分数比操作系统的学分例如，在课程信息表中选择学分数比操作系统的学分数多的课程信息。数多的课程信息。SELECT C2.curID,C2.cur

12、Name,C2.creditFROM T_curriculum C1,T_curriculum C2WHERE C1.curName = 操作系统操作系统 AND C1.creditC2.credit8.4 8.4 外连接查询外连接查询在前面讲述的连接操作中，返回的结果都是满足连接在前面讲述的连接操作中，返回的结果都是满足连接条件的记录。有些时候，开发人员或者用户对于不满足连接条件的记录。有些时候，开发人员或者用户对于不满足连接条件的部分记录也感兴趣，这个时候就需要使用外连接查询条件的部分记录也感兴趣，这个时候就需要使用外连接查询。外连接查询不仅可以返回满足连接条件的记录，对于一个外连接查询不

13、仅可以返回满足连接条件的记录，对于一个数据表中在另一个数据表中不匹配的记录也可以返回。外连数据表中在另一个数据表中不匹配的记录也可以返回。外连接查询主要包括三种：左外连接、右外连接和全外连接。这接查询主要包括三种：左外连接、右外连接和全外连接。这一节将介绍这三种连接在不同数据库中的使用方法。一节将介绍这三种连接在不同数据库中的使用方法。8.4.1 8.4.1 左外连接左外连接左外连接中查询的结果中不仅将显示满足连接条件的左外连接中查询的结果中不仅将显示满足连接条件的记录，而且还包括左侧表中不满足查询条件的记录。下面分记录，而且还包括左侧表中不满足查询条件的记录。下面分别介绍在别介绍在Oracl

14、e数据库、数据库、MySQL数据库和数据库和Microsoft SQL Server数据库中左外连接的使用方法。数据库中左外连接的使用方法。1Oracle数据库数据库2MySQL和和Microsoft SQL Server数据库数据库8.4.1 8.4.1 左外连接左外连接1Oracle数据库数据库在在Oracle数据库中，可以使用加号运算符（数据库中，可以使用加号运算符（+）来表示）来表示左外连接。当该加号运算符（左外连接。当该加号运算符（+）出现在连接条件的左边时）出现在连接条件的左边时，就称之为左外连接。其语法格式如下：，就称之为左外连接。其语法格式如下：SELECT 表名表名1.字段字

15、段, 表名表名2.字段字段 .FROM 表名表名1,表名表名2WHERE 表名表名1.字段字段1(+)=表名表名2.字段字段2其中，其中，SELECT 语句中表名语句中表名1.字段和表名字段和表名2.字段表示指字段表示指定数据表定数据表1和数据表和数据表2中要查询的列；中要查询的列；FROM 语句中表名语句中表名1和表名和表名2表示指定连接的数据表的名字；表示指定连接的数据表的名字；WHERE子句中表子句中表名名1.字段字段1(+)=表名表名2.字段字段2表示左外连接。此时，表名表示左外连接。此时，表名1.字字段段1所在的列的值将会被全部查询出来。所在的列的值将会被全部查询出来。8.4.1 8

16、.4.1 左外连接左外连接2. MySQL和和Microsoft SQL Server数据库数据库在在MySQL数据库和数据库和Microsoft SQL Server数据库中可数据库中可以使用以使用LEFTOUTER JOIN关键字实现，其中关键字实现，其中OUTER关键关键字是可选的。使用字是可选的。使用LEFTOUTER JOIN关键字实现左外连关键字实现左外连接的语法规则如下：接的语法规则如下：SELECT 表名表名1.字段字段, 表名表名2.字段字段 .FROM 表名表名1 LEFT JOIN表名表名2ON 表名表名1.字段字段1=表名表名2.字段字段2这里使用这里使用LEFT JO

17、IN关键字代替关键字代替SQL语句中语句中FROM语语句里的逗号，使用句里的逗号，使用ON子句代替标准子句代替标准SQL语句中的语句中的WHERE子句，并将子句，并将SQL语句中表示左外连接的加号运算符（语句中表示左外连接的加号运算符（+）去）去除。除。 8.4.2 8.4.2 右外连接右外连接右外连接中查询的结果中不仅将显示满足连接条件的右外连接中查询的结果中不仅将显示满足连接条件的记录，而且还包括右侧表中不满足查询条件的记录。下面分记录，而且还包括右侧表中不满足查询条件的记录。下面分别介绍在别介绍在Oracle数据库、数据库、MySQL数据库和数据库和Microsoft SQL Serve

18、r数据库中右外连接的使用方法。数据库中右外连接的使用方法。1Oracle数据库数据库2MySQL和和Microsoft SQL Server数据库数据库8.4.2 8.4.2 右外连接右外连接1. Oracle数据库数据库在在Oracle数据库中，当该加号运算符（数据库中，当该加号运算符（+）出现在连接）出现在连接条件的右边时，就称之为右外连接。其语法格式如下：条件的右边时，就称之为右外连接。其语法格式如下：SELECT 表名表名1.字段字段, 表名表名2.字段字段 .FROM 表名表名1,表名表名2WHERE 表名表名1.字段字段1=表名表名2.字段字段2(+)其中，其中，SELECT 语句

19、中表名语句中表名1.字段和表名字段和表名2.字段表示指字段表示指定数据表定数据表1和数据表和数据表2中要查询的列；中要查询的列；FROM 语句中表名语句中表名1和表名和表名2表示指定连接的数据表的名字；表示指定连接的数据表的名字；WHERE子句中表子句中表名名1.字段字段1=表名表名2.字段字段2(+)表示左外连接。此时，表名表示左外连接。此时，表名2.字字段段2所在的列的值将会被全部查询出来。所在的列的值将会被全部查询出来。8.4.2 8.4.2 右外连接右外连接2. MySQL和和Microsoft SQL Server数据库数据库在在MySQL和和Microsoft SQL Server

20、数据库中可以使用数据库中可以使用RIGHT OUTER JOIN关键字实现，其中关键字实现，其中OUTER关键字是关键字是可选的。使用可选的。使用RIGHT OUTER JOIN关键字实现左外连接的关键字实现左外连接的语法规则如下：语法规则如下：SELECT 表名表名1.字段字段, 表名表名2.字段字段 .FROM 表名表名1 RIGHT JOIN表名表名2ON 表名表名1.字段字段1=表名表名2.字段字段2这里使用这里使用RIGHT JOIN关键字代替关键字代替SQL语句中语句中FROM语语句里的逗号，使用句里的逗号，使用ON子句代替子句代替SQL语句中的语句中的WHERE子句子句，并将标准

21、，并将标准SQL语句中表示右外连接的加号运算符（语句中表示右外连接的加号运算符（+）去）去除。除。8.4.2 8.4.2 全外连接全外连接全外连接中查询的结果中不仅将显示左侧表中不满足全外连接中查询的结果中不仅将显示左侧表中不满足连接条件的记录，而且还会显示右侧表中不满足查询条件的连接条件的记录，而且还会显示右侧表中不满足查询条件的记录。全外连接可以认为是左外连接与右外连接的合集（不记录。全外连接可以认为是左外连接与右外连接的合集（不包括重复行）。包括重复行）。全外连接可以使用全外连接可以使用FULL OUTER JOIN关键字实现，关键字实现，其中其中OUTER关键字是可选的。使用关键字是可

22、选的。使用FULL OUTER JOIN关键字实现左外连接的语法规则如下：关键字实现左外连接的语法规则如下：SELECT 表名表名1.字段字段, 表名表名2.字段字段 .FROM 表名表名1 FULL JOIN表名表名2ON 表名表名1.字段字段1=表名表名2.字段字段2这里使用这里使用FULL JOIN关键字代替关键字代替SQL语句中语句中FROM语语句里的逗号，使用句里的逗号，使用ON子句代替子句代替SQL语句中的语句中的WHERE子句子句8.5 8.5 集合查询集合查询在在SQL的连接查询语句中，还有一种查询方式就是结的连接查询语句中，还有一种查询方式就是结合查询。集合查询主要包括三种：

23、并操作、交操作和差操作合查询。集合查询主要包括三种：并操作、交操作和差操作。其中交操作和差操作并不是对目前主流的所有的数据库的。其中交操作和差操作并不是对目前主流的所有的数据库的适用。这一节中将分别介绍这三种集合的操作方法。适用。这一节中将分别介绍这三种集合的操作方法。8.5.1 8.5.1 并操作（并操作（UNIONUNION）执行并操作使用的关键字是执行并操作使用的关键字是UNION。并操作返回的结。并操作返回的结果集是包括了两个查询语句中查询出来的所有不同的行，不果集是包括了两个查询语句中查询出来的所有不同的行，不包含重复行。其语法格式如下：包含重复行。其语法格式如下：SELECT 语句

24、语句1UNIONSELECT 语句语句2其中语句其中语句1和语句和语句2表示的是两个用于查询的表示的是两个用于查询的SELECT语语句。句。UNION关键字表示对这两个查询语句查询出来的结果关键字表示对这两个查询语句查询出来的结果进行并操作。这里需要保证进行并操作。这里需要保证SELECT 语句语句1和和SELECT 语句语句2中查询出的列数必须相同，而且对应的列的数据类型必须中查询出的列数必须相同，而且对应的列的数据类型必须一致。一致。8.5.2 8.5.2 交操作（交操作（INTERSECTINTERSECT）执行交操作使用的关键字是执行交操作使用的关键字是INTERSECT。交操作返回。

25、交操作返回的结果集包括了连接查询结果的公共行。交操作中不会出现的结果集包括了连接查询结果的公共行。交操作中不会出现重复行。其语法格式如下：重复行。其语法格式如下：SELECT 语句语句1INTERSECTSELECT 语句语句2其中语句其中语句1和语句和语句2表示的是两个用于查询的表示的是两个用于查询的SELECT语语句。句。INTERSECT关键字表示对这两个查询语句查询出来的关键字表示对这两个查询语句查询出来的结果进行交操作。这里需要保证结果进行交操作。这里需要保证SELECT 语句语句1和和SELECT 语句语句2中查询出的列数必须相同，而且对应的列的数据类型中查询出的列数必须相同，而且

26、对应的列的数据类型必须一致。必须一致。8.5.2 8.5.2 交操作（交操作（INTERSECTINTERSECT）例如，有两个表例如，有两个表A和表和表B，表，表A中有三个列，分别是中有三个列，分别是A1,A2和和A3，表，表B中也包含三个列，分别是中也包含三个列，分别是B1,B2和和B3。这。这里假设字段里假设字段A1和字段和字段B1有相同的数据类型，字段有相同的数据类型，字段A2和字段和字段B2有相同的数据类型，字段有相同的数据类型，字段A3和字段和字段B3有相同的数据类型有相同的数据类型。现在对这两张表执行交操作。现在对这两张表执行交操作。 8.5.2 8.5.2 交操作（交操作（INTERSECTINTERSECT）对这两张表中的记录进行交操作。这里表对这两张表中的记录进行交操作。这里表A1查询的列查询的列为列为列A1，A2和和A3。这表。这表B1查询的列为列查询的列为列B1，B2和和B3。使。使用用INTERSECT关键字对查询结果进行交操作。根据交操作关键字对查询结果进行交操作。根据交操作的运算规则，执行的运算规则，执行A交交B的运算之后，其执行的结果如下图的运算之后，其执行的结果如下图所示。所示。8.5.3 8.5.3 差操作（差操作（MINUSMINUS）执执行交操作使用的关键字是执执行交操作使用的关键字是MINUS。差操作返回