SQL的几种连接：内连接，外连接，交叉连接

发表于 | 分类于

SQL连接比较常用的连接可以分为内连接，外连接，交叉连接。
表的连接所依据的关系是由where 子句定义的。在实际应用中，用户要实现表的连接一定要依据一定的关系。如果没有指明关系，返回的结果将是连接表中所有符合查询条件数据行的笛卡尔积。
举例来说明，现在建两个表，student 和 teacher，数据如下。

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> select * from student;
+------------+------------+------+------------+----------+
| name       | parents    | sex  | birth      | lesson   |
+------------+------------+------+------------+----------+
| LiLei      | JiangJiang | F    | 2008-09-14 | Math     |
| JiangJiang | Jeon       | F    | 1998-02-14 | Math     |
| HanMei     | JAY        | M    | 2010-07-23 | Math     |
| WangLiLi   | WangJing   | M    | 2009-06-19 | Language |
| Lucy       | JingJE     | M    | 2009-05-28 | Computer |
| Lucy       | Jingee     | M    | 2011-04-28 | History  |
| YangGuang  | YangYun    | F    | 1996-03-24 | History  |
| ZhangYue   | ZhangGuo   | F    | 2007-04-13 | Science  |
+------------+------------+------+------------+----------+

1
2
3
4
5
6
7
8
9
10
mysql> select * from teacher;
+------------+------+-----------+------------+
| name       | sex  | subject   | date       |
+------------+------+-----------+------------+
| ChengChung | F    | Math      | 1990-04-23 |
| ZhangHan   | F    | Language  | 1989-09-21 |
| ZiJin      | M    | History   | 1991-03-12 |
| LILEI      | F    | Computer  | 1976-07-08 |
| QinYu      | M    | Chemistry | 1987-06-13 |
+------------+------+-----------+------------+

内连接(inner join)

使用比较运算符（=,<,>等）根据每个表共有的列的值匹配 两个表中的行。包括等值连接，不等值连接和自然连接。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> select student.name,lesson,teacher.name
    -> from student inner join teacher
    -> on student.lesson = teacher.subject;
+------------+----------+------------+
| name       | lesson   | name       |
+------------+----------+------------+
| LiLei      | Math     | ChengChung |
| JiangJiang | Math     | ChengChung |
| HanMei     | Math     | ChengChung |
| WangLiLi   | Language | ZhangHan   |
| Lucy       | Computer | LILEI      |
| Lucy       | History  | ZiJin      |
| YangGuang  | History  | ZiJin      |
+------------+----------+------------+
7 rows in set (0.00 sec)

其中inner可以省略。
上面查询等价于

1
2
3
mysql> select student.name,lesson,teacher.name
    -> from student, teacher
    -> where student.lesson = teacher.subject;

等值连接

在连接条件中使用等于号=运算符比较连接列的列值，其查询结果中包括重复列。

不等值连接

在连接条件使用等于号以外的其他比较运算符比较被连接的列的列值.

自然连接

natural join将表中具有相同名称的列自动进行记录匹配，自然连接不必指定任何同等连接条件。

1
2
3
4
5
6
mysql> select * from student natural join teacher;
+-------+------+------------+------------+--------+----------+------------+
| name  | sex  | parents    | birth      | lesson | subject  | date       |
+-------+------+------------+------------+--------+----------+------------+
| LiLei | F    | JiangJiang | 2008-09-14 | Math   | Computer | 1976-07-08 |
+-------+------+------------+------------+--------+----------+------------+

返回学生表中name,sex和教师表中 name,sex都一致的结果。
如果我们现在把教师表中LiLei的性别改为M，重新进行自然连接。

1
2
3
4
5
6
mysql> update teacher set sex='M' where name='LILEI';
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from student natural join teacher;
Empty set (0.00 sec)

自然连接自动判断相同名称的列，而后进行匹配，不能人为的指定哪些列被匹配。 因此使用where子句要灵活许多。还有一些数据库并不支持自然连接，例如 sql server。

外连接

SQL的外连接一共有三种，左外连接，右外连接，全外连接。

左外连接

left join以左表为基础，将数据进行连接，将左边没有的对应项列为NULL。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> select s.name as student, lesson, t.name as teacher,subject
    -> from student as s left join teacher as t
    -> on s.lesson = t.subject;
