SQL不同横向连接效果实现方法大全

背景介绍

在以MySQL为代表的关系型数据库软件中，不同表间的横向连接(join)是非常重要的内容。SQL能直接达成的效果包括左连接、右连接、内连接。本篇文章将总结一下在SQL数据库中不同连接方式（包括以上三种方式及相应扩展）的实现手段及其效果。

接下来参照的业务探索表格（两张）如下：

表T1:

表T2:

不同连接方法及相关语句

（1）左连接 (LEFT JOIN)

左连接的目的，是将左表的内容全部保留，并将右表的内容按连接字段对左表内容进行匹配，若没有相应匹配字段则用NULL表示；同时右表独有的字段内容不参加匹配。以上面的T1表作为左表，而T2表作为右表，并按照NAME字段进行匹配，可得到左连接的结果如下：

之所以这样匹配的原因在于，表T1作为左表，其所有内容都必须保留（如效果表中的前两列）；而作为右表的表T2中，必须将T2内NAME字段中所有内容进行查看，检查是否有表T1中的NAME字段中完全相同的值，若有则匹配在最终表的同一行后的列，否则T2中相应的字段不被采纳。

表T2中NAME字段的“CCC”在表T1的NAME字段中也有相应的取值，故相关内容需按类似“枚举”的方式作为同一行（即表T2中NAME字段中的“CCC”需要匹配到表T1中所有NAME字段含有“CCC”的一行中）；而左表T1中的其他部分即使没有对应的右表T2补齐的部分，T2相应的部分仍需要书写，但相应部分应被记作NULL（空值）。

对应文氏图应画作：

此案例中对应SQL代码如下：

SELECT * FROM T1 
LEFT JOIN T2
ON T1.NAME= T2.NAME;

(2) 右连接 (RIGHT JOIN)

右连接的横向连接逻辑与左连接类似。此处假设我们继续将T1作为左表，而T2作为右表，同样用两张表各自NAME字段进行连接。可得到相关右连接结果如下：

右连接在这种情况下就将表T2作为主表，而表T1作为副表对其进行匹配，匹配的依据是表T2的NAME字段的值。若表T1中NAME字段的值有表T2中NAME字段的，则匹配在相关表T2的NAME字段的同一行；否则若没有能匹配表T2中NAME字段值的，则在表T2相关字段取值的同一行标以NULL（空值）。其他情况下表T1相关的取值不应采纳，舍去。

对应的文氏图应画作：

此案例对应SQL代码如下：

SELECT * FROM T1
RIGHT JOIN T2
ON T1.NAME= T2.NAME;

(3) 内连接 (INNER JOIN)

与上述方法的逻辑不太类似，内连接的方法类似于数学中求两个集合的交集。此时从逻辑上来说并不区分严格意义上的左表和右表。同样以上述两张表中的字段NAME作为连接依据字段，可得到内连接结果如下：

可以看到，内连接本质上是取连接字段相同的一部分内容进行匹配，即连接字段在两张表中对应的内容必须完全一致才会在一行中展示出来；若连接字段中没有相同的内容，则不会进行任何的连接。

对应的文氏图应画作：

此案例对应SQL代码如下：

SELECT * FROM T1
INNER JOIN T2
ON T1.NAME= T2.NAME;

(4) 左反连接 (LEFT ANTI JOIN)

