PostgreSQL技术问答27 - JOIN
本文是《PostgreSQL技术问答》系列文章中的一篇。关于这个系列的由来，可以参阅开篇文章：
文章的编号只是一个标识，在系列中没有明确的逻辑顺序和意义。读者进行阅读时，不用太关注这个方面。
本文讨论的内容是PostgreSQL中的数据连接： JOIN。
什么是JOIN
JOIN，英文意为“连接”。
数据表(其实是数据模型)的连接，是关系型数据库理论和概念的核心和精华。这里有一个反面对比的例子就是Excel。Excel中，要同时表示两类或者多种类型的数据和它们的关系，是把它们的属性，按照字段和值，都“平铺”在一个大表当中，然后列值分类过滤的方式，来进行处理。这样的方式，虽然比较直观，但显然，冗余的数据和信息太多(同一个值可能会重复很多行)，处理的效率就不会太高。数据量比较少的时候，这个问题还不算突出，但如果数据量很大，就会带来严重的性能问题。
而关系型数据库的做法，是把逻辑上不同类型的数据，分成不同的表来进行存储。当如果业务处理，需要将这些表中的数据关联起来进行呈现的时候，就需要进行连接操作。它会使用一个连接条件，比如相同的标识符，将在不同表中的记录，提取属性内容，合并成为更大的扩展的记录，来实现一些业务目的。
例如，公司中，员工和部门的关系。逻辑上员工和部门是两个不同类型但是有关联的概念，一个部门有很多员工，员工可以属于不同的部门。当检查一个部门有哪些员工，或者某个员工属于哪个部门的时候，就需要来两者之间建立联系，通常是通过在数据库的员工表中，增加一个其所属部门的标识列，代表其所属部门，当需要检查该员工所属部门的时候，就使用这个标识字段，和部门表中的部门标识进行比较，如果匹配，就代表该员工在员工表中的数据行，可以连接到对应部门表中的相同标识的部门那一条记录，这样，就将两个表，在逻辑上连接了起来，即为JOIN。
这基本上就是连接的基本概念。我们看到，要建立两个表和其映射的逻辑实体之间的连接关系，需要的要素包括：
- 主表，就是以哪个表作为主体来进行连接
- 连接板，就是被连接记录的表，就是需要从这个表中，针对主表的行，(使用关联条件)找到逻辑关联的行
- 连接关系，就是使用何种条件，来检查两个表中认为可以匹配的方式，一般都是以某个行的标识作为连接关系，比较简单
下面，我们来具体了解一下，在Postgres中，是如何来设计和实现这一构想的。
什么是连接形式，有哪些连接形式
在Postgres包括标准SQL中，使用连接的标准形式为：
from T1 join_type T2 [ join_condition ]
这个模式中，from是关键字，用来指明主表。T1就是主表，T2是连接表，join_condition是连接关系(后面有详细讨论)。
我们还看到，除了上面三个要素之外，还有一个类型类型(join_type)。这个本来应该是简单的“join”关键字，但在实现的时候，可选增加了这个设计，从而为在数据连接操作的实现提供了更多的业务灵活性。现在，在Postgres中，可以支持和实现的连接方式包括：
- Inner: 内部连接，这个关键字可以忽略，也是默认形式
- Left: 左连接，就是以JOIN关键字的左边的表(From表)作为基础，先关联连接表中，所有匹配条件的记录，然后例举不能构成关联条件的记录，并将它们在无法关联的字段(来自连接表)中的内容设为null(因为无法找到关联关系，但又需要构造这些字段内容)。
- Right: 右连接，就是以JION关键字右边的表(连接表)作为基础，先关联连接表中，所有匹配条件的记录，然后例举不能构成关联条件的记录，并将它们在无法关联的字段(来自From中的表)中的内容设为null(因为无法找到关联关系，但又需要构造这些字段内容)。
- Full: 完全连接，其实就是左连接的内容和右连接的内容合并一下，能够一次性的列举所有三种状况
- Outer: 其实就是Full完全连接的另外一个说法。
下面这张借用于bytebytego的图表，可以清晰的展示这四种类型(Full和Outer其实是一样的)的连接方式：
利用上面几种技术形式，在业务方面，基于几种连接类型，可以实现的查询业务逻辑就可以包括：
- Inner连接，可以查询在两个表中都存在的记录
- Left/Right连接，可以查询两个表中都存在的记录，加上其中一个表中无法连接另一个表的记录
- Outer/Full，可以查询在两个表中都存在的记录，加上A表无法连接的记录，再加上B表中无法连接的记录
什么是join_condition
在前面的join标准语法形式中，我们还看到join语句和连接操作，需要一个join_condition作为连接的条件，正常情况下，这个部分是不能省略的。
为了便于理解这个概念，我们可以从思想上来模拟一下数据库进行连接操作的过程，应该大体如下。首先数据库基于查询指令，对主表进行遍历，针对当前记录，使用连接条件中的规则，对连接表进行检索，得到的结果记录集(一行或者多行)，和当前的行进行横向合并后，加入结果记录集；然后检查下一行，直到遍历完成，就可以得到最后的结果集。这样，我们就可以看到连接条件的本质，就是对连接表中的记录进行检查和过滤。
由此，我们也可以了解，除了简单的字段对应之外，其实可以有更复杂的检查关系，包括多个连接条件，以及其他可能和连接字段无关的外部条件检查等等。
下面，我们通过一些实例，来了解一下Postgres中连接是如何具体操作的。
JOIN是如何使用的
下面是一个简单的例子，展示了最简单基本的情况，为了方便讨论，笔者使用了构造的数据表，可以直接执行和使用：
with A(id, name) as (values
(1,'关羽'),
(2,'张飞'),
(3,'典韦')
), B(id, attr, score) as (values
(1,'智力', 85),
(1,'武功', 90),
(2,'武功', 88),
(5,'魅力', 76)
) select A.id, name,attr, score from A join B on A.id = B.id;
id | name | attr | score
----+------+------+-------
1 | 关羽 | 智力 | 85
1 | 关羽 | 武功 | 90
2 | 张飞 | 武功 | 88
(3 rows)
可以看到JOIN语句的标准形式和需要注意的问题：
- Select中，可以选择两个表中特定的字段，但要注意，如果字段名在两个表中都相同，需要强制指定所使用的表
- from 主表
- join 关联表
- on 关联条件，此处使用了两个表中的标识字段值相对的形式
下面我们进一步讨论更复杂一点的情况。
除了正常的连接记录之外，我想查询没有那些没有匹配连接的记录，如何操作
答案就是，使用非内部连接，包括Left Join和Right Join。其实左右连接，在技术上是等效的，完全取决于你如何定义哪个表是主表。这里的左、右的意思是，相对于join这个关键字，完整保留左边的表，还是右边的表的记录。下面是使用左连接和右连接的例子：
-- 左连接
select A.id, name, attr, score from A left join B on A.id = B.id;
id | name | attr | score
----+------+------+-------
1 | 关羽 | 智力 | 85
1 | 关羽 | 武功 | 90
2 | 张飞 | 武功 | 88
3 | 典韦 | |
(4 rows)
-- 右连接
select A.id, name, attr, score from A right join B on A.id = B.id;
id | name | attr | score
----+------+------+-------
1 | 关羽 | 智力 | 85
1 | 关羽 | 武功 | 90
2 | 张飞 | 武功 | 88
| | 魅力 | 76
(4 rows)
可以直观的看到，如果在记录中，找不到对应的关联关系，则对应的表的字段会被设为null。这一特性，还可以帮助我们来判断，这条记录，是一个正常连接，还是在对方表中找不到匹配的记录，这在实际应用中也经常用到。
如果还想再进一步，查询两个表中，所有在对方表中，没有连接关系的记录，还可以使用full join，完全连接。下面的例子，展示了这个用法：
select A.id, name,attr, score, case
when (A.id is not null and attr is null) then 'left'
when (A.id is null and attr is not null) then 'right'
else 'inner' end as jointype
from A full join B using (id);
id | name | attr | score | jointype
----+------+------+-------+----------
1 | 关羽 | 智力 | 85 | inner
1 | 关羽 | 武功 | 90 | inner
2 | 张飞 | 武功 | 88 | inner
| | 魅力 | 76 | right
3 | 典韦 | | | left
(5 rows)
我们可以看到，这个结果，其实是由三个部分构成的：
- Inner Join： 就是在T1和T2中都存在，并且满足连接关系的记录，而且字段的值都是完整的
- Right Join: 在右表中存在，但没有连接关系的记录， 可以通过左表字段内容为空判断
- Left Join: 在左边中存在，但没有连接关系的记录，可以通过右表字段内容为空判断
所以，使用left/right/full join，可以查询除了正常匹配之外，无法进行匹配的主表或者连接表中的记录。而且可以在同一个操作语句和结果记录集中实现，简化了业务操作和开发。
Join on ...这种写法有点繁琐，有没有更简单的写法
我们观察标准的Join连接写法，这个表述确实是比较繁琐。有时候，在两个表中，使用的字段都是相同的，语义也一样。幸运的是，在合适的条件之下，Postgres提供了一些更简单的写法，就是using和natural语句，也可以达成相同的目的，下面是几个简单的例子：
// 使用Using Columns
select A.id, name,attr, score from A join B using (id);
// Na
select A.id, name,attr, score from A natural join B ;
id | name | attr | score
----+------+------+-------
1 | 关羽 | 智力 | 85
1 | 关羽 | 武功 | 90
2 | 张飞 | 武功 | 88
(3 rows)
// left join
select A.id, name,attr, score from A natural left join B ;
id | name | attr | score
----+------+------+-------
1 | 关羽 | 智力 | 85
1 | 关羽 | 武功 | 90
2 | 张飞 | 武功 | 88
3 | 典韦 | |
(4 rows)
从上面的例子可以看到：
- 如果在JOIN 前面声明 Natural关键字，系统会自动搜索两个表中，相同名词的字段(可以多个)，并使用它们来进行连接查询，等同于on A.c1=B.c1 and A.c2=B.c3...
- 也可以手动指定“同名的”连接字段，使用using (colume...)方式，更加自然
- 同样支持left、right、full、outer等连接方式
- 这两种方法的主要限制是在表中连接字段的名词相同
- 在字段名相同的情况下，并且没有其他的限制，这两种方式都更加简洁自然
- 这两个特性，在一对一关系的表中，就更加合适，因为它们天然就是同一个标识
如果不使用连接会发生什么
前面讨论的内容，都是需要使用两个表中的关联字段，进行连接来查询的情景。这里就会有一个很自然的疑问，就是如果直接查询两个表，但不使用任何连接关系，会出现什么情况呢？
答案就是就会执行所谓的“交叉连接(Cross Join)”。严格的说，这个并不是所谓的连接关系，就是直接将两个表的所有可能的组合形式列举了出来。这个计算还有一个学术名词，就是“笛卡尔积”。比如下面这个例子：
select A.id, B.id, name,attr, score from A,B ;
-- 等效于
select A.id, B.id, name,attr, score from A cross join B;
id | id | name | attr | score
----+----+------+------+-------
1 | 1 | 关羽 | 智力 | 85
2 | 1 | 张飞 | 智力 | 85
3 | 1 | 典韦 | 智力 | 85
1 | 1 | 关羽 | 武功 | 90
2 | 1 | 张飞 | 武功 | 90
3 | 1 | 典韦 | 武功 | 90
1 | 2 | 关羽 | 武功 | 88
2 | 2 | 张飞 | 武功 | 88
3 | 2 | 典韦 | 武功 | 88
1 | 5 | 关羽 | 魅力 | 76
2 | 5 | 张飞 | 魅力 | 76
3 | 5 | 典韦 | 魅力 | 76
(12 rows)
对于这种“交叉连接”，按照join的原理也很好理解。就是当遍历主表时，对于每一行，都没有连接约束条件，就是连接表中所有的记录都可以匹配。上面的例子中，由于没有连接关系，所以也不需要指定连接的条件。A表有3条记录，B表有4条，所有它会把所有可能的组合，即所有12条记录都列在这里。如果这里硬要使用join的方式，需要在join之前声明cross类型，如例子中所示，而且不需要关联字段。
在一般的情况下，我们没有太多机会会用到这种操作。但笔者还是想到，如果其中的一个表可以确定是单条记录，使用交叉连接或者笛卡尔积的方式，可以将数据简单的直接附加到原表上的，而无需考虑什么关联操作。
数据连接如何体现数据间的关系
从逻辑上来看，不同的有关系的业务逻辑实体，对应在关系型数据库中，数据条目之间的关系，或者称为数据关系模型，其实只有三种。一对一、一对多和多对多。
- 一对一
就是从主表来看，在关联表中，可以关联到主表中的记录，最多只关联一个(或者可能没有关联的记录)。这种方式，适合于基于处理性能或者管理方便的原因，将数据表或者模型横向拆分的场合。例如，有一个主体信息表和一个附加信息表，使用一对一的关系进行连接，就可以有效的控制主体信息表的字段数量，而且对于没有附加信息的主体，也可以减少信息记录占用的空间和资源。
虽然在逻辑上，可以将一对一看成是一对多的一种特殊形式，但两者在业务逻辑上和实现操作的差异还是很大的。在数据库中要实现完整的一对一关联，可能需要在关联表中，使用关联关系相关的字段，并且设置其唯一性约束。如果不使用这种硬约束的方式，可能需要从外部程序，通过一些额外的工作来一对一关系数据进行维护，比如重复记录检查等等。
- 一对多
一对多的通常用于描述一种“分组”或者“父子”的关系。最典型的就是学校中，班级和学生之间的关系。一个班级内有很多学生，而特定的学生，只能属于一个特定的班级。
在数据库中，通常使用在关联表中，增加主表记录的标识字段，并配合关联查询，来实现一对多的关系模型和操作。例如，在学生表中，增加班级ID这一字段，就可以以此为关联，查询班级中所有学生的列表，或者查询特定学生所属班级的信息。
- 多对多
多对多关系，在真实的世界中，也是非常常见的。还是以学校中的场景为例，学校中的教师和班级，就是一个典型的多对多的关系，一个老师可能教授很多班级的课程，同时一个班级有很多老师来上不同的课。
实际上在一般的关系数据库系统中，是没有直接的方式，来实现和描述这个模型的。我们一般使用设计一个“中间表”的方式。比如新增加一个教师-班级表，并设计两个字段，分别代表教师ID和班级ID。然后，以这个表作为主表，就可以展开各种班级教师关系之间的查询，如查询某个教师在上哪些班级的课程，以及某个班级，有哪些教师上课等等。
在中间表中，还可以附加一些额外的信息，来提供除主对象记录之外，和关联关系有关的一些业务特性。例如在中间表中，可以增加顺序编号，在关联分组内部辅助排序；也可以在教师班级关联表中，增加课程信息，表示特定关联关系的业务等等。
中间表还能够提供一个好处，就是可以将业务实体进行解耦，在维护两者关系的时候，只需要维护中间表中的内容就可以了，不需要修改原始表中的内容，在原始表中，也无需知晓关系表中的信息和存在，比较容易扩展和维护。实际上，使用类似的概念，我们使用中间表，也可以实现“一对一”和“一对多”的关系，当然那会带来一些额外的复杂性。
除了中间表之外，在Postgres中，由于对数组类型和操作有比较好的支持。在没有额外的需求场景下，可以通过在主表中，设计一个“关联ID”数组字段，也可以实现多对多关联，并很好的支持各种查询方式。这个问题，笔者在另外有一篇博文进行了探讨，读者有兴趣可以去了解一下。
实际操作时，连接表和被连接表好像可以互换，应当如何选择呢
确实，从数学和技术上而言，连接两个表时，使用任意一个作为主表就是等效的(交换率？)。但在实际的场景中，结果可能没有什么不同，但交换连接的位置，可能会对性能造成不同的影响。因为通常而言，具有关联关系的两个表的数据规模，可能会差异很大。比如一个学校中，年级和班级的数量，和学生的数量，可能会差几十倍。那么，从JOIN操作执行的原理来看，检查学生表和班级表的操作，显然后者的代价更低。所以理论上，相关的查询，应当以班级表作为主表，以学生表作为关联表来进行操作。
所以，选择哪个表作为主表，其实是由业务特性来决定的。一般情况下，我们基于以下原则进行操作：
- 选择数据规模明显小，或者一对多关系中，哪个“一”的表作为主表，另一个表为关联表
- 如果涉及多个表的关联，尽量选择有多个关联字段，处于中心位置的表作为主表，和其他表构成一种“星”型的连接关系
关联查询时，将过滤条件，放在On子句(关联条件)和Where子句(过滤条件)中，有什么区别
在使用关联查询的时候，我们发现，对于关联查询的结果进行过滤的时候，可以把过滤条件按常规的方式写在Where子句当中，也可以写在关联条件子句On中。好像一般结果也没有什么差异。那么应该怎么做比较好呢？
为了研究这个问题，笔者做了一个简单的实验，代码和结果如下：
defaultdb=> explain
select T2.id,mid,T1.name name1, T2.name name2
from tmaster T1 join tslave T2 on T2.mid = T1.id and T1.id = 2;
QUERY PLAN
------------------------------------------------------------------------
Nested Loop (cost=0.00..2048.68 rows=56352 width=76)
-> Seq Scan on tslave t2 (cost=0.00..1323.28 rows=14088 width=18)
Filter: (mid = 2)
-> Materialize (cost=0.00..21.02 rows=4 width=62)
-> Seq Scan on tmaster t1 (cost=0.00..21.00 rows=4 width=62)
Filter: (id = 2)
(6 rows)
defaultdb=> explain
select T2.id,mid,T1.name name1, T2.name name2
from tmaster T1 join tslave T2 on T2.mid = T1.id
where T1.id = 2 ;
QUERY PLAN
------------------------------------------------------------------------
Nested Loop (cost=0.00..2048.68 rows=56352 width=76)
-> Seq Scan on tslave t2 (cost=0.00..1323.28 rows=14088 width=18)
Filter: (mid = 2)
-> Materialize (cost=0.00..21.02 rows=4 width=62)
-> Seq Scan on tmaster t1 (cost=0.00..21.00 rows=4 width=62)
Filter: (id = 2)
(6 rows)
defaultdb=> explain
select T2.id,mid,T1.name name1, T2.name name2
from tslave T2 join tmaster T1 on T2.mid = T1.id
where T1.id = 2 ;
QUERY PLAN
------------------------------------------------------------------------
Nested Loop (cost=0.00..2048.68 rows=56352 width=76)
-> Seq Scan on tslave t2 (cost=0.00..1323.28 rows=14088 width=18)
Filter: (mid = 2)
-> Materialize (cost=0.00..21.02 rows=4 width=62)
-> Seq Scan on tmaster t1 (cost=0.00..21.00 rows=4 width=62)
Filter: (id = 2)
(6 rows)
先说一个简单的结论，就是这些不同的操作，起码对于现在这个场景，也就是一般情况下，实际上并没有什么差异。因为可以注意，从查询计划的构造和内容来看，三种情况是一模一样的。所以，开发者可以根据自己的喜好，例如代码构造的简洁一致，来选择具体的实现方式。下面，我们来简单分析一下，为什么笔者可以得到这个结论。
在常规的认知中，将过滤条件，放置在On子句中，理论上更好。因为从正常的操作过程来看，数据库在主表中遍历行并检查关联条件的时候，就可以进行过滤，而无需等到关联完成以后，在进行过滤。所需要处理的数据集大小显然不同。但从上面的查询解释和分析来看，其实是一样的，为什么呢？笔者觉得，既然我们能看到这一点，数据库的设计和实现者也可以轻易的发现这个问题，然后他们就可以在执行查询计划的时候，将操作计划优化到两者不会产生实质的区别。所以，原理上可能操作的次序会对结果造成影响，但在实现上，对指令的解析和优化，可以消除这个影响。这应该就是一个优秀的软件系统设计和实现应该具备的素质。
同样的道理，我们从示例代码中，还可以发现，在做数据表连接的时候，起码在这个例子中，T1和T2的位置(主表-关联表)也是没有影响的。应该也是上面分析的原因，系统进行了合理的规划和优化。
当然，这些结论，只是笔者的认知和一家之言。笔者现在还没有精力的时间深入更深的技术细节来获得更有力的证据。如果读者对此有疑问或者觉得不对的地方，可以提出来大家进行探讨。
小结
本文探讨了关系型数据库中，一个非常重要和核心的概念，就是Join连接。文中讨论了对连接这种操作方式和功能的理解，并结合一些代码示例，例举了几种连接的方式，它们的特点、区别和应用场景等方面的内容。
转载自:https://juejin.cn/post/7389935779333029899