连接查询与集合查询_MySQL

连接查询与集合查询

在对数据库的查询过程中，有些时候检索一张表中的数据记录往往不能满足开发人员或者客户的需要。例如，查询学生的选课成绩信息。而学生选课信息和课程成绩信息分别在两个不同的数据表中。其中，在课程信息表（T_curriculum）中包括课程的编号、课程的名字、课程的学分、课时以及授课的教师等学生选课信息，而学生的编号、选课的课程编号以及课程成绩等信息在成绩信息表（T_result）中，此时为了在查询的结果中显示学生选课信息和所选课程的相关信息，就需要同时检索课程信息表（T_curriculum）和成绩信息表（T_result）。这就需要进行连接查询的操作。

1.内连接查询

等值连接

等值连接是指将指定的连接条件通过使用等号运算符（=）连接起来，并返回符合连接条件的数据行。其语法格式如下：

SELECT 表名1.字段, 表名2.字段 ….

FROM 表名1,表名2

WHERE 表名1.字段1=表名2.字段2

其中，SELECT 语句中表名1.字段和表名2.字段表示指定数据表1和数据表2中要查询的列；FROM语句中表名1和表名2表示指定连接的数据表的名字；WHERE子句中表名1.字段1=表名2.字段2表示用于指定连接条件的列。这里的字段1中的列和字段2中的列必须是两个表之间相互关联的列。

非等值连接

非等值连接是指使用除等号运算符（=）以外的其他运算符将指定条件连接起来而执行的查询操作。其他运算符包括、>=（大于等于）、<=（小于等于）、>（大于）、<（小于）、!=（不等于）等，还可以使用BETWEEN…AND运算符。

SELECT R.stuID,S.stuName,C.curID, C.curName,R.result

FROM T_result R,T_curriculum C,T_student S

WHERE R.curID=C.curID

AND R.result>80

使用ON子句建立相等连接

在SQL语句中除了WHERE子句中使用等号运算符（=）实现等值连接的操作之外，还可以使用ON子句建立相等连接条件。其语法规则如下：

SELECT 表名1.字段, 表名2.字段 ….

FROM 表名1 JOIN 表名2

ON 表名1.字段1=表名2.字段

其中关键字JOIN表示将表1和表2连接起来，ON子句用来指定连接条件的列。

使用USING子句建立相等连接

在进行连接操作时，有时只希望将两张表中相互关联的列建立一个等值连接。此时，可以使用USING子句建立相等连接来简化使用等号运算符（=）建立的等值连接操作。其语法规范如下：

SELECT 表名1.字段, 表名2.字段 ….

FROM 表名1 JOIN 表名2

USING (字段1)

其中关键字JOIN表示将表1和表2连接起来，USING子句中使用括号将字段1括起来，字段1就是两个表中建立等值连接相互关联的列。

2.交叉连接

交叉连接返回的结果是一个笛卡尔积。所谓笛卡尔积实际就是两个集合相乘的结果。假设集合A中有n个元素，集合B中有m个元素，如果最后返回的结果是n*m，那么这个结果就是集合A和集合B的笛卡尔积。

SELECT R.stuID,C.curID

FROM T_result R,T_curriculum C

或FROM T_result R cross join T_curriculum C

3.自连接查询

前面讲到的连接都是在表与表之间进行的。连接查询除了可以在不同的表之间进行，也可以对同一张表实现连接操作，这种连接查询的方式称为自连接。所谓自连接，就是指一个数据表与其自身进行连接。其语法规则如下：

SELECT A.字段, A.字段 ….

FROM 表名1 A,表名1 B

WHERE A.字段=B.字段

由于连接的是同一张表，所以在FROM语句中需要为表定义不同的别名，这里为表1分别定义了表的别名为A和B。SELECT语句中可以使用A.字段的形式也可以使用B.字段的形式查询需要的记录。这里的SELECT 语句中使用的是A.字段的形式。

例如，在课程信息表中选择学分数比操作系统的学分数多的课程信息。

