数据蒋堂 | JOIN运算剖析

会员服务 ·

数据蒋堂 | JOIN运算剖析

2017 年 11 月 8 日 数据派THU 蒋步星

来源：数据蒋堂

作者：蒋步星

本文长度为2200字，建议阅读5分钟

本文为你讲解SQL中用于多表关联的运算JOIN。

JOIN是SQL中用于多表关联的运算，无论从程序员编写还是数据库实现角度来看，JOIN都是SQL中最难的运算。

其实，SQL对JOIN的定义非常简单，就是对两个集合（表）做笛卡尔积后再按某种条件过滤，写出来的语法也就是A JOIN B ON ...的形式。原则上，笛卡尔积后的结果集应当是以两集合成员构成的二元组为成员，不过由于SQL中的集合成员总是有字段的记录，而且也不支持泛型数据类型来描述成员为记录的二元组，所以就简单地把结果集处理成由两表记录的字段合并后构成的新记录集合。这也是JOIN一词在英语中的愿意，并没有乘法（笛卡尔积）的意思。把结果集理解成二元组还是字段合并的记录，都不会影响本文的讨论。

JOIN定义中并没有规定过滤条件的形式。理论上，只要目标结果集是两源集笛卡尔积的子集，都可以理解为JOIN运算。比如，我们可以计算 A JOIN B ON A<B，如果集合A={1,2,3},B={2,3,4}，则这个JOIN结果就是{(1,2),(1,3),{1,4),(2,3),{2,4),(3,4)}。

不过，有经验的程序员都知道，现实中绝大多数JOIN都是等值JOIN，即过滤条件是一个或多个相等关系（多个之间是AND关系），语法形如A JOIN B ON A.ai=B.bi AND ...，其中ai和bi分别是A和B的字段。而前述例子中ON A<B这类称为非等值JOIN，相对少见得多，而且许多情况下非等值JOIN可以转换成等值JOIN来处理，所以我们这里重点讨论等值JOIN。

根据对空值的处理规则，等值JOIN还可以衍生出LEFT JOIN和FULL JOIN，而且一般会被分成一对一、一对多、多对多等几种情况。这些常规术语在所有的SQL教科书都有，这里就不再赘述了。

我们来考察下面三种等值JOIN：

外键表

表A的某些字段与表B的主键关联（所谓关联，是指JOIN的过滤条件即由这些对应字段相等构成）。A表称为事实表，B表称为维表。A表中与B表主键关联的字段称为A指向B的外键，B也称为A的外键表。外键表是多对一的关系，且只有JOIN和LEFT JOIN，一般不会用到FULL JOIN。

典型例子：帐户交易记录和帐户基本信息。

同维表

表A的主键与表B的主键关联，A和B互称为同维表。同维表是一对一的关系，JOIN、LEFT JOIN和FULL JOIN的情况都会有。

典型例子：员工表和销售员表。

主子表

表A的主键与表B的部分主键关联，A称为主表，B称为子表。主子表是一对多的关系，只有JOIN和LEFT JOIN，不会有FULL JOIN。

典型例子：订单和订单明细。

这里说的主键是指逻辑上的主键，也就是在表中取值唯一的字段（组），一个表上可能有多个字段（组）都取值唯一（并不常见），可以认为都是主键。不是一定是在物理表上建立的那个主键。

在SQL的概念体系中并不区分外键表和主子表，多对一和一对多从SQL的观点看来只是关联方向不同，本质上是一回事。确实，订单也可以理解成订单明细的外键表。但是，我们在这里要把它们区分开，将来在简化语法和性能优化时将使用不同的手段。

我们说，这三种JOIN已经涵盖了绝大多数等值JOIN的情况，甚至可以说几乎全部有业务意义的等值JOIN都属于这三类，把等值JOIN限定在这三种情况之中，几乎不会减少其适应范围。

仔细考察这三种JOIN，我们发现所有关联都涉及主键，没有多对多的情况，可以不考虑这种情况吗？

是的！多对多的等值JOIN几乎没有业务意义。

