数据库SQL中的表连接类型

作者: SpaceCat | 来源:发表于2016-12-14 21:58 被阅读361次

    数据库中,如果用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 杨冬青, 李红燕, 唐世渭等译。

    相关文章

      网友评论

      • 938b0e0dfc52:别的不说,就提供思路,就值得尊敬
      • 渣渣xm:大神,好感谢,周五考数据库。。😁😁

      本文标题:数据库SQL中的表连接类型

      本文链接:https://www.haomeiwen.com/subject/aihimttx.html