数据蒋堂 | JOIN简化

本文主要是介绍数据蒋堂 | JOIN简化 - 消除关联，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

来源：数据蒋堂

作者：蒋步星

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

本文为你讲解SQL中用于多表关联的JOIN运算的简化——消除关联。

我们将等值JOIN分成三种情况来分别讨论，分情况相当于加强了条件，我们可以充分利用每种情况下的特征。

1. 外键属性化

先看个例子，设有如下两个表：

employee表和delpartment表的主键都是其中的id字段，employee表的department字段是指向department表的外键，department表的manager字段又是指向employee表的外键。这是很常规的表结构设计。

现在我们想问一下：哪些美国籍员工有一个中国籍经理？

用SQL写出来是这样的：

SELECT A.* FROM employee A JOIN department B ON A.department=B.id JOIN employee C ON B.manager=C.id

WHERE A.nationality=‘美国' AND C.nationality=‘中国'

句子较长，由于employee表需要两次参与JOIN，还需要为它起个别名加以区分。

我们换一种写法：

SELECT * FROM employee WHERE nationality='美国' AND department.manager.nationality='中国'

当然，这不是标准的SQL语句了。

第二个句子中用红色部分表示当前员工的“所属部门的经理的国籍”。我们把外键字段理解成一个对象，外键表的字段被理解为外键字段的属性，department.manager即是”所属部门的经理“，而这个字段在department中仍然是个外键，那么它的外键表字段可以继续理解为它的属性，也就会有department.manager.nationality，即“所属部门的经理的国籍”。

这种的对象式理解方式，显然比笛卡尔积过滤的理解方式要自然直观得多。外键表JOIN时并不会涉及到两个表的乘法，外键字段只是用于找到外键表中对应的那条记录，完全不会涉及到笛卡尔积这种有乘法特性的运算。

我们前面约定，外键表JOIN时维表中关联字段必须是主键，所以外键字段对应的维表记录一定是唯一的，这样deparment.manager.nationality对于employee表中每一条记录都是唯一的，这就不会发生歧义。而如果不做这个约定，就可能发生多对多的情况，department.manager.nationality无法明确定义。

事实上，这种对象式写法在结构化高级语言（如C，Java）中很常见，在这类语言中，数据就是按对象方式存储的。employee表中的department字段取值根本就是一个对象，而不是编号。其实许多表的主键取值本身并没有业务意义，仅仅是为了区分记录，而外键字段也仅仅是为了找到外键表中的相应记录，如果外键字段直接是对象，就不需要再通过编号来标识了。不过，SQL缺乏离散性，不能直接使用这种存储机制，还要借助编号。

外键表关系中，事实表和维表是不对等的，只能基于事实表去找维表字段，而不会有倒过来的情况。

2. 同维表等同化

同维表的情况相对简单，还是从例子开始，设有两个表：

两个表的主键都是id，经理也是员工，两表共用同样的员工编号，经理会比普通员工多一些属性，另用一个经理表来保存。

现在我们要统计所有员工（包括经理）的总收入（加上津贴）。

用SQL写出来还是会用到JOIN：

SELECT employee.id, employee.name, employy.salary+manager.allowance FROM

employyee LEFT JOIN manager ON employee.id=manager.id

而对于两个一对一的表，我们其实可以简单地把它们看成一个表：

SELECT id,name,salary+allowance FROM employee

同样地，根据我们的约定，同维表JOIN时两个表都是按主键关联的，相应记录是唯一对应的，salary+allowance对employee表中每条记录都是唯一可计算的，不会出现歧义。

同维表之间的关系是对等的，从任何一个表都可以引用到其它同维表的字段。

3. 主子表一体化

订单及订单明细是典型的主子表：

Orders表的主键是id，OrderDetail表中的主键是(id,no)，前者的主键是后者的一部分。

现在我们想计算每张订单的总金额。

直接用SQL写出来会是这样：

SELECT Orders.id, Orders.customer, SUM(OrderDetail.price)

FROM Orders JOIN OrderDetail ON Orders.id=OrderDetail.id

GROUP BY Orders.id, Orders.customer

要完成这个运算，不仅要用到JOIN，还需要做一次GROUP BY，否则选出来的记录数太多。

如果我们把子表中与主表相关的记录看成主表的一个字段，那么这个问题也可以不再使用JOIN以及GROUP BY：

SELECT id, customer, OrderDetail.SUM(price) FROM Orders

与普通字段不同，OrderDetail被看成Orders表的字段时，其取值将是一个集合，因为两个表是一对多的关系。所以要在这里使用聚合运算把集合值计算成单值。

这样看待数据关联，不仅理解书写更为简单，而且不容易出错。

假如Orders表还有一个子表用于记录回款情况：

我们现在想知道哪些订单还在欠钱，也就是累计回款金额小于订单总金额的订单。

简单地把这三个表JOIN起来是不对的，OrderDetail和OrderPayment会发生多对多的关系，这就错了（回忆上一篇中多对多大概率错误的说法）。这两个子表要分别先做GROUP，再一起与Orders表JOIN起来才能得到正确结果，写出来较为繁琐。

而如果我们把子表看成主表的集合字段，那就很简单了：

SELECT id,customer,OrderDetail.SUM(price) x, OrderPayment.SUM(amount) y FROM Orders WHERE x>y

这种写法就不容易发生多对多的错误。

主子表关系是不对等的，不过两个方向的引用都有意义，上面谈了从主表引用子表的情况，从子表引用主表则和外键表类似。

我们改变对JOIN运算的看法，摒弃笛卡尔积的思路，把多表关联运算看成是稍复杂些的单表运算。这样，我们相当于从最常见的等值JOIN运算中基本消除了关联，甚至在语法中取消了JOIN关键字，书写和理解都要简单很多。

专栏作者简介

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

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

数据蒋堂

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

往期回顾：

数据蒋堂 | JOIN运算剖析

数据蒋堂 | 迭代聚合语法

数据蒋堂 | 非常规聚合

数据蒋堂 | 再谈有序分组

数据蒋堂 | 有序分组

数据蒋堂 | 非等值分组

数据蒋堂 | 还原分组运算的本意

数据蒋堂 | 有序遍历语法

数据蒋堂 | 常规遍历语法

数据蒋堂 | 从SQL语法看离散性

数据蒋堂 | 从SQL语法看集合化

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

数据蒋堂 | SQL的困难源于关系代数

数据蒋堂 | SQL像英语是个善意的错误

数据蒋堂 | 开放的计算能力为数据库瘦身

数据蒋堂 | 计算封闭性导致臃肿的数据库

数据蒋堂 | 怎样看待存储过程的移植困难

数据蒋堂 | 存储过程的利之弊

数据蒋堂 | 不要对自助BI期望过高

数据蒋堂 | 报表的数据计算层

数据蒋堂 | 报表应用的三层结构

数据蒋堂 | 列式存储的另一面

数据蒋堂 | 硬盘的性能特征

数据蒋堂 | 我们需要怎样的OLAP？

数据蒋堂 | 1T数据到底有多大？

数据蒋堂 | 索引的本质是排序

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

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

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

校对：朱江华峰

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

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

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

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