createtable dist1(bigint,id1 string)row format delimited fieldsterminatedby'\t';insertinto 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
createtable dist2(id2 string,num bigint)row format delimited fieldsterminatedby'\t';insertinto dist2 values('a',111),('c',333);
a 111
c 333
--正常的left joinselect t1.class,t1.id1,t2.num
from(select class,id1
from dist1
) t1
leftjoin(select id2,num
from dist2
) t2
on t1.id1=t2.id2;
6 a 1114 a 1111 a 1115 b NULL2 b NULL3 c 333
select t1.class,t1.id1,t2.num
from(select class,id1
from dist1
) t1
leftjoin(select id2,num
from dist2
) t2
on t1.id1=t2.id2
where t2.num isNULL;
5 b NULL2 b NULL
select t1.class,t1.id1,t2.id2
from(select class,id1
from dist1
) t1
leftjoin(select id2
from dist2
) t2
on t1.id1=t2.id2;
6 a a
4 a a
1 a a
5 b NULL2 b NULL3 c c
select t1.class,t1.id1
from(select class,id1
from dist1
) t1
leftjoin(select id2
from dist2
) t2
on t1.id1=t2.id2
where t2.id2 isNULL;