042.hive-LEFT SEMI JOIN 、 left anti join、inner join、full join

1. left semi join

类似 in \exists 的功能，但是更高效

a left join  b   若1对多，结果集会出现多条数据，但是left semi join 只会筛选出a表中包含过关联条件的数据不会增加

 

 

2. left anti join

a left anti join b 的功能是在查询过程中，剔除a表中和b表有交集的部分

 

3.

inner join 

返回交集部分

 

4. full join

 

CREATE TABLE test001 (
  id                  bigint        ,
  shop_name                  string             COMMENT '门店名称' 
);

CREATE TABLE test002 (
  id                  bigint        ,
  shop_name                  string             COMMENT '门店名称' 
);
 
insert  into  test001( id,shop_name ) values(1,'shop001'),(2,'shop002'),(3,'shop003'),(4,'shop004');
insert  into  test001( id,shop_name ) values (4,'shop00414');
insert  into  test002( id,shop_name ) values(5,'shop001'),(2,'shop002'),(3,'shop003'),(4,'shop004');
insert  into  test002( id,shop_name ) values (4,'shop00424');

 

 left semi join

select 
a.* 
from 
    test001  a 
left semi join 
    test002  b 
on a.id =b.id  ;

 

 

 left  join

select 
a.*,b.*
from 
    test001  a 
left  join 
    test002  b 
on a.id =b.id  ;

 

 

 

 

full join

 

select 
a.*,b.*
from 
    test001  a 
full join 
    test002  b 
on a.id =b.id  ;

 

 

inner join

 

select 
a.*,b.*
from 
    test001  a 
inner join 
    test002  b 
on a.id =b.id  ;