join操作

为什么要给予join操作特殊的关照？

答：join操作受到了许多关注，存在若干个变形，理由有3个：

1. join操作是关系代数中最有用的操作之一，是联合来自两个或者多个关系的信息最常用的操作之一；
2. 虽然一个join可通过selections和projections之后接一个cross-product来定义，但是在实践中，join比普通的cross-product出现的频率更高；
3. 不用将底层的叉积实物化就能识别和实现join非常重要，因为一个cross-product的结果通常要比join大很多；
4. 跟叉积相比，用join操作来表示一些查询更自然；
5. 存在一些仅使用叉积是很难表示的查询；

难道不能使用cross-product、selections、projections等操作来表示每个join操作吗？

答：不能，因为存在一些查询用叉积是很难表示的。

join操作的变形

join类型和join条件.png

• inner join
  natual join；
  join...on
  join...using
• outer join
  left outer join
  right outer join
  full outer join

natural join

natural-join.png

示例代码

mysql> desc student;
+-----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| ID | varchar(5) | NO | PRI | NULL | |
| name | varchar(20) | NO | | NULL | |
| dept_name | varchar(20) | YES | MUL | NULL | |
| tot_cred | decimal(3,0) | YES | | NULL | |
+-----------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> desc takes;
+-----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| ID | varchar(5) | NO | PRI | NULL | |
| course_id | varchar(8) | NO | PRI | NULL | |
| sec_id | varchar(8) | NO | PRI | NULL | |
| semester | varchar(6) | NO | PRI | NULL | |
| year | decimal(4,0) | NO | PRI | NULL | |
| grade | varchar(2) | YES | | NULL | |
+-----------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

mysql> select count(*) from student;
+----------+
| count(*) |
+----------+
| 13 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from takes;
+----------+
| count(*) |
+----------+
| 22 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from student natural join takes;
+----------+
| count(*) |
+----------+
| 22 |
+----------+
1 row in set (0.00 sec)

mysql> select * from student natural join takes;
+-------+----------+------------+----------+-----------+--------+----------+------+-------+
| ID | name | dept_name | tot_cred | course_id | sec_id | semester | year | grade |
+-------+----------+------------+----------+-----------+--------+----------+------+-------+
| 00128 | Zhang | Comp. Sci. | 102 | CS-101 | 1 | Fall | 2017 | A |
| 00128 | Zhang | Comp. Sci. | 102 | CS-347 | 1 | Fall | 2017 | A- |
| 12345 | Shankar | Comp. Sci. | 32 | CS-101 | 1 | Fall | 2017 | C |
| 12345 | Shankar | Comp. Sci. | 32 | CS-190 | 2 | Spring | 2017 | A |
| 12345 | Shankar | Comp. Sci. | 32 | CS-315 | 1 | Spring | 2018 | A |
| 12345 | Shankar | Comp. Sci. | 32 | CS-347 | 1 | Fall | 2017 | A |
| 19991 | Brandt | History | 80 | HIS-351 | 1 | Spring | 2018 | B |
| 23121 | Chavez | Finance | 110 | FIN-201 | 1 | Spring | 2018 | C+ |
| 44553 | Peltier | Physics | 56 | PHY-101 | 1 | Fall | 2017 | B- |
| 45678 | Levy | Physics | 46 | CS-101 | 1 | Fall | 2017 | F |
| 45678 | Levy | Physics | 46 | CS-101 | 1 | Spring | 2018 | B+ |
| 45678 | Levy | Physics | 46 | CS-319 | 1 | Spring | 2018 | B |
| 54321 | Williams | Comp. Sci. | 54 | CS-101 | 1 | Fall | 2017 | A- |
| 54321 | Williams | Comp. Sci. | 54 | CS-190 | 2 | Spring | 2017 | B+ |
| 55739 | Sanchez | Music | 38 | MU-199 | 1 | Spring | 2018 | A- |
| 76543 | Brown | Comp. Sci. | 58 | CS-101 | 1 | Fall | 2017 | A |
| 76543 | Brown | Comp. Sci. | 58 | CS-319 | 2 | Spring | 2018 | A |
| 76653 | Aoi | Elec. Eng. | 60 | EE-181 | 1 | Spring | 2017 | C |
| 98765 | Bourikas | Elec. Eng. | 98 | CS-101 | 1 | Fall | 2017 | C- |
| 98765 | Bourikas | Elec. Eng. | 98 | CS-315 | 1 | Spring | 2018 | B |
| 98988 | Tanaka | Biology | 120 | BIO-101 | 1 | Summer | 2017 | A |
| 98988 | Tanaka | Biology | 120 | BIO-301 | 1 | Summer | 2018 | NULL |
+-------+----------+------------+----------+-----------+--------+----------+------+-------+
22 rows in set (0.00 sec)