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 | | 45565 | Katz | Comp. Sci. | 75000.00 | 76766 | BIO-101 | 1 | Summer | 2009 | | 58583 | Califieri | History | 62000.00 | 76766 | BIO-101 | 1 | Summer | 2009 | | 76543 | Singh | Finance | 80000.00 | 76766 | BIO-101 | 1 | Summer | 2009 | | 76766 | Crick | Biology | 72000.00 | 76766 | BIO-101 | 1 | Summer | 2009 | | 83821 | Brandt | Comp. Sci. | 92000.00 | 76766 | BIO-101 | 1 | Summer | 2009 | | 98345 | Kim | Elec. Eng. | 80000.00 | 76766 | BIO-101 | 1 | Summer | 2009 | | 10101 | Srinivasan | Comp. Sci. | 65000.00 | 76766 | BIO-301 | 1 | Summer | 2010 | | 12121 | Wu | Finance | 90000.00 | 76766 | BIO-301 | 1 | Summer | 2010 | | 15151 | Mozart | Music | 40000.00 | 76766 | BIO-301 | 1 | Summer | 2010 | | 22222 | Einstein | Physics | 95000.00 | 76766 | BIO-301 | 1 | Summer | 2010 | | 32343 | El Said | History | 60000.00 | 76766 | BIO-301 | 1 | Summer | 2010 | | 33456 | Gold | Physics | 87000.00 | 76766 | BIO-301 | 1 | Summer | 2010 | | 45565 | Katz | Comp. Sci. | 75000.00 | 76766 | BIO-301 | 1 | Summer | 2010 | | 58583 | Califieri | History | 62000.00 | 76766 | BIO-301 | 1 | Summer | 2010 | | 76543 | Singh | Finance | 80000.00 | 76766 | BIO-301 | 1 | Summer | 2010 | | 76766 | Crick | Biology | 72000.00 | 76766 | BIO-301 | 1 | Summer | 2010 | | 83821 | Brandt | Comp. Sci. | 92000.00 | 76766 | BIO-301 | 1 | Summer | 2010 | | 98345 | Kim | Elec. Eng. | 80000.00 | 76766 | BIO-301 | 1 | Summer | 2010 | | 10101 | Srinivasan | Comp. Sci. | 65000.00 | 10101 | CS-101 | 1 | Fall | 2009 | | 12121 | Wu | Finance | 90000.00 | 10101 | CS-101 | 1 | Fall | 2009 | | 15151 | Mozart | Music | 40000.00 | 10101 | CS-101 | 1 | Fall | 2009 | | 22222 | Einstein | Physics | 95000.00 | 10101 | CS-101 | 1 | Fall | 2009 | | 32343 | El Said | History | 60000.00 | 10101 | CS-101 | 1 | Fall | 2009 | | 33456 | Gold | Physics | 87000.00 | 10101 | CS-101 | 1 | Fall | 2009 | ... 180 rows in set (0.00 sec)

使用where限制连接属性，产生结果ID出现2次，查询到的结果除此与自然连接相同。

mysql> select * from instructor, teaches where instructor.id = teaches.id; +-------+------------+------------+----------+-------+-----------+--------+----------+------+ | ID | name | dept_name | salary | ID | course_id | sec_id | semester | year | +-------+------------+------------+----------+-------+-----------+--------+----------+------+ | 10101 | Srinivasan | Comp. Sci. | 65000.00 | 10101 | CS-101 | 1 | Fall | 2009 | | 10101 | Srinivasan | Comp. Sci. | 65000.00 | 10101 | CS-315 | 1 | Spring | 2010 | | 10101 | Srinivasan | Comp. Sci. | 65000.00 | 10101 | CS-347 | 1 | Fall | 2009 | | 12121 | Wu | Finance | 90000.00 | 12121 | FIN-201 | 1 | Spring | 2010 | | 15151 | Mozart | Music | 40000.00 | 15151 | MU-199 | 1 | Spring | 2010 | | 22222 | Einstein | Physics | 95000.00 | 22222 | PHY-101 | 1 | Fall | 2009 | | 32343 | El Said | History | 60000.00 | 32343 | HIS-351 | 1 | Spring | 2010 | | 45565 | Katz | Comp. Sci. | 75000.00 | 45565 | CS-101 | 1 | Spring | 2010 | | 45565 | Katz | Comp. Sci. | 75000.00 | 45565 | CS-319 | 1 | Spring | 2010 | | 76766 | Crick | Biology | 72000.00 | 76766 | BIO-101 | 1 | Summer | 2009 | | 76766 | Crick | Biology | 72000.00 | 76766 | BIO-301 | 1 | Summer | 2010 | | 83821 | Brandt | Comp. Sci. | 92000.00 | 83821 | CS-190 | 1 | Spring | 2009 | | 83821 | Brandt | Comp. Sci. | 92000.00 | 83821 | CS-190 | 2 | Spring | 2009 | | 83821 | Brandt | Comp. Sci. | 92000.00 | 83821 | CS-319 | 2 | Spring | 2010 | | 98345 | Kim | Elec. Eng. | 80000.00 | 98345 | EE-181 | 1 | Spring | 2009 | +-------+------------+------------+----------+-------+-----------+--------+----------+------+ 15 rows in set (0.00 sec)