接下来介绍的是反连接类。反连接通常指的是两张表中其中一张表中的记录，且对于这些记录，在另一张表中没有记录匹配的情况。同样以上述表为示例，表T1作为左表，而表T2作为右表，则左反连接的意思即T1表中的某些记录行，这些记录行在某种连接规则(如此处还是以NAME字段作为连接规则）下在表T2中完全没有对应的行实现匹配（即表T2中匹配过来的记录内容都是NULL）。左反连接在这种情况下的连接效果如下：

可以看到，左反连接的本质是保留左表的某些部分，这些部分需满足在右表中完全没有记录能与之匹配。输出的左反连接效果最直观的表现便是右表所有记录内容都是NULL。

对应的文氏图应画作：

此案例对应SQL代码如下：

SELECT * FROM T1
LEFT JOIN T2
ON T1.NAME= T2.NAME
WHERE T2.ID IS NULL;

这边对比一下左连接，可以发现：左反连接是在左连接的基础之上添加了关于右表主键的一个约束条件，“WHERE T2.ID IS NULL”确保了将左连接情况下左表T1与右表T2能匹配的非空部分舍去。

(5) 右反连接 (RIGHT ANTI JOIN)

右反连接的原理与左反连接类似。我们还是以上述背景为例：左表T1，右表T2，连接字段采用两表中的NAME字段进行连接。此时右反连接的连接效果如下：

可以看到，此时相当于将T2右表作为主表，将其中无法匹配到T1左表记录的行作保留，反之则舍去，然后将这些记录在T1表中作为NULL来补充到每行记录中。

对应的文氏图应画作：

此时对应SQL代码如下：

SELECT * FROM T1
RIGHT JOIN T2
ON T1.NAME= T2.NAME
WHERE T1.ID IS NULL;

对比一下右连接的连接语句不难发现，右反连接是在右连接的基础上对副表T1添加了一个限制条件“WHERE T1.ID IS NULL”实现的，这部分作用和左反连接的限制条件一样，将属于右表T2且能被左表T1能匹配的部分舍去。

（6）全反连接 (FULL ANTI JOIN)

全反连接的意思是，将两张表各自独有的部分一并展示出来。经过上面的基础铺垫，我们不难发现，全反连接实际上就是左反连接与右反连接纵向合并的结果，相当于两部分取并集得到的效果。我们还是以T1作为左表，T2作为右表，连接规则仍然是NAME字段。最终输出效果如下图所示：

可以看到，此处全反连接的输出结果中，完全包含了左右两表中各自独有，而另一张表无法匹配的记录。不存在两张表同时存在匹配内容的记录。

对应的文氏图表示如下：

此时对应的SQL代码如下：

SELECT * FROM T1
LEFT JOIN T2
ON T1.NAME= T2.NAME
WHERE T2.ID IS NULL
UNION ALL
SELECT * FROM T1
RIGHT JOIN T2
ON T1.NAME= T2.NAME
WHERE T1.ID IS NULL;

该SQL代码将全反连接分成了生成左反连接、右反连接两部分，然后在此基础上通过UNION ALL进行纵向合并，达成对左反连接、右反连接两部分取并集的效果。此处采用纵向合并时，利用UNION ALL而非 UNION的原因在于，虽然连接的两表在取全反连接时不可能产生彼此间重复的记录情况，但有时候有些单表内部可能会有重复记录。全反连接的定义决定了对于这些表内的重复记录，我们不应该舍去，这样才能确保两张表中的左反、右反连接部分完全作为全反连接的组成部分，达成类似文氏图中的效果。

（7）全外连接 (FULL OUTER JOIN)

全外连接的想法就是将两张表中的内容完整地取并集。我们还是以上面的两张表T1、T2为例，连接规则依旧参考两张表中的NAME字段。全外连接的效果如下：

对应的文氏图表示如下：

需要注意的是，以MySQL为例，确实支持全连接（FULL OUTER JOIN)，但在某些标准版本中，全连接并不是原生支持的，这就意味着在某些情况下，我们需要利用其他方法间接实现类似效果。这里我们将通过两种方式展示SQL代码：

（a）可以直接使用 full outer join的情况下：

SELECT * FROM T1
FULL OUTER JOIN T2
ON T1.NAME= T2.NAME;

（b）无法直接使用 full outer join的情况下：

SELECT * FROM T1
LEFT JOIN T2
ON T1.NAME= T2.NAME
UNION ALL
SELECT * FROM T1
RIGHT JOIN T2
ON T1.NAME= T2.NAME
WHERE T1.ID IS NULL;

可以看出，直接使用 full outer join比较方便，原理类似于left\right\inner join；而当不能直接使用时，方法时利用一个左连接+右外连接的方法（也可以利用右连接+左外连接 或者 左外连接+内连接+右外连接的方法），实现全部记录的覆盖，最终效果是一致的。

（8）交叉连接 (CROSS JOIN)

交叉连接，也叫笛卡尔积连接、叉积连接。作为一种特殊的横向连接的方法，其连接原理与上述的所有方法稍有不同，是一种类似数学中排列组合的枚举连接方法，且与连接字段无关，得到的结果是两个表的乘积。我们还是以表T1、T2为例，不用考虑连接字段问题，交叉连接的效果如下：

可以看到，该方法实现的就是枚举效果。在原表中，表T1有三行，表T2也有三行，因此经过交叉连接后的表就有3*3=9行。一张表中的每一行都会与另一张表中的每一行记录实现匹配。此时这样的效果无法用文氏图表示，而应用类似下图展现：

对应的SQL代码如下：

SELECT * FROM T1
CROSS JOIN T2;

再次注意，实现交叉连接的SQL语句中不需要连接字段限制，否则可能报错。