基于 MySQL 的数据库实践（自然连接）
nbwinpe 2018-04-14
在基本查询一节的示例中，我们有从 instructor 和 teaches 表组合信息，匹配条件是 instructor.ID 等于 teaches.ID 的查询，ID 属性是两个表中具有相同名称的所有属性，按照两个表中所有相同名称属性组合实际上是一种通用情况，即 from 子句中的匹配条件在最自然的情况下需要在所有匹配名称的属性上相等。因此，SQL 提供了完成这种操作的运算，称之为自然连接（natural join）。实际上，SQL 还支持更丰富的连接（join）运算，后面会提到。
自然连接运算作用于两个关系，并产生一个关系作为结果，不同于两个关系上的笛卡尔积，笛卡尔积将第一个关系的每个元组与第二个关系的所有元组都进行连接；自然连接只考虑那些在两个关系模式中都出现的属性上取值相同的元组对。
因此，回到 instructor 和 teaches 关系的例子上，它们的自然连接只考虑在唯一共有属性 ID 上取值相同的元组对。
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 (0.01 sec)
我们知道 from 子句可以涉及多个关系，现在我们可以说，这些关系也可以是自然连接的结果，这是很直观的，因为自然连接的结果也是一个关系。
考虑查询，列出教师的名字以及他们讲授课程的名称。
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 (0.01 sec)
这个查询首先计算 instructor 和 teaches 的自然连接，如前所见，再计算这个救过和 course 的笛卡尔积，然后按照 where 子句筛选出结果，注意 where 子句中的 teaches.course_id 表示自然连接结果中的 course_id 域，这是因为该域最终来自 teaches 关系。
下面的查询给出的结果虽然在当前模式下相同，但其实是有问题的。
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 (0.00 sec)
它的问题在于 course 关系和 instructor 关系中都包含了 dept_name 属性，因此它们自然连接的结果要在这个属性上相同，这样的查询会遗漏以下模式的元组对，教师所讲授的课程不是他所在系的课程，前一个查询能够正确输出这样的元组对。
为了应付这个问题，即在保留自然连接的简洁性的同时规避过多的属性匹配，SQL 提供了一种自然连接的构造形式，允许用户来指定需要哪些列相等。
mysql> select name, title
-> from (instructor natural join teaches) join course using (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 (0.00 sec)
join ... using 运算中需要给定一个属性名列表，其两个输入中都必须具有指定名称的属性，考虑运算 r1 join r2 using (A1, A2)，它与 r1 和 r2 的自然连接类似，只不过在 t1.A1 = t2.A1 且 t1.A2 = t2.A2 的情况下就能匹配 r1 的元组 t1 和 r2 的元组 t2，即使它们都有属性 A3，也不考虑这个属性的事。
