数据:
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不用说了吧