浅析表连接
表连接
表连接是一个很有意思的事情,报表中常用的就是JOIN和LEFT JOIN,可能大家也会看到INNER JOIN , LEFT OUTER JOIN等,它们的关系,请读者自己网上查阅,在这里我们要卖个关子。
对初学者来说,表连接是很容易迷糊的一点。容易混淆的原因是因为进行表连接时,经常会把关联字段和关联字段中存的数据混在一起说,容易给人误解。 还是第2节中的例子,我们知道,《人员主集》.’国籍’和《代码项》.’代码项内码’的数据是有关联的,一般在我们的系统中,‘国籍’中的数据,在’代码项内码’中都能找得到。
当我们说,可以通过这两个字段,将这两个表连接起来,其实真正意思是可以通过这两个字段中的数据把这两个表连接起来,请务必牢记这一点,真正在关联中起作用的是字段中的数据,是表中的那实实在在的若干条记录决定了查找出来的结果集。
说到连接,如果每张表都只有一条记录,那我们头脑中想象出来的表连接结果就很直观,我们很容易就能感受到,只需要把两表中各自的一条记录,像编一条绳子一样串起来就行了,串起来用到的’扣’就是两个连接字段中相等的数据。如下图:
内连接
用JOIN关键字进行连接就叫内连接。
JOIN…ON…部分做的事情就是用’国籍’字段和’代码项内码’字段对两表进行内连接,连接的结果是保留了两个表总共的5个字段,注意,表连接并不会合并字段,也不会增加新字段,会保持两个表的原有字段不动,我们可以把表连接的结果,看成是如下的一张虚拟中间表:
数据库执行SQL的顺序是从FROM开始, 然后是JOIN部分,最后再是SELECT部分, 我们阅读的顺序一般也是如此 。
如果我们的数据是如下的情况,结果又会如何呢?
如上图,《人员主集》中只有张三1条记录,《代码项》中也只有美国1条记录,而张三的’国籍’字段跟美国的’内码’字段不匹配,所以还是上一段SQL,我们的结果集就会是空,什么记录都拿不到,因为表连接之后产生的虚拟中间表是没有记录的。虚拟中间表内的数据,如下图。
由于关联字段串联不起来,导致我们的虚拟中间表里没有数据,从而逻辑顺序排在后面的SELECT部分,拿不到任何数据。
在进行SELECT的时候,同样的一段SQL, 同样的连接字段,会因为表中的数据不同,尤其是连接字段中数据的不同,产生不同的结果集。所用连接字段的数据严重影响了我们的结果集, 我们写的SQL语句,可以理解为,是用字段来代指一列数据,而实际影响结果集的是表中存在的记录。
上面展示了用JOIN进行表连接, 一条记录的情况,如果记录多一点会怎样,还是用主子集的表结构举例:
就像连接线中描述的那样,我们最终会得到5条记录,如下图:
如果两个表的记录改成如下所示:
那JOIN的结果会是:
产生的虚拟中间表中没有李四的记录了,在这里读者可以对JOIN的结果有一个直观的感受,使用JOIN时,我们把两个表看做两个仓库:仓库A和仓库B,把表连接产生的虚拟中间表看成仓库C。把两个原始表中的记录,看做仓库中存放的很多条绳子,所以现在,仓库A和仓库B都存放了若干条绳子,仓库C暂时是空的。
我们把SQL语句中规定的关联字段看做系绳子时用于打结的部分,在使用JOIN进行表连接的时候,两个仓库的绳子要想系起来, 仓库管理员会遍历查找仓库A和仓库B的每一条绳子,我们可以简单的认为,它是按如下的方式遍历的:
不妨给仓库A、B中的每一条绳子编下号1、2、3、N…… 仓库管理员会先复制一份A.1,我们就叫它A.1C吧, 然后带着A.1C,去和仓库B中的绳子挨个比对,当发现仓库B中的某条绳子B.N的关联字段的值和A.1的关联字段的值相等时,就复制一条B.N,产生一条新绳子叫B.NC,然后系一下A.1C和B.NC,放到仓库C里去,这算一个来回,仓库管理员还得再回仓库A,继续复制出一条A.1C,带去仓库B作比较,这次,他只需要从B.N+1继续比对就可以了,发现相等的,就再系下放到仓库C 。这样来来回回,如果仓库B中有多条绳子跟A.1匹配的,那么最终仓库C中,就会出现多条绳子的前半段都是A.1的情况,比如上图的虚拟中间表,张三的记录,就出现在仓库C中绳子的左半端,出现了3次。因为张三那条记录的连接字段:’国籍’,在仓库B中匹配成功了三次,匹配成功一次,就放到仓库C中一次。
如果张三这条记录是相亲节目的男嘉宾,他是不是很嗨皮, 如果对于某条绳子A.N,仓库B中从头到尾没有匹配的,那A.N这根绳子,是注定不会出现在仓库C中了,再回头看下上两图的原表和虚拟中间表,《人员主集》中李四这条记录为什么没有出现在虚拟中间表中呢?请读者参考上下文解答这个问题。
左连接
左连接的关键字是LEFT JOIN,我们的SQL改成如下所示:
结果会是如何呢?
左连接跟内连接稍有不同,仓库管理员大部分操作跟上面是一样的。不同点是,当他发现仓库B中没有跟A.N匹配的绳子的话,他会取出事先准备好的特殊绳子与A.N拼接,再放到仓库C中。这条特殊的绳子,长度跟B.N一样,结构也跟B.N相同,不同的是每个字段的值都是NULL。
也就是说,采用左连接,对左表的任何一条记录,不管右表中有没有匹配的记录,它都会显示出来。
左连接非常照顾左表中’不怎么受欢迎’的记录,它们不会被遗忘;对于左表中那些’很受欢迎’的记录,在右表中有N条匹配的那种,它们的连接效果跟内连接是一样的,也会在虚拟中间表中出现N次。
来看看左右两表只有一条记录的情况:
原表:
虚拟中间表:
张三这条记录并不能和美国这条记录匹配成功,可因为是左连接,左表的所有记录都会出现在虚拟中间表中,所以即便张三事实上没有成功牵手女嘉宾,但是节目组仍然会给张三一个安慰奖:全是NULL的另一半。
我们报表中用的最多的就是左连接, 因为它可以保证记录数,如果表中原记录数是M, 使用左连接跟其他表连接后,虚拟中间表的记录数会大于等于M,左表中的任何一条记录的出场次数,都会大于等于1。
拿人事模块的报表来说,人员主集上的记录数,也就是公司的总人数,常常会是非常硬的基准,如果我们要查下人员花名册,要取公司下的所有人的明细,20个字段的数据,其中包括最高学历,但是人员主集上张三的最高学历字段没有维护,我们如果用内连接,张三这条记录就不会展示了,实际上我们想要的效果是展示所有人,有些字段没维护,留空就好了,对此,左连接完美满足我们的需求。
下面是多条记录中左连接的例子
原表:
虚拟中间表:
如上图,左表中所有记录都出现了,因为张三在右表中成功匹配了三次,所以,他在虚拟中间表里出现了三次;李四虽然没有在右表中成功匹配,所以只在虚拟中间表中出现了一次,右半部分全是NULL值。