SQLServer学习笔记八：多表连接查询

学习目标

• 掌握多表连接查询的概念
• 学会使用内连接查询数据
• 学会使用外连接查询数据
• 学会使用UNION合并查询结果

前面学习的内容都是基于单个数据库表的查询，下面将学习涉及多个表的数据查询。

多表连接查询的分类

在之前的学生成绩查询中，每次显示的都是学生的编号信息，因为该表中只存储了学生的编号。实际上最好显示学生的姓名，而姓名却存储在学生信息表中，像这种需要从多个表中选择或者比较数据的情况，就需要使用多表连接查询。

多表连接查询实际上是通过各个表之间共同列的关联性来查询数据的，它是关系数据库查询最主要的特征。
以下是几种常用的连接查询方式：内连接、外连接。

• 内连接查询
  内连接查询是最典型、最常用的连接查询，它根据表中共同的列来进行匹配。特别是两个表存在主外键关系时通常会使用内连接查询。
• 外连接查询
  外连接查询是至少返回一个表中的所有记录，根据匹配条件有选择性地返回另一张表的记录。外连接可以是左外连接、右外连接。

内连接查询

内连接查询通常会使用=或<>等比较运算符来判断两列数据值是否相等，上面所说的根据学生学号来判断学生姓名的连接就是一种内连接。

内连接使用INNER JOIN...ON关键字或WHERE子句来进行表之间的关联。内连接查询可以通过两种方式实现。

在WHERE子句中指定连接条件

查询学生姓名和成绩的T-SQL如下：

SELECT students.sname AS 姓名, score.cno AS 课程编号, score.grade AS 成绩
FROM students, score
WHERE students.sno = score.sno

查询结果

上面这种形式的查询，相当于FROM后面紧跟了两个表名，在字段列表中用表名.列名来区分列，再在WHERE条件子句中加以判断，要求学生编号信息相同。

在FROM 子句中使用 INNER JOIN…ON

上面的查询也可以通过以下的JOIN...ON子句来实现：

SELECT S.sname AS 姓名, CS.cno AS 课程编号, CS.grade AS 成绩
FROM students AS S
INNER JOIN score AS CS ON S.sno = CS.sno

在上面的内连接查询中：

• INNER JOIN用来连接两个表
• INNER可以省略
• ON设置条件
• AS 指定表的“别名”。如果查询的列名在用到的两个或多个表中不重复，则对于这一列的引用不必限定表名。

在看下面的T-SQL语句

SELECT S.sname AS 姓名, CS.cno AS 课程编号, CS.grade AS 成绩
FROM students AS S
INNER JOIN score AS CS ON S.sno = CS.sno
WHERE CS.grade >= 60 AND CS.cno = '0101001' 

查询将返回科目编号为$0101001$的及格学生的姓名和成绩。WHERE子句用来限定查询条件。

查询结果

内连接查询通常不仅仅连接两个表，有时候还会涉及三个表或者更多表。

例如，除了学生信息表、学生成绩表之外，还存在课程表。下面的查询不仅仅要显示学生姓名、分数，而且要通过课程编号来显示课程名称表中对应课程的名称，可以使用以下三表连接查询的T-SQL语句来实现。

SELECT S.sname AS 姓名, C.cname AS 课程名称, CS.grade AS 成绩
FROM students AS S
INNER JOIN score AS CS ON S.sno = CS.sno
INNER JOIN courses AS C ON C.cno = CS.cno

查询结果
注意，以下数据分别来自三个不同的数据表。

外连接查询

内连接查询的结果是从两个或两个以上表的组合中挑选出符合连接条件的数据，如果数据无法满足连接条件则将其忽略。在内连接查询中，参与连接的表的地位是平等的。

与内连接相对的方式称为外连接查询。在外连接查询中参与连接的表有主从之分，以主表的每行数据匹配从表的数据列，将符合连接条件的数据直接返回到结果集中；对于那些不符合连接条件的列，将被填上NULL（空值）后再返回到结果集中。

左外连接查询

左外连接查询的结果集包括LEFT JOIN子句中指定的做表的所有行，而不仅仅是连接列所匹配的行。如果左表的某行在右表中没有匹配行，则结果集中右表的所选择的列均为空值。