+------------+----------+------------+----------+
| student    | lesson   | teacher    | subject  |
+------------+----------+------------+----------+
| LiLei      | Math     | ChengChung | Math     |
| JiangJiang | Math     | ChengChung | Math     |
| HanMei     | Math     | ChengChung | Math     |
| WangLiLi   | Language | ZhangHan   | Language |
| Lucy       | History  | ZiJin      | History  |
| YangGuang  | History  | ZiJin      | History  |
| Lucy       | Computer | LILEI      | Computer |
| ZhangYue   | Science  | NULL       | NULL     |
+------------+----------+------------+----------+

右外连接

right join以右表为基础，将数据进行连接，将没用的对应项列为NULL。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> select s.name as student, lesson, t.name as teacher,subject
    -> from student as s right join teacher as t
    -> on s.lesson = t.subject;
+------------+----------+------------+-----------+
| student    | lesson   | teacher    | subject   |
+------------+----------+------------+-----------+
| LiLei      | Math     | ChengChung | Math      |
| JiangJiang | Math     | ChengChung | Math      |
| HanMei     | Math     | ChengChung | Math      |
| WangLiLi   | Language | ZhangHan   | Language  |
| Lucy       | Computer | LILEI      | Computer  |
| Lucy       | History  | ZiJin      | History   |
| YangGuang  | History  | ZiJin      | History   |
| NULL       | NULL     | QinYu      | Chemistry |
+------------+----------+------------+-----------+

全外连接

full outer join 完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值。如果表之间有匹配行，则整个结果集行包含基表的数据值。

MySQL 暂不支持全外连接功能。

交叉连接

cross join 返回左表中每一行和右表所有行的组合，也称为笛卡尔积。

1
2
3
mysql> select s.name as student, lesson, t.name as teacher,subject
    -> from student as s cross join teacher as t
    -> order by s.name;

等价于

1
2
3
mysql> select s.name as student, lesson, t.name as teacher,subject
    -> from student as s ,teacher as t
    -> order by s.name;

结果为：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
+------------+----------+------------+-----------+
| student    | lesson   | teacher    | subject   |
+------------+----------+------------+-----------+
| HanMei     | Math     | ZhangHan   | Language  |
| HanMei     | Math     | QinYu      | Chemistry |
| HanMei     | Math     | ZiJin      | History   |
| HanMei     | Math     | ChengChung | Math      |
| HanMei     | Math     | LILEI      | Computer  |
| JiangJiang | Math     | ChengChung | Math      |
| JiangJiang | Math     | LILEI      | Computer  |
| JiangJiang | Math     | ZhangHan   | Language  |
| JiangJiang | Math     | QinYu      | Chemistry |
| JiangJiang | Math     | ZiJin      | History   |
| LiLei      | Math     | ZiJin      | History   |
| LiLei      | Math     | ChengChung | Math      |
| LiLei      | Math     | LILEI      | Computer  |
| LiLei      | Math     | ZhangHan   | Language  |
| LiLei      | Math     | QinYu      | Chemistry |
| Lucy       | Computer | ZiJin      | History   |
| Lucy       | History  | ChengChung | Math      |
| Lucy       | History  | LILEI      | Computer  |
| Lucy       | Computer | ChengChung | Math      |
| Lucy       | Computer | LILEI      | Computer  |
| Lucy       | History  | ZhangHan   | Language  |
| Lucy       | History  | QinYu      | Chemistry |
| Lucy       | Computer | ZhangHan   | Language  |
| Lucy       | Computer | QinYu      | Chemistry |
| Lucy       | History  | ZiJin      | History   |
| WangLiLi   | Language | ZiJin      | History   |
| WangLiLi   | Language | ChengChung | Math      |
| WangLiLi   | Language | LILEI      | Computer  |
| WangLiLi   | Language | ZhangHan   | Language  |
| WangLiLi   | Language | QinYu      | Chemistry |
| YangGuang  | History  | ZhangHan   | Language  |
| YangGuang  | History  | QinYu      | Chemistry |
| YangGuang  | History  | ZiJin      | History   |
| YangGuang  | History  | ChengChung | Math      |
| YangGuang  | History  | LILEI      | Computer  |
| ZhangYue   | Science  | ZiJin      | History   |
| ZhangYue   | Science  | ChengChung | Math      |
| ZhangYue   | Science  | LILEI      | Computer  |
| ZhangYue   | Science  | ZhangHan   | Language  |
| ZhangYue   | Science  | QinYu      | Chemistry |
+------------+----------+------------+-----------+

[1]. 参考: http://www.cnblogs.com/zxlovenet/p/4005256.html
[2]. 参考: http://www.jb51.net/article/39432.htm

如果觉得对您有帮助，就扫我交个朋友吧！