写的比较早。很多时候，由于SQL逻辑的复杂性和对SQL执行逻辑的理解不够透彻，很容易产生一些莫名其妙的结果。这些结果似乎并不符合预期。殊不知，这才是真正的结果。本文梳理了几个常见的SQL问题。我们在实际编写SQL脚本的时候，需要多加注意。我希望这篇文章可以帮助你。关于LEFTJOIN外连接是我们在写SQL时经常使用的一种多表连接方式，使用起来非常简单。值得注意的是，越简单的东西越容易忽略细节。通常我们是这样理解LEFTJOIN的：语义是满足Joinon条件直接返回，不满足则需要返回LeftOuterJoin左表的所有列，并且所有列右表中应填空。理解没有问题，但是里面有个误区：predicatepushdown。详见如下示例：假设有如下三张表：--createtablecreatetablet1(idint,valueint)partitionedby(dsstring);createtablet2(idint,valueint)partitionedby(dsstring);createtablet3(c1int,c2int,c3int);--数据加载，t1表insertoverwritetablet1partition(ds='20220120')select'1','2022';insertoverwritetablet1partition(ds='20220121')select'2','2022';insertoverwritetablet1partition(ds='20220122')select'2','2022';--dataloading,t2tableinsertoverwritetablet2partition(ds='20220120')选择'1','120??';当我们执行下面的SQL查询时，会返回什么数据呢？SELECT*FROMt1LEFTJOINt2ONt1.id=t2.idANDt1.ds='20220120';结果1:1202220220120112020220120结果2:12022202201201120202201202202220220121NULLNULLNULL1202220220122NULLNULLNULL我相信即使有很多开发经验的人也能得到正确的结果。其实result1并不是正确的结果，真正的返回值是result2，是不是和预期的结果不一致？很多初学者会认为上面查询SQL中的ANDt1.ds='20220120'会将谓词下推，从而得到结果2。其实SQL本身的语义并不是这样的。如果需要获取结果1的数据，正确的查询方法如下：--方法一：SELECT*FROMt1LEFTOUTERJOINt2ONt1.id=t2.idWHEREt1.ds='20220120';--方法二:SELECT*FROM(SELECT*FROMt1WHEREds='20220120')t1LEFTOUTERJOINt2ONt2ONt1.id=t2.id;仔细看有区别吗？重点在WHEREt1上。ds='20220120'过滤条件，top查询方法是ONt1.ds='20220120'，所以根据LEFTJOIN的语义，如果没有过滤条件，那么应该返回左表的所有数据，和右表不能匹配则填null。执行计划我们来看看不带谓词下推的查询SQL的执行计划。正常LEFTJOIN查看执行计划EXPLAINSELECT*FROMt1LEFTJOINt2ONt1.id=t2.idANDt1.ds='20220120';执行计划结果hive>EXPLAIN>SELECT*>FROMt1>LEFTJOINt2>ONt1.id=t2.id>ANDt1.ds='20220120'>;OKSTAGEDEPENDENCIES:Stage-4isarootstageStage-3dependsonstages:Stage-4Stage-0dependsstages:Stage-3STAGEPLANS:Stage:Stage-4MapReduceLocalWorkAlias->MapLocalTables:$hdt$_1:t2FetchOperatorlimit:-1Alias->映射本地运算符树：$hdt$_1:t2TableScan别名：t2统计信息：行数：1数据大小：5基本统计信息：COMPLETE列统计信息：NONE选择运算符表达式：id（类型：int），值（类型:int),ds(type:string)outputColumnNames:_col0,_col1,_col2统计数据：Numrows:1Datasize:5Basicstats:COMPLETEColumnstats:NONEHashTableSinkOperatorfilterpredicates:0{(_col2='20220120')}1keys:0_col0(type:int)1_col0(type:int)Stage:Stage-3MapReduceMapOperatorTree：TableScan别名：t1统计信息：行数：3数据大小：18基本统计信息：完整列统计信息：无选择运算符表达式：id（类型：int），值（类型：int），ds（类型：字符串）outputColumnNames：_col0,_col1,_col2统计数据：行数：3数据大小：18基本统计数据：COMPLETE列统计数据：无MapJoinOperator条件映射：LeftOuterJoin0到1个过滤谓词：0{(_col2='20220120')}1个键：0_col0（类型：整数）1_col0（类型：int）outputColumnNames：_col0、_col1、_col2、_col3、_col4、_col5统计数据：行数：3数据大小：19基本统计数据：COMPLETE列统计数据：无文件输出运算符压缩：false统计数据：行数数据大小:19Basicstats:COMPLETEColumnstats:NONEtable:inputformat:org.apache.hadoop.mapred.SequenceFileInputFormatoutputformat:org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormatserde:org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDeLocalWork:MapReduceLocalWorkStage:Stage-0FetchOperatorlimit:-1ProcessorTree:ListSink从上面的执行计划可以看出一共有3个stage，STAGEDEPENDENCIES:Stage-4是根阶段Stage-3依赖于阶段：Stage-4Stage-0依赖于阶段：Stage-3其中stage4是读取t2表并将t2表加载到map端的HashTable的map任务加入t2表中的数据量为1行。选择运算符表达式：id（类型：int）、value（类型：int）、ds（类型：string）outputColumnNames：_col0、_col1、_col2统计信息：Numrows：1数据大小：5基本统计信息：COMPLETE列统计信息：NONEHashTableSinkOperatorstage3是一个map任务，读取t1表中的数据，并在map端执行join。t1表的行数为3行，可见没有进行过滤操作。映射运算符树：TableScan别名：t1统计信息：行数：3数据大小：18基本统计信息：COMPLETE列统计信息：NONE选择运算符表达式：id（类型：int）、value（类型：int）、ds（类型：string）outputColumnNames:_col0,_col1,_col2Statistics:Numrows:3Datasize:18Basicstats:COMPLETEColumnstats:NONEStage-0输出结果，最后没有进行过滤操作。Stage:Stage-0FetchOperatorlimit:-1ProcessorTree:ListSink称词下推的LEFTJOIN查看执行计划EXPLAINSELECT*FROMt1LEFTOUTERJOINt2ONt1.id=t2.idWHEREt1.ds='20220120';执行计划结果STAGE依赖关系：Stage-4是根阶段Stage-3依赖于阶段：Stage-4Stage-0依赖于阶段：Stage-3阶段计划：阶段：Stage-4MapReduceLocalWorkAlias->MapLocalTables：$hdt$_1:t2获取运算符限制：-1别名->映射本地运算符树：$hdt$_1:t2TableScan别名：t2统计信息：行数：1数据大小：5基本统计信息：COMPLETE列统计信息：NONE选择运算符表达式：id(type:int),value(type:int),ds(type:string)outputColumnNames:_col0,_col1,_col2统计数据：行数：1数据大小：5基本统计数据：COMPLETE列统计数据：NONEHashTableSinkOperator键：0_col0（类型：int）1_col0（类型：int）阶段：Stage-3MapReduceMapOperatorTree：TableScan别名：t1统计数据：行数：1数据大小：6基本统计数据：COMPLETE列统计数据：NONE选择运算符表达式:id(type:int),value(type:int)outputColumnNames:_col0,_col1统计数据：Numrows:1数据大小：6基本统计数据：COMPLETE列统计数据：NONEMapJoinOperator条件映射：LeftOuterJoin0to1键：0_col0（类型：int）1_col0（类型：int）outputColumnNames：_col0、_col1、_col3、_col4、_col5统计信息：行数：1数据大小：6基本统计信息：COMPLETE列统计信息：无选择运算符表达式：_col0（类型:int),_col1(type:int),'20220120'(type:string),_col3(type:int),_col4(type:int),_col5(type:string)outputColumnNames:_col0,_col1,_col2,_col3,_col4,_col5统计数据：Numrows:1数据大小：6Basicstats:COMPLETEColumnstats:NONEFile输出运算符压缩：false统计信息：Numrows：1数据大小：6基本统计信息：COMPLETE列统计信息：NONE表：输入格式：org.apache.hadoop.mapred.SequenceFileInputFormat输出格式：org.apache.hadoop.hive.ql。io.HiveSequenceFileOutputFormatserde:org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDeLocalWork:MapReduceLocalWorkStage:Stage-0FetchOperatorlimit:-1ProcessorTree:ListSink从上面的执行计划可以看出来：总共有3个stage，STAGEDEPENDENCIES:Stage-4是根stageStage-3依赖stages:Stage-4Stage-0依赖stages:Stage-3其中stage4是map任务读取t2表，将t2表加载成一个HashTable，用于map端的joint2表，数据量为1行。TableScan别名：t2统计数据：行数：1数据大小：5基本统计数据：完整列统计数据：无选择运算符表达式：id（类型：int），值（类型：int），ds（类型：字符串）outputColumnNames：_col0，_col1,_col2Statistics:Numrows:1Datasize:5Basicstats:COMPLETEColumnstats:NONEHashTableSinkOperatorstage3是一个map任务，读取t1表中的数据，并在map端执行join。t1表的个数为1行，进行过滤操作。TableScan别名：t1统计信息：行数：1数据大小：6基本统计信息：完整列统计信息：无选择运算符表达式：id（类型：int）、值（类型：int）outputColumnNames：_col0、_col1统计信息：行数：1数据大小：6基本统计数据：COMPLETE列统计数据：无MapJoinOperator条件映射：LeftOuterJoin0到1个键：0_col0（类型：int）1_col0（类型：int）outputColumnNames：_col0、_col1、_col3、_col4、_col5Statistics:Numrows:1Datasize:6Basicstats:COMPLETEColumnstats:NONEStage-0输出结果，最后没有进行任何操作。Stage:Stage-0FetchOperatorlimit:-1ProcessorTree:ListSink总结本文主要结合具体的使用实例详细讲解HiveSQL的LEFTJOIN操作。主要包括两种常见的LEFTJOIN方法，一种是普通的LEFTJOIN，即只包含ON条件，此时没有过滤操作，即返回左表的所有数据。另一种方式是谓词下推，即在关联中使用WHERE条件时，此时会过滤数据。因此，在编写SQL时，尤其要注意这些细节，以免出现意想不到的错误结果。