如果JOIN两个表时的关联字段没有涉及到任何主键，那就会发生多对多的情况，而这种情况几乎一定还会有一个规模更大的表把这两个表作为维表关联起来。比如学生表和科目表在JOIN时，会有个成绩表以学生表和科目表作为维表，单纯只有学生表和科目表的JOIN没有业务意义了。

当写SQL时发现多对多的情况，那大概率是这个语句写错了！或者数据有问题！这条法则用于排除JOIN错误很有效。

不过，我们一直在说“几乎”，并没有用完全肯定的说法，也就是说，多对多在非常罕见的情况下也有业务意义。可举一例，用SQL实现矩阵乘法时会发生多对多的等值JOIN，具体写法读者可以自行补充。

笛卡尔积再过滤这种JOIN定义，确实非常简单，而且简单的内涵将得到更大的外延，可以把多对多等值JOIN甚至非等值JOIN等都包括进来。但是，过于简单的内涵无法充分体现出最常见等值JOIN的运算特征。这会导致编写代码和实现运算时就不能利用这些特征，在运算较为复杂时（涉及关联表较多以及有嵌套的情况），无论是书写还是优化都非常困难。而充分利用这些特征后，我们就能创造更简单的书写形式并获得更高效率的运算性能，我们将在以后的文章中逐步说明。

与其为了把罕见情况也包括进来而把运算定义为更通用的形式，还不如把这些情况定义成另一种运算更为合理。

专栏作者简介

润乾软件创始人、首席科学家

清华大学计算机硕士，著有《非线性报表模型原理》等，1989年，中国首个国际奥林匹克数学竞赛团体冠军成员，个人金牌；2000年，创立润乾公司；2004年，首次在润乾报表中提出非线性报表模型，完美解决了中国式复杂报表制表难题，目前该模型已经成为报表行业的标准；2014年，经过7年开发，润乾软件发布不依赖关系代数模型的计算引擎——集算器，有效地提高了复杂结构化大数据计算的开发和运算效率；2015年，润乾软件被福布斯中文网站评为“2015福布斯中国非上市潜力企业100强”；2016年，荣获中国电子信息产业发展研究院评选的“2016年中国软件和信息服务业十大领军人物”；2017年, 自主创新研发新一代的数据仓库、云数据库等产品即将面世。

数据蒋堂

《数据蒋堂》的作者蒋步星，从事信息系统建设和数据处理长达20多年的时间。他丰富的工程经验与深厚的理论功底相互融合、创新思想与传统观念的相互碰撞，虚拟与现实的相互交织，产生出了一篇篇的沥血之作。此连载的内容涉及从数据呈现、采集到加工计算再到存储以及挖掘等各个方面。大可观数据世界之远景、小可看技术疑难之细节。针对数据领域一些技术难点，站在研发人员的角度从浅入深，进行全方位、360度无死角深度剖析；对于一些业内观点，站在技术人员角度阐述自己的思考和理解。蒋步星还会对大数据的发展，站在业内专家角度给予预测和推断。静下心来认真研读你会发现，《数据蒋堂》的文章，有的会让用户避免重复前人走过的弯路，有的会让攻城狮面对扎心的难题茅塞顿开，有的会为初入行业的读者提供一把开启数据世界的钥匙，有的甚至会让业内专家大跌眼镜，产生思想交锋。

往期回顾：

数据蒋堂 | 常规遍历语法

数据蒋堂 | SQL用作大数据计算语法好吗？

数据蒋堂 | 功夫都在报表外--漫谈报表性能优化

数据蒋堂 | 非结构化数据分析是忽悠？

数据蒋堂 | 多维分析的后台性能优化手段

校对：王红玉

编辑：黄继彦

为保证发文质量、树立口碑，数据派现设立“错别字基金”，鼓励读者积极纠错。

若您在阅读文章过程中发现任何错误，请在文末留言，或到后台反馈，经小编确认后，数据派将向检举读者发8.8元红包。

同一位读者指出同一篇文章多处错误，奖金不变。不同读者指出同一处错误，奖励第一位读者。

感谢一直以来您的关注和支持，希望您能够监督数据派产出更加高质的内容。