SELECT C2.curID,C2.curName,C2.credit

FROM T_curriculum C1,T_curriculum C2

WHERE C1.curName = '操作系统'

AND C1.credit

4.外连接查询

在前面讲述的连接操作中，返回的结果都是满足连接条件的记录。有些时候，开发人员或者用户对于不满足连接条件的部分记录也感兴趣，这个时候就需要使用外连接查询。外连接查询不仅可以返回满足连接条件的记录，对于一个数据表中在另一个数据表中不匹配的记录也可以返回。外连接查询主要包括三种：左外连接、右外连接和全外连接。

左外连接

左外连接中查询的结果中不仅将显示满足连接条件的记录，而且还包括左侧表中不满足查询条件的记录。在Oracle数据库中，可以使用加号运算符（+）来表示左外连接。当该加号运算符（+）出现在连接条件的左边时，就称之为左外连接。其语法格式如下：

SELECT 表名1.字段, 表名2.字段 ….

FROM 表名1,表名2

WHERE 表名1.字段1(+)=表名2.字段2

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

在MySQL数据库和Microsoft SQL Server数据库中可以使用LEFT[OUTER] JOIN关键字实现，其中OUTER关键字是可选的。使用LEFT[OUTER] JOIN关键字实现左外连接的语法规则如下：

SELECT 表名1.字段, 表名2.字段 ….

FROM 表名1 LEFT JOIN表名2

ON 表名1.字段1=表名2.字段2

这里使用LEFT JOIN关键字代替SQL语句中FROM语句里的逗号，使用ON子句代替标准SQL语句中的WHERE子句，并将SQL语句中表示左外连接的加号运算符（+）去除。

右外连接

在Oracle数据库中，当该加号运算符（+）出现在连接条件的右边时，就称之为右外连接。其语法格式如下：

SELECT 表名1.字段, 表名2.字段 ….

FROM 表名1,表名2

WHERE 表名1.字段1=表名2.字段2(+)

在MySQL和Microsoft SQL Server数据库中可以使用RIGHT [OUTER] JOIN关键字实现，其中OUTER关键字是可选的。使用RIGHT [OUTER] JOIN关键字实现左外连接的语法规则如下：

SELECT 表名1.字段, 表名2.字段 ….

FROM 表名1 RIGHT JOIN表名2

ON 表名1.字段1=表名2.字段2

全外连接

全外连接中查询的结果中不仅将显示左侧表中不满足连接条件的记录，而且还会显示右侧表中不满足查询条件的记录。全外连接可以认为是左外连接与右外连接的合集（不包括重复行）。

全外连接可以使用FULL [OUTER] JOIN关键字实现，其中OUTER关键字是可选的。使用FULL [OUTER] JOIN关键字实现左外连接的语法规则如下：

SELECT 表名1.字段, 表名2.字段 ….

FROM 表名1 FULL JOIN表名2

ON 表名1.字段1=表名2.字段2

5.集合查询

在SQL的连接查询语句中，还有一种查询方式就是结合查询。集合查询主要包括三种：并操作、交操作和差操作。其中交操作和差操作并不是对目前主流的所有的数据库的适用。

并操作（UNION）

执行并操作使用的关键字是UNION。并操作返回的结果集是包括了两个查询语句中查询出来的所有不同的行，不包含重复行。其语法格式如下：

SELECT 语句1

UNION

SELECT 语句2

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

交操作（INTERSECT）

执行交操作使用的关键字是INTERSECT。交操作返回的结果集包括了连接查询结果的公共行。交操作中不会出现重复行。其语法格式如下：

SELECT 语句1

INTERSECT

SELECT 语句2

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

差操作（MINUS）

执执行交操作使用的关键字是MINUS。差操作返回的记录结果集是只在第一个SELECT语句中出现存在，但不存在于第二个SELECT语句的查询结果中。在其语法格式如下：

SELECT 语句1

MINUS

SELECT 语句2