join是SQL查询中很常见的一个操作。具体有join、leftjoin、rightjoin、fulljoin等多种形式，具体原理如下图所示。但最常见的一种是使用左连接。本文代码在mysql和hive中均经过测试。代码本身并不难也不长。我准备了测试数据的mysql和hive代码。如果觉得有必要，可以在后台回复“左”，方便自己修改和练习。leftjoin通俗解释：左表为主表，返回左表所有行。如果右表没有匹配到，左表仍然有记录，右表的字段填充为null。看起来很容易理解，但是在实际操作中可能会有一些容易被忽视的地方。1、leftjoin后有几条记录。这是为了了解leftjoin的执行条件。当A连接B时，我们在on语句中指定与两个表关联的键。只要键值相等，就会出现在结果中。有一对一、一对多、多对多等几种情况。让我们用一个例子来说明。1、一对一最好理解。t_name表有id、name（用户名）、sex（性别）、dt（注册日期）等字段。t_age表。有id、age（年龄）、province（省）、dt（更新日期）等字段。表中包含的信息如下：现在我们对t_name（左表，别名a）和t_age（右表，别名b）进行leftjoin操作，关联的key为id。a表有6条记录，b表有3条记录，key键是唯一的，所以最终结果以a表为准。有6条记录，b表有3条记录不能关联。对应的记录中，b表的字段全部为空。2.一对多这次我们使用t_age作为左表，关联条件为dt。关注dt为20190905的记录，由于右表中有20190905的3条记录，链接时这3条记录都满足链接条件，所以最终的结果会有3条20190905的记录。这次的主流表是t_age表，但是很明显结果不是原来的3条记录，而是7条记录：20190905有3条记录，20190906有4条记录，不明白的可以继续往下看。3、多对多上面的例子，20190906最终有4条记录，也是因为满足关联条件，是2对2的情况。这里我们还是回到t_name表为主表的情况，使用dt进行关联。可以预见，与2相比，这次的结果中多了一行20190907，而表b中对应的字段还是空的。在2和3中，我们看到了一对多和多对多的情况。事实上，前者是后者的特例。我们只是简单列出了两张表关联后的所有字段，但在实际操作中可能需要做一些统计、聚合等。这里提醒大家，在写关联条件之前，最好先想好最后的结果会是怎样。最后可能有几行，统计的时候会不会多一些统计，哪些行可能有空值，哪些字段可能存在Null等等，不要想当然地犯错误。这是一个很好的介绍。有兴趣的同学可以看这篇博客深入学习，https://www.cnblogs.com/qdhxhz/p/10897315.html2.leftjoin的执行原理接下来，我们来仔细看看连接条件写法的区别inon和writinginwhere。在此之前，我们可以看看leftjoin的具体执行逻辑。参考了网上大神的博客：https://developer.aliyun.com/article/718897。总结如下：mysql使用嵌套循环处理leftjoin。SELECT*FROMLTLEFTJOINRTONP1(LT,RT))WHEREP2(LT,RT)其中P1为on过滤条件，如果缺失则视为TRUE，P2为where过滤条件，如果缺少，它将被视为TRUE。执行逻辑可以描述为：FOReachrowltinLT{//遍历左表的每一行BOOLb=FALSE;FOReachrowrtinRTsuchthatP1(lt,rt){//遍历右表的每一行，找到满足连接条件的行IFP2(lt,rt){//满足where过滤条件t:=lt||rt;//合并行，输出该行}b=TRUE;//lt在RT中有对应的行}IF(!b){//遍历RT后，找到lt如果RT中没有对应的行，尝试补一行withnullIFP2(lt,NULL){//填充null满足where过滤条件t:=lt||NULL;//输出填充lt和null的行}}}看不懂代码，只看结论：如果要限制右表，必须在on条件下进行。如果在where中进行，可能会导致数据缺失，导致左表中右表中没有匹配行的行不会出现在最终的结果中，违反了我们对leftjoin的理解。因为对于左表中没有匹配到右表行的行，遍历完右表后b=FALSE，所以我们会尝试用NULL填充右表，但是此时我们的P2限制了右表中的行，如果NULL不满足P2（NULL一般不满足约束条件，除非ISNULL），则不会添加到最终结果中，导致结果缺失。2、如果没有where条件，不管on条件如何限制左表，左表的每一行都会至少有一个合成结果。对于左表行，如果右表没有对应的行，那么右表遍历后，b=FALSE会使用一行NULL来生成数据，这个数据是冗余的。所以要过滤左表必须用where。我们再来看一个例子，回过头来学习可能会更好地理解这段话。1.condition只有一个这里可以直接看第一部分的例子。最终以左表为准，右表匹配不到null结果，但可能出现多对多的情况。2.条件有2个。上图是在关联条件中加入b.age=24后的输出结果。由于b表的限制，满足条件的只有一个，但是由于没有where条件，所以还是以左表为准，因为是一对一的，所以输出的仍然是b表的记录数左表。更极端的是，我们可以“清除”列表b。以上两种情况，b表都没有符合条件的结果，所以在左表的基础上，右边的所有字段都为空。3、如果有where，把b.age=24写入where，发现结果里只有这一行，打破了“左连接”是基于左表的限制。我们再看看最后两种情况写在where中会发生什么：是的，结果都是空的。因为where是在on之后执行的，而on生成的结果中并没有满足条件的记录！这里给出两个结论：1.on条件是生成临时表时使用的条件。不管on中的条件是否成立，都会返回左表中的记录。2、where条件是临时表生成后过滤临时表的条件。这时候leftjoin就没有意义了（必须返回左表的记录），如果条件不成立，就会过滤掉所有的记录。4.如果有为null或者isnotnull，当条件写在on:当条件写在where:直观上我们理解WHERE...ISNULL子句会从数据中过滤掉不满足的匹配后匹配阶段条件的数据行。对于条件写在on的情况，可以说isnull是负匹配条件，isnotnull是正匹配条件。对于写在where中的条件，其实更容易理解，看现有where条件的结果是什么。读者可以从上面的例子来思考一下。3、看两个实际案例经过上面的讨论，我们再看两个案例，进一步理解和思考leftjoin的用法。1.案例一这个案例来自一个网络博客，前面已经提到了。链接：https://developer.aliyun.com/article/718897可以先想想怎么写，再看原文的答案。其实每个需求都可以很方便的写成两种，区别在于条件是写在where还是on。判断的原则是我们需要保证结果中的数据既不缺失也不冗余。需求1的条件需要写在on（保证结果不漏），需求2的条件需要写在where（保证结果不冗余）。2、案例2假设有一个用户活跃表t_active，记录了每天的活跃uid和对应的活跃日期。现在想看看当天有多少活跃用户还活跃（也就是一个留存概念）。所期望的如下表所示：对于表中的数据，我们可以这样理解。2019-09-29（即2019-09-29）0天活跃人数为100人，2019-09-29仍有100人活跃，2019-09-29（即2019-09-29）1天（即is,2019-09-28)活跃用户数80，2019-09-29还剩60。等等。对于这个需求，我们可以使用leftjoin进行自关联，将之前活跃的天数作为左表，期望计算的天数作为右表，计算日期差，分别统计左右表。初步SQL如下：（数据是我自己整理的）请在往下看之前先了解需求和目标，先想清楚。上面的写法有问题吗？你能得到上面想要的结果吗？原始数据和这条SQL操作的结果如下：dt和datediff在操作结果中为null，你能想象这是为什么吗？而当dt不为null时，最后两列的数据是一样的，显然不符合我们的预期。是什么原因？让我们一步步来看。首先，我们使用leftjoin的方式应该没有问题。我们先看select*没有任何计算的结果。可以看出，这相当于上面说的没有where条件的一对一关联，结果会以左表为准，不相关的用null来填充。值得注意的是，不能关联的日期是空值，空值参与datediff计算时，结果会是空值。看到这里有点明白了吗？因为在计算中涉及到空值，所以最终的datediff有一个空值，而在统计的时候，由于空值的存在，最终以日期差为维度时，左表的数和左表的数右边的表是一样的。如下代码所示：从上面的结果，我们可以推导出最初的SQL运行结果。比如当datediff=5时，一共有两条记录，左右表的count(distinctuid)都是2。当datediff为null时，左表的结果为7，右表为0，依此类推，与前面的结果相同。所以我们知道，没有达到预期的根本原因是空日期的存在。那么如何解决这个问题，显然只要填上空的日期就可以了。您可以用例当右表中的日期不相关时，使用相应的日期来弥补。代码如下：可以看到最终得到了想要的结果。以最后一行为例，表示距离2019-09-29（即2019-09-24）还有5天的那天有5个活跃的人。2019-09-29还有2人活跃，可以查看详细数据。其余的依此类推。我们使用casewhen来硬编码日期，这是基于我们知道今天是哪一天这一事实。在实践中，它可以是一个变量，但也必须是一个固定值，这需要具体情况具体分析。4.小结在这篇文章中，我们了解了leftjoin的原理以及在实践中可能遇到的问题。包括关联结果中记录的条数，on和where中写的关联条件的区别，where语句中有null时如何理解，最后用例子帮助大家理解。过程中参考了网上的一些博客，大家可以在阅读本文的基础上查阅。希望能帮到你！后台回复“已离开”，可获取本文测试用到的数据采集代码。
