3.3.3 自然连接

发表于 更新于 分类于 ， ， ， Waline： 本文字数： 5.3k 阅读时长 ≈ 5 分钟

3.3.3 自然连接

在我们的查询示例中,需要从instructor和teaches表中组合信息,匹配条件是需要instructor.ID等于teaches.ID。这是在两个关系中具有相同名称的所有属性。
在最通常的情况下from子句中的匹配条件是在所有匹配名称的属性上相等。

为了在这种通用情况下简化SQL编程者的工作,SQL支持一种被称作自然连接的运算,下面我们就来讨论这种运算。事实上SQL还支持几种另外的方式使得来自两个或多个关系的信息可以被连接(join)起来。我们已经见过怎样利用笛卡儿积和where子句谓词来连接来自多个关系的信息。连接来自多个关系信息的其他方式在4.1节介绍。

笛卡儿积

笛卡儿积将第一个关系的每个元组与第二个关系的所有元组都进行连接;

自然连接

自然连接(natural join)运算作用于两个关系,并产生一个关系作为结果。自然连接只考虑那些在两个关系模式中都出现的属性上取值相同的元组对。
因此,回到instructor和teaches关系的例子上,instructor和teaches的自然连接计算中只考虑这样的元组对:来自instructor的元组和来自teaches的元组在共同属性ID上的取值相同

授课:teaches(ID,course_id,sec_id,semester,year)
讲师:instructor(ID,name,dept_name,salary)

SQL查询 对于大学中所有讲授课程的教师,找出他们的姓名以及所讲述的所有课程标识

之前写法

1
2
3
select name, course_id
from instructor,teaches
where instructor.ID= teaches.ID;

自然连接写法

该查询可以用SQL的自然连接运算更简洁地写作:

1
2
select name, course_id
from instructor natural join teaches;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
mysql> select name, course_id
from instructor natural join teaches;
+------------+-----------+
| name       | course_id |
+------------+-----------+
| Srinivasan | CS-101    |
| Srinivasan | CS-315    |
| Srinivasan | CS-347    |
| Wu         | FIN-201   |
| Mozart     | MU-199    |
| Einstein   | PHY-101   |
| El Said    | HIS-351   |
| Katz       | CS-101    |
| Katz       | CS-319    |
| Crick      | BIO-101   |
| Crick      | BIO-301   |
| Brandt     | CS-190    |
| Brandt     | CS-190    |
| Brandt     | CS-319    |
| Kim        | EE-181    |
+------------+-----------+
15 rows in set

from后面可以使用多个自然连接

在一个SQL查询的from子句中,可以用自然连接将多个关系结合在一起

SQL查询 列出教师的名字以及他们所讲授课程的名称

先自然连接 再笛卡儿积

1
2
3
select name,title
from instructor natural join teaches, course
where teaches.course_id= course.course_id;

课程:course(course_id,title,dept_name,credits)
授课:teaches(ID,course_id,sec_id,semester,year)
讲师:instructor(ID,name,dept_name,salary)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
mysql>  select name,title
from instructor natural join teaches, course
where teaches.course_id= course.course_id;
+------------+----------------------------+
| name       | title                      |
+------------+----------------------------+
| Crick      | Intro. to Biology          |
| Crick      | Genetics                   |
| Srinivasan | Intro. to Computer Science |
| Katz       | Intro. to Computer Science |
| Brandt     | Game Design                |
| Brandt     | Game Design                |
| Srinivasan | Robotics                   |
| Katz       | Image Processing           |
| Brandt     | Image Processing           |
| Srinivasan | Database System Concepts   |
| Kim        | Intro. to Digital Systems  |
| Wu         | Investment Banking         |
| El Said    | World History              |
| Mozart     | Music Video Production     |
| Einstein   | Physical Principles        |
+------------+----------------------------+
15 rows in set

先计算instructor和teaches的自然连接 再计算该结果和course的笛卡儿积
where子句从这个结果中提取出这样的元组:来自连接结果的课程标识与来自course关系的课程标识相匹配。
注意where子句中的teaches.course_id表示自然连接结果中的course_id域,因为该域最终来自teaches关系。

全部自然连接

1
2
select name,title
from instructor natural join teaches natural join course;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
mysql> select name,title from  instructor natural join teaches natural join course;
+------------+----------------------------+
| name       | title                      |
+------------+----------------------------+
| Crick      | Intro. to Biology          |
| Crick      | Genetics                   |
| Srinivasan | Intro. to Computer Science |
| Katz       | Intro. to Computer Science |
| Brandt     | Game Design                |
| Brandt     | Game Design                |
| Srinivasan | Robotics                   |
| Katz       | Image Processing           |
| Brandt     | Image Processing           |
| Srinivasan | Database System Concepts   |
| Kim        | Intro. to Digital Systems  |
| Wu         | Investment Banking         |
| El Said    | World History              |
| Mozart     | Music Video Production     |
| Einstein   | Physical Principles        |
+------------+----------------------------+
15 rows in set

instructor(ID,name,dept_name,salary)
teaches(ID,course_id,sec_id,semester,year)
为了说明原因,注意instructor和teaches的自然连接包括属性(ID, name, dept_name, salary, course_id, secid)

自然连接和笛卡儿积的区别

相同点

自然连接和笛卡儿积都会合并元组

不同点

笛卡儿积直接合并元组,就是在前一个关系的元组后面追加后面关系的元组即可.

实例

instructor关系包括(ID,name,dept_name,salary)这4个属性.
teaches关系包括(ID, course_id,sec_id,semester,year)这5个属性.

笛卡尔积结果

instructor和teaches的笛卡儿积将有(ID,name,dept_name,salary,ID, course_id,sec_id,semester,year)这9个属性.可以看到笛卡儿积的结果将有两个重复的ID属性.

1
2
3
4
mysql> select * from instructor ,teaches;
+-------+------------+------------+--------+-------+-----------+--------+----------+------+
| ID    | name       | dept_name  | salary | ID    | course_id | sec_id | semester | year |
+-------+------------+------------+--------+-------+-----------+--------+----------+------+

这里笛卡儿积合并的结果为:
第一个表的属性 第二个表的属性

自然连接结果

instructor关系和teaches关系都有一个公共的属性ID,自然连接合并公共的属性ID,也就是自然连接的结果为:
(ID,name,dept_name,salary,course_id,sec_id,semester,year),可以发现自然连接只有8个属性,重复的属性ID只占一列.

1
2
3
mysql> select * from instructor natural join teaches;
+-------+------------+------------+--------+-----------+--------+----------+------+
| ID    | name       | dept_name  | salary | course_id | sec_id | semester | year |

这里自然连接合并的结果为:
公共属性 第一个表的属性 第二个表的属性

大学数据库模式

教室:classroom(building,room_number,capacity)
系:department(dept_name,building,budget)
课程:course(course_id,title,dept_name,credits)
授课:teaches(ID,course_id,sec_id,semester,year)
讲师:instructor(ID,name,dept_name,salary)
section(course_id,sec_id,semester,year,building,room_number,time_slot_id)
学生:student(ID,name,dept_name,tot_cred)
takes(ID,course_id,sec_id,semester,year,grade)
advisor(s_ID,i_ID)
time_slot(time_slot_id,day,start_time,end_time)
prereq(course_id,prereq_id)