MySQL 连接 通过实例总结详解 笛卡尔积，自然连接，内连接，外连接

MySQL 连接

表结构：
以下是使用到的两张表instructor和teaches，我们希望通过属性ID进行连接

mysql> select * from instructor;
+-------+------------+------------+----------+
| ID    | name       | dept_name  | salary   |
+-------+------------+------------+----------+
| 10101 | Srinivasan | Comp. Sci. | 65000.00 |
| 12121 | Wu         | Finance    | 90000.00 |
| 15151 | Mozart     | Music      | 40000.00 |
| 22222 | Einstein   | Physics    | 95000.00 |
| 32343 | El Said    | History    | 60000.00 |
| 33456 | Gold       | Physics    | 87000.00 |
| 45565 | Katz       | Comp. Sci. | 75000.00 |
| 58583 | Califieri  | History    | 62000.00 |
| 76543 | Singh      | Finance    | 80000.00 |
| 76766 | Crick      | Biology    | 72000.00 |
| 83821 | Brandt     | Comp. Sci. | 92000.00 |
| 98345 | Kim        | Elec. Eng. | 80000.00 |
+-------+------------+------------+----------+
12 rows in set (0.00 sec)

mysql> select * from teaches;
+-------+-----------+--------+----------+------+
| ID    | course_id | sec_id | semester | year |
+-------+-----------+--------+----------+------+
| 76766 | BIO-101   | 1      | Summer   | 2009 |
| 76766 | BIO-301   | 1      | Summer   | 2010 |
| 10101 | CS-101    | 1      | Fall     | 2009 |
| 45565 | CS-101    | 1      | Spring   | 2010 |
| 83821 | CS-190    | 1      | Spring   | 2009 |
| 83821 | CS-190    | 2      | Spring   | 2009 |
| 10101 | CS-315    | 1      | Spring   | 2010 |
| 45565 | CS-319    | 1      | Spring   | 2010 |
| 83821 | CS-319    | 2      | Spring   | 2010 |
| 10101 | CS-347    | 1      | Fall     | 2009 |
| 98345 | EE-181    | 1      | Spring   | 2009 |
| 12121 | FIN-201   | 1      | Spring   | 2010 |
| 32343 | HIS-351   | 1      | Spring   | 2010 |
| 15151 | MU-199    | 1      | Spring   | 2010 |
| 22222 | PHY-101   | 1      | Fall     | 2009 |
+-------+-----------+--------+----------+------+
15 rows in set (0.00 sec)

笛卡尔积

instructor X teaches

产生的结果最多，排列出所有可能组合，无null，连接属性ID出现2次。

mysql> select * from instructor, teaches;
+-------+------------+------------+----------+-------+-----------+--------+----------+------+
| ID    | name       | dept_name  | salary   | ID    | course_id | sec_id | semester | year |
+-------+------------+------------+----------+-------+-----------+--------+----------+------+
| 10101 | Srinivasan | Comp. Sci. | 65000.00 | 76766 | BIO-101   | 1      | Summer   | 2009 |
| 12121 | Wu         | Finance    | 90000.00 | 76766 | BIO-101   | 1      | Summer   | 2009 |
| 15151 | Mozart     | Music      | 40000.00 | 76766 | BIO-101   | 1      | Summer   | 2009 |
| 22222 | Einstein   | Physics    | 95000.00 | 76766 | BIO-101   | 1      | Summer   | 2009 |
| 32343 | El Said    | History    | 60000.00 | 76766 | BIO-101   | 1      | Summer   | 2009 |
| 33456 | Gold       | Physics    | 87000.00 | 76766 | BIO-101   | 1      | Summer   | 2009 |