3.45 多表连结

3.45 多表连结

关联多表才能获得所需的信息的时候，用SELECT实现！

使用子查询处理多表

【例】检索出“计算机学院”的教师名单。该操作需要关联教师信息表 （Teacher）和学院信息表（College），才能获得这些数据。

这里嵌套了一层SELECT子查询语句，当然可以是多层但是不建议

SELECT TeacherID, TeacherName, TeacherTitle
FROM Teacher
WHERE CollegeID IN

(SELECT CollegeID
FROM College
WHERE CollegeName=’计算机学院’)
ORDER BY TeacherID;

使用连接查询多表

基本思想

是将关联表的主键值与外键值进行匹配比对，从中检索。

可以采用连接查询方式处理，其查询SQL语句如下。

SELECT TeacherID, TeacherName, TeacherTitle
FROM Teacher, College
WHERE Teacher.CollegeID=College.CollegeID AND College.CollegeName=’计算机学院’
ORDER BY TeacherID;

连接查询与子查询

连接查询、子查询可以实现相同功能

区别：当 SQL 查询所输出的信息来自多个表， SELECT子查询就不能满足要求，这时必须采用连接查询实现处理。

【例】在选课管理系统数据库中，我们希望得到各个学院的教师人数信息。这时需要采用连接查询方法，实现两表关联查询

SELECT College.CollegeName AS 学院名称， COUNT(Teacher.CollegeID) AS 教师人数
FROM Teacher, College
WHERE Teacher.CollegeID=College.CollegeID
GROUP BY College.CollegeName
ORDER BY College.CollegeName DESC;

简化表名

在多表连接查询中，可以使用 AS 关键词给表名赋予一个简单名称。

【例】在选课管理系统数据库中，我们希望得到各个学院的教师信息。使用AS关键词，给Teacher--A，给College--B。

SELECT B.CollegeName AS 学院名称， A.TeacherID AS 编号， A.TeacherName AS 姓名， A.TeacherGender AS
性别， A.TeacherTitle AS 职称
FROM Teacher AS A, College AS B
WHERE A.CollegeID=B.CollegeID
ORDER BY B.CollegeName, A.TeacherID;

JOIN…ON连接

在SQL中，实现多表关联查询。

格式

【例】在上例的SQL查询语句，可以使用JOIN…ON关键词语句方式重新编写如下。

SELECT B.CollegeName AS 学院名称, A.TeacherID AS 编号, A.TeacherName AS 姓名,A.TeacherGender AS 性别, A.TeacherTitle AS 职称
FROM Teacher AS A JOIN College AS B
ON A.CollegeID=B.CollegeID
ORDER BY B.CollegeName, A.TeacherID;

实现多表查询

【例3-40】在选课管理系统数据库中，我们希望查询课表信息。实现4表关联数据查询

SELECT C.CourseName AS 课程名称， T.TeacherName AS 教师姓名， P.CourseRoom AS 地点， P.CourseTime AS 时间， S.CollegeName AS 开课学院
FROM Course AS C JOIN Plan AS P ON C.CourseID=P.CourseID JOIN Teacher AS T ON
P.TeacherID=T.TeacherID
JOIN College AS S ON S.CollegeID=T.CollegeID
ORDER BY P.CoursePlanID;

1.内连接

在以上JOIN…ON连接查询中，只有关联表相关字段的列值满足等值连接条件时，才从这些关联表中提取数据组合成新的结果集

【例】在选课管理系统数据库中，我们希望查询所有开设课程的学生选课情况。

SELECT C.CourseName AS 课程名称， T.TeacherName AS 教师， COUNT(R.CoursePlanID) AS选课人数
FROM Course AS C JOIN Plan AS P
ON C.CourseID=P.CourseID
JOIN Teacher AS T ON P.TeacherID=T.TeacherID
JOIN Register AS R ON P.CoursePlanID=R.CoursePlanID
GROUP BY C.CourseName, T.TeacherName;

在上面的内连接查询中，只能找出有学生注册的课程名称和选课人数，但不能找出没有学生注册的课程名称和选课人数。

2.外连接

有时候我们也希望输出那些不满足连接条件的元组数据。

• LEFT JOIN：左外连接，即使右表中没有匹配，也从左表返回所有的行。
• RIGHT JOIN：右外连接，即使左表中没有匹配，也从右表返回所有的行。
• FULL JOIN：全外连接，只要其中一个表中存在匹配，就返回行。

【例+】在选课管理系统数据库中，我们希望查询所有开设课程的学生选课情况，但要包含没有学生选课的课

SELECT C.CourseName AS 课程名称， T.TeacherName AS 教师， COUNT(R.CoursePlanID) AS选课人数
FROM Course AS C JOIN Plan AS P
ON C.CourseID=P.CourseID
JOIN Teacher AS T ON P.TeacherID=T.TeacherID
LEFT JOIN Register AS R ON P.CoursePlanID=R.CoursePlanID
GROUP BY C.CourseName, T.TeacherName;