13-连接查询
8.7 连接查询
本节视频教学录像：9分钟
涉及两个或两个以上表的查询称之为连接查询。连接查询是关系数据库中最重要的查询类型。连接查询分为内连接查询、外连接查询和复合条件的连接查询。本节详细讲解这3种类型查询的实现过程。连接查询要求参加连接的表必须要是有相同意义的字段，比如学生表和成绩表进行连接，两个表都有相同字段sno列，这是连接的前提条件。
1.内连接查询
连接查询中使用WHERE子句给出连接条件，格式如下。
Where [<表名1.]<列名1〉<比较运算符>[<表名2.]<列名2〉
这里的比较运算符通常是“=”。
【范例8-34】
查询选修了课程的学生学号、姓名、课程号、成绩。
MySQL> select student.sno,sname,cno,grade -> from student,sc -> where student.sno=sc.sno;
结果如下。
+-----+-------+------+-------+ |sno|sname|cno |grade| +-----+-------+------+-------+ | 1|刘敏 | 1| 89| | 1|刘敏 | 2| 97| | 1|刘敏 | 3| 67| | 2|周松 | 1| 78| | 2|周松 | 2| 90| +-----+-------+------+-------+ 5 rows in set (0.08 sec)
该查询中，学生的姓名在student表中，课程编号和成绩在sc表中，因此该查询涉及两个表。两个表中都有学生编号sno,那么两个表就通过该公共字段进行连接。在书写查询时注意：sno字段前面加了表的前缀，是为了避免出现混淆，如果字段名称在参加连接的表中是唯一的，可以省略前缀。
在查询中给student 定义了表的别名s,这样可以简化查询的书写。
提示 定义表的别名以后，列的前缀要使用新定义的列的别名，否则出错。
可以使用ANSI SQL 语法中另外一种书写格式完成【范例8-21】的查询要求。
MySQL> select s.sno,sname,cno,grade -> from student s inner join sc -> on s.sno=sc.sno;
结果如下。
+-----+-------+------+-------+ |sno|sname|cno |grade| +-----+-------+------+-------+ | 1|刘敏 | 1| 89| | 1|刘敏 | 2| 97| | 1|刘敏 | 3| 67| | 2|周松 | 1| 78| | 2|周松 | 2| 90| +-----+-------+------+-------+ 5 rows in set (0.03 sec)
ANSI SQL 标准中的写法和使用WHERE子句给出连接条件的查询有很大区别：原来FROM子句中，参加连接的表之间用“，”号隔开，而标准语法是在参加连接的表中间加入inner join，连接条件使用on关键字给出，连接条件是相同的。
有的查询可能要对同一个表查询多次，可以使用相同表连接实现查询目的，这种连接查询称为自身连接。自身连接查询是内连接查询的一种特殊形式，在实现查询时将表逻辑上视为不同的表，通过表的别名实现。
【范例8-35】
查询每门课的课程名称及其先行课的课程名称。
MySQL> select c1.cname,c2.cname -> from course c1,course c2 -> where c1.cpno=c2.cno;
结果如下。
+----------+----------+ |cname |cname | +----------+----------+ | 数据结构 | 操作系统 | | 操作系统 | 信息系统 | |数据库 |数据结构| +----------+----------+ 3 rows in set (0.01 sec)
该查询中course使用了两次，分别定义了别名c1 和c2，然后根据查询要求，定义了连接条件c1.cpno=c2.cno进行查询。
2.外连接查询
在内连接查询中，只有满足连接条件的记录才能出现在查询结果中。但在实际应用中，希望不满足连接条件的记录在结果中出现，这时需要使用外连接查询。例如范例8-21中，只有选课的学生信息出现，而没有选修课程的学生因为在sc表中没有满足连接条件的行存在，所以被舍弃。如果希望所有同学的信息都出现，在成绩信息对应的字段填写空值(null),此实现可使用外连接。外连接分为左外连接、右外连接、完全外连接，但MySQL不支持完全外连接。
⑴left join （左连接）：join左表中所有记录和右表中满足连接条件的记录信息。
⑵righg join （左连接）：join右表中所有记录和左表中满足连接条件的记录信息。
【范例8-36】
查询所有学生的编号、姓名及选课学生的课程号、成绩。
MySQL> select s.sno,sname,cno,grade -> from student s left join sc -> on s.sno=sc.sno;
结果如下。
+-----+-------+------+-------+ |sno|sname|cno |grade| +-----+-------+------+-------+ | 1|刘敏 | 1| 89| | 1|刘敏 | 2| 97| | 1|刘敏 | 3| 67| | 2|周松 | 1| 78| | 2|周松 | 2| 90| | 3|张明 |NULL| NULL| | 4|孟欣 |NULL| NULL| +-----+-------+------+-------+ 7 rows in set (0.00 sec)
该查询中的表的连接类型为left join，student 表中所有记录均在查询结果中，但3号学生和4号学生因为在sc表中没有满足连接条件的记录存在，因此sc 对应的列的信息为NULL。
该查询也可以使用右外连接实现，代码如下。
MySQL> select s.sno,sname,cno,grade -> from sc right join student s -> on s.sno=sc.sno;
结果如下。
+-----+-------+------+-------+ |sno|sname|cno |grade| +-----+-------+------+-------+ | 1|刘敏 | 1| 89| | 1|刘敏 | 2| 97| | 1|刘敏 | 3| 67| | 2|周松 | 1| 78| | 2|周松 | 2| 90| | 3|张明 |NULL| NULL| | 4|孟欣 |NULL| NULL| +-----+-------+------+-------+ 7 rows in set (0.00 sec)
在上述实现中，将student 表放到了join的右侧，实现了相同的查询功能。
3.复合条件连接查询
在上述的查询中，WHERE子句中只有连接条件，该子句还可以跟多个连接条件，成为复合条件的连接。
【范例8-37】
查询选修了数据库课程的学号、成绩。
MySQL> select sno,grade -> from course c,sc -> where c.cno=sc.cno and cname='数据库';
结果如下。
+------+-------+ |sno |grade| +------+-------+ | 1| 89| | 2| 78| +------+-------+ 2 rows in set (0.00 sec)
连接查询除了可以两个表进行接连，还可以两个以上的表进行连接，称为多表连接。
【范例8-38】
查询学生的学号、姓名、选修的课程名。
MySQL> select s.sno,sname,cname -> from student s,sc,course c -> where s.sno=sc.sno and c.cno=sc.cno;
结果如下。
+-----+-------+----------+ |sno|sname|cname | +-----+-------+----------+ | 1|刘敏 |数据库 | | 2|周松 |数据库 | | 1|刘敏 |操作系统| | 2|周松 |操作系统| | 1|刘敏 |信息系统| +-----+-------+----------+ 5 rows in set (0.00 sec)
该查询要求student表中的编号、姓名及course表中的cname出现在查询结果中，但这两个表没有意义相同的列，不能进行连接。因此要借助另一个表sc参加连接。