042.hiveLEFT 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  ;