HIVE实例说明join ,semi join, outer join，anti join的区别



现在有两张表table_a和table_b ,其中每张表的数据如下 
  table_a 表中数据：

       Id        Name      Score 

        1            a              90

        2            b              80

        3            c              85

  table_b 表中数据：

       Id        Name      Score 

        2            b              80

        3            c              85

        4            d              90
 

(1) left join

<strong></strong><pre class="html" name="code"><span style="font-size:12px;">select a.id,a.name,a.score from table_a a left join table_b b on a.id=b.id;</span>

 结果如下：

      1            a              90

        2            b              80

        3            c              85

(2) left outer join

select a.id,a.name,a.score from table_a a left outer join table_b b on a.id=b.id;

   结果如下：

      1            a              90

        2            b              80

        3            c              85

(3) left semi join

<pre class="html" name="code">select a.id,a.name,a.score from table_a a left semi join table_b b on a.id=b.id;

 结果如下：

        2            b              80

        3            c              85

(4) full outer  join

select a.id,a.name,a.score from table_a a full outer join table_b b on a.id=b.id;

 结果如下：

      1            a              90

        2            b              80

        3            c              85

        NULL       NULL         NULL