MySQL多表查询与左连接、右连接、内连接、全连接-摩杜云开发者社区

原文链接：https://zhuanlan.zhihu.com/p/455111363?utm_id=0

1.为什么要用连接（join）

因为大部分情况下，要符合数据库设计规范，数据不可能集中在同一张表里，那样的话会产生数据冗余，但是分成多张表会造成取数比较麻烦，join（连接）就是为解决上述问题的一种语法。

2.连接种类和语法

内连接：inner join，最常见的一种连接方式（最常用，查询效率最高）
左连接：也叫左外连接（left [outer] join）
右连接：也叫右外连接（right [outer] join）
全连接：full [outer] join ，MySQL不能直接支持。语法：

select table1.c1, table2.c2 
from table1
inner|left|right [outer] join table2 
on condition

下面以经典的学生查询数据集四张表为例，演示MySQL中的四种连接方式。这四张表的字段、数据和ER图如下：

字段说明 1.score表：s_id,c_id,score，==多表==，没有主键 2.course表：c_id,c_name,t_id，==一表==，c_id是主键，对应score表中的c_id字段 3.student表：s_id,s_name,s_age,s_sex，==一表==，s_id是主键，对应score表中的s_id字段 4.teacher表：t_id,t_name，==一表==，t_id是主键，对应course表中的t_id字段 ==多表一般是主表，主要存储数据的地方，每个字段都可能存在重复值，没有主键，无法根据某个字段定位到准确的记录；一表是从表，主要存储辅助数据，通过主键与主表连接，存储的记录是不重复的，可通过主键定位到记录。==

3.左连接（left join）

左连接：left [outer] join,==左连接从左表(t1)取出所有记录,与右表(t2)匹配。如果没有匹配，以null值代表右边表的列==。outer 可以不写，默认情况下不写outer关键字语法:

select t1.c1, t2.c2 
from t1 
left join t2 
on t1.c3 = t2.c3

上面表结构显示学生信息存在student表中，学生分数存在score表中，我想知道每个学生每科成绩和个人信息，显然这里主表是score表，因为它存储每科每个学生成绩，每个学生对应多个成绩，如果是student表的话每个人只有一条记录，没有办法表示每科成绩。这里我们使用左连接，score表为左表，student表为右表。 ++左表score表写在left join前面是主表，因此左表的数据记录全部会出现在结果集中，而右表student中的记录如果在主表中没找到匹配，就不会出现在结果集中++，上面的s_id=8的记录就没有出现在最终结果中。这里的重点是用什么连接，需要考虑好哪个是主表，哪个是从表。假如还是使用左连接，但是表的顺序互相换一下，结果就会完全不同：

这里因为student表写在前面，所以是主表，最后的结果集student表包含student表所有数据；而score表中没有s_id=8的记录，但是s_id=8在主表中必须出现，所以用null填空。

左连接还有一种情况，就是我只需要取出左表中不在右表的部分数据：语法：

select t1.c1, t2.c2 
from t1 
left join t2 
on t1.c3 = t2.c3
where t1.c3 is null

4.右连接（right join）

右连接：right [outer] join，==右连接从右表(t2)取出所有记录，与左表(t1)匹配。如果没有匹配，以null值代表左边表的列==。语法:

select t1.c1, t2.c2 
from t1 
right join t2 
on t1.c3 = t2.c3

实际上，右连接取出的结果和左连接取出的结果是一样的，唯一的不同时字段顺序不同，两者的字段顺序是相反的，左连接的左表结果在右连接的右边，左连接的右表结果在右连接的左边；而对于每种连接来说，哪张表写在前面，哪张表的字段默认就会出现在结果集的左边（select后指定字段的情况除外）。右连接还有一种情况，就是我只需要取出右表中不在左表的部分数据：

select t1.c1, t2.c2 
from t1 
right join t2 
on t1.c3 = t2.c3
where t2.c3 is null

5.内连接（inner join）

内连接，也叫等值连接， ==inner join得出同时存在t1表和t2表的数据集，通俗一点说就是求两个表的交集==。语法：

select t1.c1, t2.c2 
from t1 
inner join t2 
on t1.c3 = t2.c3

与左连接和右连接不同，内连接的代码中，“inner join”两边的t1表和t2表的位置可以互换，结果都一样。上面的s_id=8的记录没有出现在score表中所以就不是交集，被排除。还需要注意的是，通常为了代码简洁，会给表和字段都起别名（alia)，别名一般不能重复，如果结果集中有两张表的共同字段，则必须给相同字段起别名，如果查询的结果（衍生表）作为子查询，则必须给衍生表起别名。表别名直接跟在字段或者表名后面，中间可以加“as"关键字，也可以不加。

6.全连接（full join）

MySQL暂不支持这种语句，不过可以使用==union==将两个结果集“堆一起”，利用左连接，右连接分两次将数据取出，然后用union将数据合并去重。

select t1.c1, t2.c2 
from t1 
left join t2 
on t1.c3 = t2.c3
union
select t1.c1, t2.c2 
from t1 
right join t2 
on t1.c3 = t2.c3

全连接还有另一种情况除了左表和右表不相交的部分，其他的数据都取出：

select t1.c1, t2.c2 
from t1 
left join t2 
on t1.c3 = t2.c3
union
select t1.c1, t2.c2 
from t1 
right join t2 
on t1.c3 = t2.c3
where t1.c3 is null or t2.c3 is null

7.交叉连接（（cross join）

交叉连接：cross join，==返回两个表的笛卡尔乘积，作用就是计算两个表之间每个可能的组合，结果集中的记录数等于两张表各自记录数的乘积==。如果看了上一篇“MySQL的书写顺序和执行顺序”，就可以知道多表查询第一步就是做笛卡尔积产生虚拟表。

语法：

#写法一
select t1.c1, t2.c2 
from t1 
cross join t2 
#写法二
select t1.c1, t2.c2 
from t1,t2

最后汇总一下七种连接方式（交叉连接不好画，可以通过描述想象一下）：