自然连接

只考虑两个关系中在共同属性上取值相同的元组对。

ID只出现1次，无nulll。

不需要使用修饰词限制连接属性。

mysql> select * from instructor natural join teaches; +-------+------------+------------+----------+-----------+--------+----------+------+ | ID | name | dept_name | salary | course_id | sec_id | semester | year | +-------+------------+------------+----------+-----------+--------+----------+------+ | 10101 | Srinivasan | Comp. Sci. | 65000.00 | CS-101 | 1 | Fall | 2009 | | 10101 | Srinivasan | Comp. Sci. | 65000.00 | CS-315 | 1 | Spring | 2010 | | 10101 | Srinivasan | Comp. Sci. | 65000.00 | CS-347 | 1 | Fall | 2009 | | 12121 | Wu | Finance | 90000.00 | FIN-201 | 1 | Spring | 2010 | | 15151 | Mozart | Music | 40000.00 | MU-199 | 1 | Spring | 2010 | | 22222 | Einstein | Physics | 95000.00 | PHY-101 | 1 | Fall | 2009 | | 32343 | El Said | History | 60000.00 | HIS-351 | 1 | Spring | 2010 | | 45565 | Katz | Comp. Sci. | 75000.00 | CS-101 | 1 | Spring | 2010 | | 45565 | Katz | Comp. Sci. | 75000.00 | CS-319 | 1 | Spring | 2010 | | 76766 | Crick | Biology | 72000.00 | BIO-101 | 1 | Summer | 2009 | | 76766 | Crick | Biology | 72000.00 | BIO-301 | 1 | Summer | 2010 | | 83821 | Brandt | Comp. Sci. | 92000.00 | CS-190 | 1 | Spring | 2009 | | 83821 | Brandt | Comp. Sci. | 92000.00 | CS-190 | 2 | Spring | 2009 | | 83821 | Brandt | Comp. Sci. | 92000.00 | CS-319 | 2 | Spring | 2010 | | 98345 | Kim | Elec. Eng. | 80000.00 | EE-181 | 1 | Spring | 2009 | +-------+------------+------------+----------+-----------+--------+----------+------+ 15 rows in set (0.00 sec)

内连接

默认join = inner join

如select * from instructor join teaches;产生结果与笛卡尔积相同，ID出现2次

需要用using 指定连接属性，则ID出现1次

mysql> select * from instructor join teaches using (ID); +-------+------------+------------+----------+-----------+--------+----------+------+ | ID | name | dept_name | salary | course_id | sec_id | semester | year | +-------+------------+------------+----------+-----------+--------+----------+------+ | 10101 | Srinivasan | Comp. Sci. | 65000.00 | CS-101 | 1 | Fall | 2009 | | 10101 | Srinivasan | Comp. Sci. | 65000.00 | CS-315 | 1 | Spring | 2010 | | 10101 | Srinivasan | Comp. Sci. | 65000.00 | CS-347 | 1 | Fall | 2009 | | 12121 | Wu | Finance | 90000.00 | FIN-201 | 1 | Spring | 2010 | | 15151 | Mozart | Music | 40000.00 | MU-199 | 1 | Spring | 2010 | | 22222 | Einstein | Physics | 95000.00 | PHY-101 | 1 | Fall | 2009 | | 32343 | El Said | History | 60000.00 | HIS-351 | 1 | Spring | 2010 | | 45565 | Katz | Comp. Sci. | 75000.00 | CS-101 | 1 | Spring | 2010 | | 45565 | Katz | Comp. Sci. | 75000.00 | CS-319 | 1 | Spring | 2010 | | 76766 | Crick | Biology | 72000.00 | BIO-101 | 1 | Summer | 2009 | | 76766 | Crick | Biology | 72000.00 | BIO-301 | 1 | Summer | 2010 | | 83821 | Brandt | Comp. Sci. | 92000.00 | CS-190 | 1 | Spring | 2009 | | 83821 | Brandt | Comp. Sci. | 92000.00 | CS-190 | 2 | Spring | 2009 | | 83821 | Brandt | Comp. Sci. | 92000.00 | CS-319 | 2 | Spring | 2010 | | 98345 | Kim | Elec. Eng. | 80000.00 | EE-181 | 1 | Spring | 2009 | +-------+------------+------------+----------+-----------+--------+----------+------+ 15 rows in set (0.00 sec)

外连接

full/left/right outer join 全/左/右外连接

会产生null，产生2次ID，需要用on指定连接属性

