SQL中left join的一些易忽略点

=Start=

缘由:

整理记录一下最近在使用left join时碰到的坑,方便以后参考,避免以后再出现实际和预期不一致的情况。

正文:

参考解答:

在学习SQL的左连接(left join)时看到的一句话,让我快速的了解了它的功能:

以LEFT [OUTER] JOIN关键字前面的表作为主表,和其他表进行关联,返回记录和主表的记录数一致,关联不上的字段置为NULL。

但是它的结论不总是正确的!

在使用left join的时候,一定要注意被关联表(除了第一张表之外的表)的数据里面有没有重复的内容,如果有的话,结果记录和主表的记录就不一定一致(即便使用distinct关键字去重)。

下面以2张测试表做left join来说明一下。

tableA的内容如下:


col1col2col3
1a1xy
2a2xz
3a3yy

tableB的内容如下:


col1col2
1a1m
2a1n
3a2m
4a2m

此时进行left [outer] join的测试SQL:

select
x1.*, x2.*
from
(select
*
from
tableA
)x1
left outer join
(select
*
from
tableB
)x2
on x1.col1 = x2.col1
;

当tableB中没有第4行(a2,m)时,两表join的结果(有4行)如下:

a1 a1 
a1 a1 
a2 a2 
4a3 NULL NULL 

可以看到,其结果为4行,比tableA的3行要多出1行,因为在tableB中在col1列里面的a1字段有重复项(虽然整行的内容并不重复)。

当在tableB中加入第4行后,结果就再增加了1行(有5行),如果用distinct关键字去重的话会滤掉对应重复的内容(有4行,但依然比tableA的3行要多1行):

select distinct
x1.col1,
x1.col2,
x1.col3,
x2.col1 as x2_col1,
x2.col2 as x2_col2
from
(select
*
from
tableA
)x1
left outer join
(select
*
from
tableB
)x2
on x1.col1 = x2.col1
;

所以,在使用left join的时候,一定要注意被关联表的数据里面有没有重复的内容,如果有的话,结果记录和主表的记录就不一定一致(即便使用distinct关键字去重)。

参考链接:

https://stackoverflow.com/questions/39271323/hive-left-join-vs-join-gives-different-results-with-filter-in-on-clause

https://cwiki.apache.org/confluence/display/Hive/OuterJoinBehavior

=END=

声明: 除非注明,ixyzero.com文章均为原创,转载请以链接形式标明本文地址,谢谢!
https://ixyzero.com/blog/archives/4780.html

《SQL中left join的一些易忽略点》上的一个想法

  1. `
    mark一下:
    Hive SQL中的left outer join还是要注意一下,假设是两张表A和B进行关联,其中B是数据量较小的表,如果希望避免数据偏移以及提速的话(在只需要取出两者中都存在的特征时),【最好把表B(数据量较小的表)放在前面】,【然后在on条件后面加上where的过滤】,否则直接按顺序进行A和B的关联时,会发现产出的结果太大,且不符合自己的需求。
    `

发表评论

电子邮件地址不会被公开。 必填项已用*标注