hive中，幾種join的區別

數據：

tom,1
jey,2
lilly,7
lilly,8

tom,1
lilly,3
may,4
bob,5

以上兩個為數據，沒有什么意義，全是為了檢測join的使用

看一下兩張表，其實可以看出來，在name一行有重復的，也有不重復的，在id一行1表完全包含2表

1. left join

left join會把左邊的表所有數據列出來，當左邊表有而右邊表沒有的時候，就會用null代替

select *
from jn1
left join jn2
on jn1.name=jn2.name;

jn1.name?? ?jn1.id?? ?jn2.name?? ?jn2.id
tom?? ?1?? ?tom?? ?1
jey?? ?2?? ?NULL?? ?NULL
lilly?? ?7?? ?lilly?? ?3
lilly?? ?8?? ?lilly?? ?3

而右表有左表沒有的就不會顯示了

2. join

join會把兩個表共有的部分篩選出來

select *
from jn1
join jn2
on jn1.name=jn2.name;

jn1.name?? ?jn1.id?? ?jn2.name?? ?jn2.id
tom?? ?1?? ?tom?? ?1
lilly?? ?7?? ?lilly?? ?3
lilly?? ?8?? ?lilly?? ?3

可以看到，共有的部分篩選了出來

3. full join

會把兩者沒有的有的全部數據都選出來，沒有的顯示空值

select *
from jn1
full join jn2
on jn1.name = jn2.name;

jn1.name?? ?jn1.id?? ?jn2.name?? ?jn2.id
tom?? ?1?? ?tom?? ?1
NULL?? ?NULL?? ?bob?? ?5
jey?? ?2?? ?NULL?? ?NULL
lilly?? ?7?? ?lilly?? ?3
lilly?? ?8?? ?lilly?? ?3
NULL?? ?NULL?? ?may?? ?4

4. Join…on 1=1

這種情況會有笛卡爾積的產生，就是表1的每一行都會和表2匹配一下，這樣就會產生指數級的增長

select *
from jn1
join jn2
on 1=1;

jn1.name?? ?jn1.id?? ?jn2.name?? ?jn2.id
tom?? ?1?? ?lilly?? ?3
tom?? ?1?? ?bob?? ?5
tom?? ?1?? ?may?? ?4
tom?? ?1?? ?tom?? ?1
jey?? ?2?? ?lilly?? ?3
jey?? ?2?? ?bob?? ?5
jey?? ?2?? ?may?? ?4
jey?? ?2?? ?tom?? ?1
lilly?? ?7?? ?lilly?? ?3
lilly?? ?7?? ?bob?? ?5
lilly?? ?7?? ?may?? ?4
lilly?? ?7?? ?tom?? ?1
lilly?? ?8?? ?lilly?? ?3
lilly?? ?8?? ?bob?? ?5
lilly?? ?8?? ?may?? ?4
lilly?? ?8?? ?tom?? ?1

5. union

會把查詢結果拼接起來，但是要求兩個查詢結果的行數必須保持一致

否則會報錯

FAILED: SemanticException Schema of both sides of union should match

select *
from jn1
union
select * 
from jn2;

_u1.name?? ?_u1.id
jey?? ?2
lilly?? ?7
bob?? ?5
lilly?? ?3
lilly?? ?8
tom?? ?1
may?? ?4

6. union all

union會組合起來，那么union all可以不

select *
from jn1
union all
select * 
from jn2;

lilly?? ?3
tom?? ?1
may?? ?4
bob?? ?5
tom?? ?1
jey?? ?2
lilly?? ?7
lilly?? ?8

union和union all的區別

從上述的兩個結果就可以看出來了兩者的區別，union會自動去重處理，所以結果把重復的數據去掉了，而union all則不會去重。

注意tips：left join會用之后，right join不用說了吧

總結