hive中left outer join之后加and 或者 where 的测试

hive> select  * from test1;
OK
1       a       123
2       b       678
3       c       567
4       d       999
5       e       555

hive> select  * from test2;
OK
1       a       kkk
2       b       lll
3       c       hhh
8       s       999
7       r       888


select  test1.*,test2.*  from test1 left outer join  test2 on test1.a=test2.a and test1.b=test2.b
1       a       123     1       a       kkk
2       b       678     2       b       lll
3       c       567     3       c       hhh
4       d       999     NULL    NULL    NULL
5       e       555     NULL    NULL    NULL


select  test1.*,test2.*  from test1 left outer join  test2 on test1.a=test2.a and test1.b=test2.b and test1.c=123
1       a       123     1       a       kkk
2       b       678     NULL    NULL    NULL
3       c       567     NULL    NULL    NULL
4       d       999     NULL    NULL    NULL
5       e       555     NULL    NULL    NULL

select  test1.*,test2.*  from test1 left outer join  test2 on test1.a=test2.a and test1.b=test2.b where test1.c=123
1       a       123     1       a       kkk

 

转载于:https://www.cnblogs.com/52hadoop/p/3757076.html