转载

三言两语：SQL 连接（join）

本文将从连接的理论和语法讲起，结合具体的例子，详细分析 SQL 连接。

之前对数据库的连接操作似懂非懂，大概知道是什么东西，但是面试笔试的时候被虐成渣，讲不清连接到底是什么。吃一堑，长一智。这就是所谓的似懂非懂，只是单纯的看书是没用的，只有亲自动手做实验才能彻底理解什么是连接。

连接类型与条件

SQL 中每一种连接操作都包括一个连接类型和连接条件。

连接类型

连接类型决定了如何处理连接条件不匹配的记录。

连接类型	返回结果
inner join	只包含左右表中满足连接条件的记录
left outer join	在内连接的基础上，加入左表中不与右表匹配的记录，剩余字段赋值为null
right outer join	在内连接的基础上，加入右表中不与左表匹配的记录，剩余字段赋值为null
full outer join	左外连接和右外连接的组合。
cross join	等价于没有连接条件的内连接（即产生笛卡尔乘积）

关键字 inner 和 outer 是可选的，因为根据连接类型的其余内容我们可以判断出连接是内连接和外连接。简单来说就是：除了单独的 join 是内连接，其他都是外连接。

对外连接来说，连接条件是 必须的 ；但对内连接来说，连接条件是 可选的 （如果省略，将产生笛卡尔积）。

连接条件

连接条件决定两个表中哪些记录互相匹配以及连接结果中出现哪些属性。

连接条件	修饰位置	语义
natural	连接类型之前	连接两个表之间的所有公共字段相等的记录，合并相同的列
on <谓词>	连接类型之后	连接符合谓词的记录，不合并相同的列
using(A1, A2,…,An)	连接类型之后	natural 语义的子集，只连接两个表中（A1,A2,..An)的公共字段，合并相同的列

从上面的描述可以看到：连接操作是连接类型和连接条件的组合，只有在这个前提下才能真正的理解连接的功能。

FQA

例子中使用到的表

student

+----+--------+ | id | name   | +----+--------+ |  1 | 张三   | |  2 | 李四   | |  3 | 王二   | |  4 | 初一   | |  5 | 初二   | +----+--------+

teacher

+----+-----------+ | id | name      | +----+-----------+ |  1 | 王老师    | |  2 | 李老师    | |  3 | 张老师    | |  4 | 肖老师    | |  5 | NULL      | |  6 | 陈老师    | +----+-----------+

course

+----+--------+------+ | id | cname  | tid  | +----+--------+------+ |  1 | 数学   |    1 | |  2 | 英语   |    2 | |  3 | 语文   |    3 | |  4 | 体育   |    1 | |  5 | 物理   | NULL | +----+--------+------+

student_course

+-----+-----+ | sid | cid | +-----+-----+ |   1 |   1 | |   1 |   2 | |   1 |   3 | |   2 |   1 | |   2 |   4 | |   3 |   5 | |   3 |   6 | |   4 |   4 | +-----+-----+

内连接之后的结果集数量是多少？等于左表或者右表中记录的数量吗？

内连接 teacher course 的结果

select * from teacher inner join course on teacher.id = course.tid; +----+-----------+----+--------+------+ | id | name      | id | cname  | tid  | +----+-----------+----+--------+------+ |  1 | 王老师    |  1 | 数学   |    1 | |  2 | 李老师    |  2 | 英语   |    2 | |  3 | 张老师    |  3 | 语文   |    3 | |  1 | 王老师    |  4 | 体育   |    1 | +----+-----------+----+--------+------+

可以发现，王老师同时教数学和体育，因此左表中王老师匹配了右表中两条记录，物理没有老师教，所以没有出现在结果中。说明 内连接的结果集数量等于左右表中匹配记录的数量 。

左连接之后的结果集数量是多少？等于左表的记录数量吗？

左连接 teacher course 的结果

select * from teacher left join course on teacher.id = course.tid; +----+-----------+------+--------+------+ | id | name      | id   | cname  | tid  | +----+-----------+------+--------+------+ |  1 | 王老师    |    1 | 数学   |    1 | |  2 | 李老师    |    2 | 英语   |    2 | |  3 | 张老师    |    3 | 语文   |    3 | |  1 | 王老师    |    4 | 体育   |    1 | |  4 | 肖老师    | NULL | NULL   | NULL | |  5 | NULL      | NULL | NULL   | NULL | |  6 | 陈老师    | NULL | NULL   | NULL | +----+-----------+------+--------+------+

可以看到，没有教授课程的老师也出现在结果中，对应的字段都为NULL。说明结果集的数量并不等于左表记录的数量，因为两个表直接不是一对一的关系。其数量应该等于 内连接的结果集数量加上左表中不匹配的记录数量 。

Mysql 中不支持 `full outer join`

可以通过 union 操作模拟。

SELECT * FROM teacher LEFT JOIN course ON teacher.id = course.tid UNION SELECT * FROM teacher RIGHT JOIN course ON teacher.id = course.id;  +------+-----------+------+--------+------+ | id   | name      | id   | cname  | tid  | +------+-----------+------+--------+------+ |    1 | 王老师    |    1 | 数学   |    1 | |    2 | 李老师    |    2 | 英语   |    2 | |    3 | 张老师    |    3 | 语文   |    3 | |    1 | 王老师    |    4 | 体育   |    1 | |    4 | 肖老师    | NULL | NULL   | NULL | |    5 | NULL      | NULL | NULL   | NULL | |    6 | 陈老师    | NULL | NULL   | NULL | |    4 | 肖老师    |    4 | 体育   |    1 | |    5 | NULL      |    5 | 物理   | NULL | +------+-----------+------+--------+------+

多表连接问题

考虑查出所有学生的课程的记录

select * from student left join student_course on student.id = student_course.sid left join course on student_course.cid = course.id;  +----+--------+------+------+------+--------+------+ | id | name   | sid  | cid  | id   | cname  | tid  | +----+--------+------+------+------+--------+------+ |  1 | 张三   |    1 |    1 |    1 | 数学   |    1 | |  2 | 李四   |    2 |    1 |    1 | 数学   |    1 | |  1 | 张三   |    1 |    2 |    2 | 英语   |    2 | |  1 | 张三   |    1 |    3 |    3 | 语文   |    3 | |  2 | 李四   |    2 |    4 |    4 | 体育   |    1 | |  4 | 初一   |    4 |    4 |    4 | 体育   |    1 | |  3 | 王二   |    3 |    5 |    5 | 物理   | NULL | |  3 | 王二   |    3 |    6 | NULL | NULL   | NULL | |  5 | 初二   | NULL | NULL | NULL | NULL   | NULL | +----+--------+------+------+------+--------+------+

用学生表连接中间表，再连接课程表可以得到结果。连接操作是针对两个表之间的，所以上面的结果是从左到右，两两连接得到的。