Hive 日常小实验

1. join

create table dist1( bigint,id1 string)
row format delimited fields terminated by '\t';
insert into dist1 values(1,'a'),(2,'b'),(3,'c'),(4,'a'),(5,'b'),(6,'a');
1	a
2	b
3	c
4	a
5	b
6	a

create table dist2(id2 string,num bigint)
row format delimited fields terminated by '\t';
insert into dist2 values('a',111),('c',333);
a	111
c	333

--正常的left join
select t1.class,t1.id1,t2.num
from
(
    select class,id1
    from dist1
) t1
left join
(
     select id2,num
     from dist2
 ) t2
 on t1.id1=t2.id2;

6	a	111
4	a	111
1	a	111
5	b	NULL
2	b	NULL
3	c	333

select t1.class,t1.id1,t2.num
from
(
    select class,id1
    from dist1
) t1
left join
(
     select id2,num
     from dist2
 ) t2
 on t1.id1=t2.id2
 where t2.num is NULL;

5	b	NULL
2	b	NULL

select t1.class,t1.id1,t2.id2
from
(
    select class,id1
    from dist1
) t1
left join
(
     select id2
     from dist2
 ) t2
 on t1.id1=t2.id2;

6	a	a
4	a	a
1	a	a
5	b	NULL
2	b	NULL
3	c	c

select t1.class,t1.id1
from
(
    select class,id1
    from dist1
) t1
left join
(
     select id2
     from dist2
 ) t2
 on t1.id1=t2.id2
 where t2.id2 is NULL;
 

5	b
2	b