连接运算 3-JOIN 的简化
三. JOIN 的简化
我们先看如何利用关联都涉及主键这个特征来简化 JOIN 的代码书写，分别讨论这三种情况。
1. 外键属性化
先看个例子，设有如下两个表：
employee 员工表
-
id 员工编号
-
name 姓名
-
nationality 国籍
-
department 所属部门
department 部门表
-
id 部门编号
-
name 部门名称
-
manager 部门经理
employee 表和 delpartment 表的主键都是其中的 id 字段，employee 表的 department 字段是指向 department 表的外键，department 表的 manager 字段又是指向 employee 表的外键（因为经理也是个员工）。这是很常规的表结构设计。
现在我们想问一下：哪些美国籍员工有一个中国籍经理？
用 SQL 写出来是个三表 JOIN 的语句：
SELECT A.*
FROM employee A
JOIN department B ON A.department=B.id
JOIN employee C ON B.manager=C.id
WHERE A.nationality='USA' AND C.nationality='CHN'
首先要 FROM employee 用于获取员工信息，然后这个 employee 表要和 department 做 JOIN 获取员工的部门信息，接着这个 department 表还要再和 employee 表 JOIN 要获取经理的信息，这样 employee 表需要两次参与 JOIN，在 SQL 语句中要为它起个别名加以区分，整个句子就显得比较复杂难懂。
如果我们把外键字段直接理解成它关联的维表记录，就可以换一种写法：
SELECT *
FROM employee
WHERE nationality='USA' AND department.manager.nationality='CHN'
当然，这不是标准的 SQL 语句了。
第二个句子中有部分表示当前员工的“所属部门的经理的国籍”。我们把外键字段理解成维表的记录后，维表的字段被理解为外键的属性，department.manager 即是”所属部门的经理“，而这个字段在 department 中仍然是个外键，那么它对应的维表记录字段可以继续理解为它的属性，也就会有 department.manager.nationality，即“所属部门的经理的国籍”。
这种对象式的理解方式称为外键属性化，显然比笛卡尔积过滤的理解方式要自然直观得多。外键表 JOIN 时并不会涉及到两个表的乘法，外键字段只是用于找到维键表中对应的那条记录，完全不会涉及到笛卡尔积这种有乘法特性的运算。
我们前面约定，外键关联时时维表中关联键必须是主键，这样，事实表中每一条记录的外键字段关联的维表记录就是唯一的，也就是说 employee 表中每一条记录的 deparment 字段唯一关联一条 deparment 表中的记录，而 department 表中每一条记录的 manager 字段也唯一关联一条 employee 表中的记录。这就保证了对于 employee 表中的每一条记录，deparment.manager.nationality 都有唯一的取值，可以被明确定义。
但是，SQL 对 JOIN 的定义中并没有主键的约定，如果基于 SQL 的规则，就不能认定与事实表中外键关联的维表记录有唯一性，有可能发生与多条记录关联，对于 employee 表的记录来讲，department.manager.nationality 没有明确定义，就不能使用了。
事实上，这种对象式写法在高级语言（如 C，Java）中很常见，在这类语言中，数据就是按对象方式存储的。employee 表中的 department 字段取值根本就是一个对象，而不是编号。其实许多表的主键取值本身并没有业务意义，仅仅是为了区分记录，而外键字段也仅仅是为了找到维表中的相应记录，如果外键字段直接是对象，就不需要再通过编号来标识了。不过，SQL 不能支持这种存储机制，还要借助编号。
我们说过外键关联是不对称的，即事实表和维表是不对等的，只能基于事实表去找维表字段，而不会有倒过来的情况。
2. 同维表等同化
同维表的情况相对简单，还是从例子开始，设有两个表：
employee 员工表
-
id 员工编号
-
name 姓名
-
salary 工资
-
…
manager 经理表
-
id 员工编号
-
allowance 岗位津贴
-
….
两个表的主键都是 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 订单编号
-
customer 客户
-
date 日期
-
…
OrderDetail 订单明细
-
id 订单编号
-
no 序号
-
product 订购产品
-
price 价格
-
…
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 表还有一个子表用于记录回款情况：
OrderPayment 订单回款表
-
id 订单编号
-
date 回款日期
-
amount 回款金额
-
….
我们现在想知道那些订单还在欠钱，也就是累计回款金额小于订单总金额的订单。
简单地把这三个表 JOIN 起来是不对的，OrderDetail 和 OrderPayment 会发生多对多的关系，这就错了（回忆前面提过的多对多大概率错误的说法）。这两个子表要分别先做 GROUP，再一起与 Orders 表 JOIN 起来才能得到正确结果，会写成子查询的形式：
SELECT Orders.id, Orders.customer,A.x,B.y
FROM Orders
LEFT JOIN ( SELECT id,SUM(price) x FROM OrderDetail GROUP BY id) A
ON Orders.id=A.id
LEFT JOIN ( SELECT id,SUM(amount) y FROM OrderPayment GROUP BY id ) B
ON Orders.id=B.id
WHERE A.x>B.y
如果我们继续把子表看成主表的集合字段，那就很简单了：
SELECT id,customer,OrderDetail.SUM(price) x,OrderPayment.SUM(amount) y
FROM Orders
WHERE x>y
这种写法也不容易发生多对多的错误。
主子表关系是不对等的，不过两个方向的引用都有意义，上面谈了从主表引用子表的情况，从子表引用主表则和外键表类似。
我们改变对 JOIN 运算的看法，摒弃笛卡尔积的思路，把多表关联运算看成是稍复杂些的单表运算。这样，相当于把最常见的等值 JOIN 运算的关联消除了，甚至在语法中取消了 JOIN 关键字，书写和理解都要简单很多。
