01-23 09:10 阅读 443

关注

彻底理解内连接（inner join）与外连接(outer join)区别

MySQL包含两种联接，分别是内连接(inner join)和外连接(out join),但我们又同时听说过左连接，交叉连接等术语，本文旨在总结这些术语之间的关系。

1. 内连接

首先说明内连接的一个重要性质：内连接查询结果与表的顺序无关 （当然顺序可能会发生变化，但是对应关系绝对不会错乱！！！）

1.1 交叉连接（cross join）

当然，他还有其他的名字，比如：笛卡尔积，交叉积，还有最奇怪的名字“没有连接”（no join）

使用下列命令同时查询玩具表的toy列和男孩表的boy列，得到的结果就是交叉连接

SELECT t.toy,b.boy FROM toys AS t CROSS JOIN boys AS b; 复制代码

其中，CROSS JOIN可以省略，简写为

SELECT t.toy,b.boy FROM toys AS t， boys AS b; 复制代码

交叉连接回把第一张表的每个值与第二张表的每个值进行匹配，结果如下

交叉连接是内连接的一种，你又可以把内连接看作是通过查询中的条件过滤掉某些结果数据行之后的交叉连接。

1.2 相等连接

我们假设每个男孩子都又一个玩具，表之间是一对一的关系，toy_id是外键，数据库表如下图

我们想找到每个男孩儿拥有什么玩具，只需要将boys表中的toy_id和toys中的主键进行比对，就会得到结果

SELECT boys.boy,toys.toy FROM boys INNER JOIN toys ON boys.toy_id=toys.toy_id; 复制代码

1.3 不等连接

我们继续沿用1.2中的表结构，如果我们想找到每个男孩儿没有的玩具，这时候我们可以使用不等连接（说白了就是=换成<>,其他没有什么区别）

SELECT boys.boy,toys.toy FROM boys INNER JOIN toys ON boys.toy_id<>toys.toy_id ORDER BY boys.boy; 复制代码

1.4 自然连接

继续沿用1.2的表结构。。。。。

注意：自然连接只有在连接的列在两张表中的名称都相同时才会有用

其实，自然连接就是自动识别相同列的相等连接

SELECT boys.boy,toys.toy FROM boys NATURAL JOIN toys ORDER BY boys.boy; 复制代码

得到的结果和1.2中的结果完全一样（顺序可能不同）

2. 外连接

首先说明外连接不同于内连接的一个性质：外连接查询与表的顺序有关

2.1 左外连接

LEFT OUTER JOIN（左外连接）接收左表的所有行，并用这些行与右表进行匹配

当左表与右表具有一对多的关系时，左外连接特别有用。我们仍然使用之前的表结构

现在我们利用左外连接找出每个男孩拥有的玩具

SELECT b.boy,t.toy FROM boys b LEFT OUTER JOIN toys t ON b.toy_id=t.toy_id; 复制代码

LEFT OUTER JOIN左边的表boys我们称为左表，右边的toys称为右表，所以LEFT OUTER JOIN会取得左表boys的所有行和右表的toys的行进行匹配，结果如下

我们的查询结果和使用内连接时一样，难道说内连接和外连接没区别吗？怎么可能！！！接下来我们改变一下左表boys的表结构

我们向boys中新添加了一个Andy，把他的toy_id设置为6

注意，6在toys表中没有对应的玩具，接下来再次运行上述程序

我们发现居然出现了一个NULL，NULL的出现是要告诉我们右表toys中没有与左表boys中的Andy相匹配的行，也就是说

外连接一定会提供数据行，无论还行能否在另一个表中找出相匹配的行

接着做个实验，我们调换左表和右表的顺序

SELECT b.boy,t.toy FROM toys t LEFT OUTER JOIN boys b ON b.toy_id=t.toy_id; 复制代码

结论：出现NULL的列总是右表中的列

以下时左外连接的实际匹配过程

toys中的玩具hula hoop与boys中的Davey的记录比对，toys.toys_is=1,boys.toy_is=3不匹配

toys中的玩具hula hoop与boys中的Bobby的记录比对，toys.toys_is=1,boys.toy_is=5不匹配

toys中的玩具hula hoop与boys中的Beaver的记录比对，toys.toys_is=1,boys.toy_is=2不匹配

toys中的玩具hula hoop与boys中的Richie的记录比对，toys.toys_is=1,boys.toy_is=1成功匹配

.....................省略toy_id=2,3的匹配过程

toys中的玩具harmonica与boys中的Davey的记录比对，toys.toys_is=4,boys.toy_is=3不匹配

toys中的玩具harmonica与boys中的Bobby的记录比对，toys.toys_is=4,boys.toy_is=5不匹配

toys中的玩具harmonica与boys中的Beaver的记录比对，toys.toys_is=4,boys.toy_is=2不匹配

toys中的玩具harmonica与boys中的Richie的记录比对，toys.toys_is=4,boys.toy_is=1不匹配

右表查找完毕，没有匹配项，因此创建带有NULL值的行来对应harmonica

...................省略baseball cards的匹配过程

2.2 右外连接

与左外连接完全相同，只不过是用右表来评价左表

此外：RIGHT OUTER JOIN左侧的表为右表！！！！！

这里只简单举一个例子，具体内容参考左外连接

SELECT b.boy,t.toy FROM toys t RIGHT OUTER JOIN boys b ON b.toy_id=t.toy_id; 复制代码

上述代码等同于

SELECT b.boy,t.toy FROM boys b LEFT OUTER JOIN toys t ON b.toy_id=t.toy_id; 复制代码

这两种写法都是都把toys作为右表，把boys作为左表

实验的表结构如下：

结果：

在实际运用过程中，我们一般倾向于只使用一种，另一种简单了解就好。

作者：蝉沐风
链接：https://juejin.cn/post/7056196221957832718