理解mysql之内连接，外连接，全连接

一. 前言

SQL语法，select 语句可以通过连接多个表来检索数据。表与表之间的连接关系可分为内链接，外连接和全连接。我们最常用的是内连接；外连接分为左外连接和右外连接；全连接是一般很少用，它查询的结果集记录数是查询的各个表的记录数的乘积。下面详细介绍这几种连接。

我们创建两个标为例子：

学生（student）表：

 create table student(
    id int primary key,
    name varchar(20) not null
)engine=Innodb default charset=utf8mb4;

课程（course）表:

 create table course(
    id int primary key,
    cname varchar(30) not null
)engine = INNODB default charset = utf8mb4;

学生课程关系表：

create table students_courses(
cid int REFERENCES course(id),
sid int REFERENCES student(id)
);

插入以下数据：

insert into student values(1,"Timmy"),(2,"Smith"),(3,"Jhon"),(4,"Petter");

insert into course values(1,'English'),(2,'Math'),(3,'Music');

insert into course values(1,2),(2,3),(1,1),(2,1);

二. 内连接

内连接是最广泛使用的连接方式，语法标准格式是：

1	select property1,property2,... from t1,t2,... where expr;

例1：查询所有学生和他们选修的课程

1	select s.name,c.cname from student s,course c,students_courses sc where s.id = sc.sid and sc.cid = c.id;

显示结果如下:

| name  | cname   |
|-------|---------|
| Smith | English |
| Jhon  | Math    |
| Timmy | English |
| Timmy | Math    |

三. 左连接、右连接

左连接和右连接统称为外连接，左连接也可叫为左外连接，右连接也可叫为右外连接。

左连接不仅匹配类似前面的行记录，而且还显示左边的表有而右边的表中五匹配的行，对于这样的行，右边表不匹配的列均被显示为NULL。这样每一条匹配的行都从左边的表被选出，如果右边表有一个匹配的行，则被选中，如果不匹配，行仍然被选中，只不过，右边表响应的列在结果集中均设为null。也就是，LEFT JOIN 强制包含左边表的每一行，而不管右边表是否匹配。

标准语法：

1	select property1,property2,... from left_table left join right_table on expr;

例2：

1	select * from student s left join course c on s.id=c.id;

结果如下：

| id | name   | id   | cname   |
|----|--------|------|---------|
|  1 | Timmy  |    1 | English |
|  2 | Smith  |    2 | Math    |
|  3 | Jhon   |    3 | Music   |
|  4 | Petter | NULL | NULL    |

上面的结果，即用左边表的每一行与右边表匹配，如果匹配，则选择到结果集中，如果没有匹配，则结果集中右边表响应的列置为NULL。

为了进一步理解，举出一个有点奇怪的例子，例3：

1	select * from student s left join course c on s.id=1

结果显示：

| id | name   | id   | cname   |
|----|--------|------|---------|
|  1 | Timmy  |    1 | English |
|  1 | Timmy  |    2 | Math    |
|  1 | Timmy  |    3 | Music   |
|  2 | Smith  | NULL | NULL    |
|  3 | Jhon   | NULL | NULL    |
|  4 | Petter | NULL | NULL    |

结果最后三行似乎是你不希望看到的。然而如果只有 ON 子句的条件，那么左边表的每一行都会返回，只是如果没有匹配到右边表（虽然本例没有约束course表）,那结果集中右边表的记录值为NULL。

理解了了上面的内容，那么我们可以结合 WHERE 子句使用，这是一个非常有用的技巧。使用 IS NULL 和 IS NOT NULL操作符可以筛选出我们想要的记录。

例4：基于例2的改造

1	select * from student s left join course c on s.id=c.id where c.id is not null;

结果显示：

| id | name  | id | cname   |
|----|-------|----|---------|
|  1 | Timmy |  1 | English |
|  2 | Smith |  2 | Math    |
|  3 | Jhon  |  3 | Music   |

这结果和【select * from student s,course c where s.id=c.id】的结果是一样的。

右连接和左连接的原理是一样的，举一反三相信也就很容易理解了。

四. 全连接

全连接也叫笛卡尔积连接，看下面的一个例子。

例5-1：

1	select * from student,course;

| id | name   | id | cname   |
|----|--------|----|---------|
|  1 | Timmy  |  1 | English |
|  1 | Timmy  |  2 | Math    |
|  1 | Timmy  |  3 | Music   |
|  2 | Smith  |  1 | English |
|  2 | Smith  |  2 | Math    |
|  2 | Smith  |  3 | Music   |
|  3 | Jhon   |  1 | English |
|  3 | Jhon   |  2 | Math    |
|  3 | Jhon   |  3 | Music   |
|  4 | Petter |  1 | English |
|  4 | Petter |  2 | Math    |
|  4 | Petter |  3 | Music   |

例5-2：

1	select * from course，student;

| id | cname   | id | name   |
|----|---------|----|--------|
|  1 | English |  1 | Timmy  |
|  2 | Math    |  1 | Timmy  |
|  3 | Music   |  1 | Timmy  |
|  1 | English |  2 | Smith  |
|  2 | Math    |  2 | Smith  |
|  3 | Music   |  2 | Smith  |
|  1 | English |  3 | Jhon   |
|  2 | Math    |  3 | Jhon   |
|  3 | Music   |  3 | Jhon   |
|  1 | English |  4 | Petter |
|  2 | Math    |  4 | Petter |
|  3 | Music   |  4 | Petter |

可以看到，结果集中3*4=12行，这就是全连接的结果，输出结果集的字段顺序是按 FROM 子句后面的表来显示的。

全连接一般可以用在预测结果组合的可能性，例如下棋的下一步布局，雌雄繁殖匹配等等。