数据库中,如果用SQL语言进行多个表的查询,就需要用到连接操作。这里对SQL中的连接类型进行简单的介绍。本文内容可能会涉及以下几张表:
院系表:department(dept_name, building, budget);
课程表:course(course_id, title, dept_name, credits)
老师表:instructor(ID, name, dept_name, salary)
教学表:teaches(ID, course_id, sec_id, semester)
学生表:student(ID, name, dept_name, tot_cred)
选课表:takes(ID, course_id, sec_id, semester, year, grade)
1、多表查询
如果要查询所有教师的姓名、院系和院系所在的建筑,就需要用到多表查询:
select name, instructor.dept_name, building
from instructor, department
where instructor.dept_name = department.dept_name
可以这样理解,该查询在执行时,首先将instructor表和department表中的所有的记录做笛卡尔积,然后,根据where子句中指定的谓词,对笛卡尔乘积的结果进行筛选,最后得到查询结果。
然而,实际中,该查询不会按照这个步骤执行,数据库会(尽可能地)只产生满足where子句谓词的笛卡尔积元素来进行优化执行。
这是SQL种最基本的表连接形式。除此之外,SQL中还提供了关键字join,专门用于表连接查询,这些连接可以分为内连接(inner join)和外连接(outer join)两种。外连接又分为左外连接(left outer join)、右外连接(right outer join)和全外连接(full outer join)。
2、自然连接
2.1 什么是自然连接
这里首先介绍自然连接,看下面的连接:
select name, instructor.dept_name, building
from instructor, department
where instructor.dept_name = department.dept_name
这里的连接谓词是instructor.dept_name = department.dept_name
,而这两个表中名称相同的字段只有dept_name。实际上,这是一种常见的情况。为此,SQL支持了一种叫做自然连接(natural join)的运算。
自然连接只取出两张表的笛卡尔乘积中,那些在两个表中都出现的字段上取值相同的结果。比如:
select name, instructor.dept_name, building
from instructor natural join department
这个例子中,只会取出instructor表和department表笛卡尔乘积结果中,那些instructor.dept_name和department.dept_name相等的记录,作为连接的结果。因为dept_name是唯一的两张表中都出现的字段(如果两张表还有其他相同的字段,那么查询结果中来自不同表的这些字段也都必须相等)。
2.2 自然连接注意
除了只关注两个表中的相同字段,自然连接还有两个地方需要注意:
-
连接结果中,对于两张表中都有的字段,只出现一次。
-
字段出现的顺序
先是两张表中都有的字段,然后是只出现在第一张表中的字段,最后是只出现在第二张表中的字段。
2.3 自然连接运算的结果是关系
关系数据库中,关系就是表,表就代表关系。这里,自然连接运算的结果是关系,可以理解为自然连接的结果也是一张表。例子,查询所有教师的名字,以及他们教授的课程名称。
select name, title
from instructor natural join teaches, course
where teaches.course_id = course.course_id
这个例子中,先将instructor表和teaches表进行自然连接,由于得到的结果也是一个关系(表),所以也可以将该结果和course表连接。注意,这里的teaches.course_id是自然连接结果中的course_id字段,因为它是来自teaches表,所以用teaches.course_id
标识。
2.4 using子句
看上面的连接语句,似乎可以写成:
select name, title
from instructor natural join teaches natural join course
其实,前一个自然连接的结果包括字段(ID, name, dept_name, salary, course_id, sec_id),而course表包含的字段(course_id, title, dept_name, credits)。两者的公共字段除了course_id之外,还有dept_name。所以,该SQL实际上相当于:
select name, title
from instructor natural join teaches, course
where teaches.course_id = course.course_id and teaches.dept_name = course.dept_name
为了避免不必要的相等属性带来的问题,同时发扬自然连接的优点,SQL中提供了using子句来允许用户指定哪些字段相等。例如,上面的SQL就相当于:
select name, title
from instructor natural join teaches natural join course using (course_id, dept_name)
当然,using子句中也可以是单个字段,如:
select name, title
from instructor natural join teaches natural join course using (course_id)
2.5 on子句
on子句允许在参与连接的关系上指定通用的谓词,这个谓词的写法和where子句谓词类似。和using子句一样,on子句出现在连接表达式的末尾。下面是一个例子:
select *
from student join takes on student.ID = takes.ID
这里的意思是连接student表和takes表中,ID字段值相同的记录。功能上和下面的脚本类似:
select *
from student natural join takes
两者之间的区别就在于前者结果中ID属性出现两次,一次来自student,一次来自takes;而后者结果中ID属性只出现一次,因为是自然连接。
on条件可以表示任何的SQL谓词,因此使用on条件的连接表达式可以表示比自然连接更加复杂的连接条件。功能上,上面带on子句的连接等价于:
select *
from student, takes
where student.ID = takes.ID
因此,可以将on子句中的谓词移到where子句中,但这并不是说on子句是多余的,使用on子句有以下优点:
- 在外连接中,on子句和where子句的表现不同(后文介绍)。
- 在on子句中指定连接条件,在where子句中出现其它的连接条件,这样的SQL可读性更好。
3、外连接
3.1 外连接介绍
看如下查询:
select *
from student natural join takes
可以查询出学生信息和每个学生的选课信息。但是,这里有个问题,如果有一名学生Snow没有选任何课程,那么他不会出现在查询结果中。为了避免参与连接的一个或两个表中的某些记录以这种方式“丢失”,SQL提供了外连接操作。通过在结果中创建包含空值记录的方式,保留那些在连接中“丢失”的记录。
SQL中的外连接有三种形式:
- 左外连接(left outer join)
保证出现在连接操作左边的表记录不“丢失” - 右外连接(right outer join)
保证出现在连接操作右边的表记录不“丢失” - 全外连接(full outer join)
保证出现在连接操作两边的表记录都不“丢失”
比如,上面的例子中,为了保证学生记录不丢失,可以采用左外连接:
select *
from student natural left join takes
这样,查询结果中就会多一条记录,该记录中来自student表中的字段就是student表中Snow记录的值,而来自takes表中的字段都为null。
3.2 利用外连接查询“丢失”的记录
这样,利用外连接,可以查询出一门课都没有选的学生:
select ID
from student natural left join takes
where course_id is null
注意:判断是否为空用的是is null
。
3.3 左外连接和右外连接是对称的
不难理解,如下的两个连接基本等价:
select *
from student natural left join takes
基本等价于
select *
from takes natural left join student
这里,基本等价的意思是说,结果中属性出现的顺序会不同。
3.4 on子句和外连接
on子句也可以和外连接一起使用,下面两个查询的功能基本相同(除了结果中ID字段出现的次数):
select *
from student left outer join takes on student.ID = takes.ID
基本等价于
select *
from student left natural join takes
如前所述,外连接中on子句的表现和where子句不同,因为外连接只为结果集中没有出现的记录补上空值并插入结果集,on子句是外连接声明的一部分。但where子句却不是,where子句中的谓词作用于外连接的结果,用来对外连接的结果进行筛选。看下面的查询:
select *
from student left outer join takes on true
where student.ID = takes.ID
在这个查询中,由于外连接中on子句的谓词用的是true,所以student和takes表进行外连接实际上相当于做了笛卡尔积,在该外连接的结果中,包含所有来自两个表的记录,所以,不会向连接结果中补充空值记录。而where子句只筛选出外连接结果中满足谓词条件的记录。这样,学生Snow这样的记录就不会出现在最后的结果中。这就是外连接中on和where的区别。
4、内连接
区别于上面提到的外连接,SQL把一般的连接称作内连接(inner join)。而关键字inner是可选的,如果join子句中没有outer前缀,那么默认的连接类型就是inner join。
select *
from student join takes using(ID)
等价于
select *
from student inner join takes using(ID)
类似地,natural join等价于natural inner join。
5、总结
实际上,内连接和外连接可以归纳为连接的类型,而natural、on子句和using子句可以归纳为连接的条件。如下表:
连接类型 | 连接条件 |
---|---|
inner join | natural |
left outer join | on子句 |
right outer join | using子句 |
full outer join | ... |
任意的连接类型可以和任意的连接条件,进行组合,来满足连接查询需求。
参考
《数据库系统概念》 机械工业出版社 Abraham Silberschatz, Henry F. Korth, S. Sudarshan 杨冬青, 李红燕, 唐世渭等译。
网友评论