mysql> select * from instructor left outer join teaches on instructor.id = teaches.id; +-------+------------+------------+----------+-------+-----------+--------+----------+------+ | ID | name | dept_name | salary | ID | course_id | sec_id | semester | year | +-------+------------+------------+----------+-------+-----------+--------+----------+------+ | 10101 | Srinivasan | Comp. Sci. | 65000.00 | 10101 | CS-101 | 1 | Fall | 2009 | | 10101 | Srinivasan | Comp. Sci. | 65000.00 | 10101 | CS-315 | 1 | Spring | 2010 | | 10101 | Srinivasan | Comp. Sci. | 65000.00 | 10101 | CS-347 | 1 | Fall | 2009 | | 12121 | Wu | Finance | 90000.00 | 12121 | FIN-201 | 1 | Spring | 2010 | | 15151 | Mozart | Music | 40000.00 | 15151 | MU-199 | 1 | Spring | 2010 | | 22222 | Einstein | Physics | 95000.00 | 22222 | PHY-101 | 1 | Fall | 2009 | | 32343 | El Said | History | 60000.00 | 32343 | HIS-351 | 1 | Spring | 2010 | | 33456 | Gold | Physics | 87000.00 | NULL | NULL | NULL | NULL | NULL | | 45565 | Katz | Comp. Sci. | 75000.00 | 45565 | CS-101 | 1 | Spring | 2010 | | 45565 | Katz | Comp. Sci. | 75000.00 | 45565 | CS-319 | 1 | Spring | 2010 | | 58583 | Califieri | History | 62000.00 | NULL | NULL | NULL | NULL | NULL | | 76543 | Singh | Finance | 80000.00 | NULL | NULL | NULL | NULL | NULL | | 76766 | Crick | Biology | 72000.00 | 76766 | BIO-101 | 1 | Summer | 2009 | | 76766 | Crick | Biology | 72000.00 | 76766 | BIO-301 | 1 | Summer | 2010 | | 83821 | Brandt | Comp. Sci. | 92000.00 | 83821 | CS-190 | 1 | Spring | 2009 | | 83821 | Brandt | Comp. Sci. | 92000.00 | 83821 | CS-190 | 2 | Spring | 2009 | | 83821 | Brandt | Comp. Sci. | 92000.00 | 83821 | CS-319 | 2 | Spring | 2010 | | 98345 | Kim | Elec. Eng. | 80000.00 | 98345 | EE-181 | 1 | Spring | 2009 | +-------+------------+------------+----------+-------+-----------+--------+----------+------+ 18 rows in set (0.00 sec)

使用on true where ...，则上结果中的null属性被去掉，其他不变，依然ID出现两次。

mysql> select * from instructor left outer join teaches on true where instructor.id = teaches.id; +-------+------------+------------+----------+-------+-----------+--------+----------+------+ | ID | name | dept_name | salary | ID | course_id | sec_id | semester | year | +-------+------------+------------+----------+-------+-----------+--------+----------+------+ | 10101 | Srinivasan | Comp. Sci. | 65000.00 | 10101 | CS-101 | 1 | Fall | 2009 | | 10101 | Srinivasan | Comp. Sci. | 65000.00 | 10101 | CS-315 | 1 | Spring | 2010 | | 10101 | Srinivasan | Comp. Sci. | 65000.00 | 10101 | CS-347 | 1 | Fall | 2009 | | 12121 | Wu | Finance | 90000.00 | 12121 | FIN-201 | 1 | Spring | 2010 | | 15151 | Mozart | Music | 40000.00 | 15151 | MU-199 | 1 | Spring | 2010 | | 22222 | Einstein | Physics | 95000.00 | 22222 | PHY-101 | 1 | Fall | 2009 | | 32343 | El Said | History | 60000.00 | 32343 | HIS-351 | 1 | Spring | 2010 | | 45565 | Katz | Comp. Sci. | 75000.00 | 45565 | CS-101 | 1 | Spring | 2010 | | 45565 | Katz | Comp. Sci. | 75000.00 | 45565 | CS-319 | 1 | Spring | 2010 | | 76766 | Crick | Biology | 72000.00 | 76766 | BIO-101 | 1 | Summer | 2009 | | 76766 | Crick | Biology | 72000.00 | 76766 | BIO-301 | 1 | Summer | 2010 | | 83821 | Brandt | Comp. Sci. | 92000.00 | 83821 | CS-190 | 1 | Spring | 2009 | | 83821 | Brandt | Comp. Sci. | 92000.00 | 83821 | CS-190 | 2 | Spring | 2009 | | 83821 | Brandt | Comp. Sci. | 92000.00 | 83821 | CS-319 | 2 | Spring | 2010 | | 98345 | Kim | Elec. Eng. | 80000.00 | 98345 | EE-181 | 1 | Spring | 2009 | +-------+------------+------------+----------+-------+-----------+--------+----------+------+ 15 rows in set (0.00 sec)

总结

下表中，任意的连接类型可以与右侧任意的连接条件组合，where显然不是连接条件。

连接类型连接条件inner joinnaturalleft outer joinon right outer joinusing(A1, A2, …, An)full outer join