左外连接查询使用LEFT JOIN...ON或者LEFT OUTER JOIN...ON来进行表之间的关联。例如统计所有学生的考试情况，要求所示所有参加学生的每门考试分数，没有参加考试的学生也要显示出来。这时候，以学生表为主表（也叫左表）、成绩表为从表的左外连接查询的T-SQL如下：

SELECT S.sname AS 姓名, SC.cno AS 课程编号, SC.grade AS 分数
FROM students AS S
LEFT OUTER JOIN score AS SC ON S.sno = SC.sno 

查询结果

其中，从学生表中把每一条记录根成绩表的记录进行匹配，匹配条件为S.sno = SC.sno，如果匹配成果，则加入记录集；若没有找到匹配的记录，则用NULL填充记录集。

右外连接查询

右外连接查询与左外连接查询类似，只不过要包含右表中所有匹配的行。若右表中有的项在坐标中没有对应的项，则以NULL值填充。

右外连接查询使用RIGHT JOIN...ON或者RIGHT OUTER JOIN...ON来进行表之间的关联。例如，通过右外连接查询所有教师和其所在院系，在教师表中没有分配院系的教师也会被列出：

SELECT D.dname AS 院系, T.tname AS 教师姓名
FROM departments AS D
RIGHT JOIN teachers AS T ON D.deptno = T.deptno
ORDER BY D.deptno

查询结果

全连接（FULL OUTER JOIN）

与左外连接和右外连接不同，全连接（FULL OUTER JOIN）结合的左、右外连接的结果。结果集将包含来自两个表的所有记录，并使用NULL值作为没有匹配到的结果。

SELECT T.tno, T.tname, D.dname
FROM teachers AS T
FULL JOIN departments AS D ON D.deptno = T.deptno

例如上面的语句的执行结果为：

交叉连接（CROSS JOIN）

交叉连接（CROSS JOIN）返回两个或多个联接表中的记录集的笛卡尔乘积，即两个表记录相乘的结果。例如：

students

学号	姓名
202201	张三
202202	李四

courses

课程编号	课程名称
1001	SQL Server
1002	C ++
1003	网页设计

代码

SELECT * FROM students
CROSS JOIN courses

查询结果

学号	姓名	课程编号	课程名称
202201	张三	1001	SQL Server
202202	李四	1001	SQL Server
202201	张三	1002	C ++
202202	李四	1002	C ++
202201	张三	1003	网页设计
202202	李四	1003	网页设计

练习

使用交叉连接查询每名同学每门课的成绩，如果没有参加考试，则使用NULL值作为结果。

SELECT S.sno, S.sname, C.cno, C.cname, SC.grade
FROM students AS S
CROSS JOIN courses AS C -- 交叉连接courses，返回笛卡尔乘积
LEFT JOIN score AS SC ON SC.sno = S.sno AND SC.cno = C.cno -- 左外连接score，获得该学生的每门课成绩
ORDER BY S.sno

查询结果

UNION(ALL)合并查询结果

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。使用时请注意：

• UNION 内部的 SELECT 语句必须拥有相同数量的列
• 列也必须拥有相似的数据类型
• 每条 SELECT 语句中的列的顺序必须相同

语法

SELECT 列1, 列2, 列3 FROM 表1
UNION
SELECT 列1, 列2, 列3 FROM 表2

UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。

练习

查询参加过课程编号为0101001和0102005考试的学生姓名，使用UNION将他们合并。

代码

SELECT S.sname
FROM score AS SC
JOIN students AS S ON S.sno = SC.sno
WHERE SC.cno = '0101001' 
UNION 
SELECT S.sname
FROM score AS SC
JOIN students AS S ON S.sno = SC.sno
WHERE SC.cno = '0102005'

结果

总结

• 多表之间的查询可以使用连接查询，连接查询又分为内连接查询、外连接查询和交叉连接查询。
• 最常见的连接查询是内连接查询(INNER JOIN...ON)，通常会在相关表之间提取引用的数据项。
• 使用UNION(ALL)可以合并查询结果