Impala Join类型介绍

多表关联是在数据分析中非常常见的一个操作，impala作为一个ad-hoc的查询系统，也提供了多种join类型。本文将结合一个简单的例子，给大家介绍下各种join的特点。

首先，我们构建了两个简单的测试表：employee和department，并往这两个表中分别插入了若干条数据，如下所示：

     

为了验证效果，我们故意往employee表中插入了几条dept_id为0的数据，表示该雇员不属于任何部门。数据已经构造完毕，下面我们就一一介绍每种join的特点。

[INNER] JOIN

这是最常见的join方式，其中inner关键字可以省略不写，效果与join等同。这种join类型，只有当左右表中，都能匹配上的时候，才会输出结果，我们使用如下的SQL进行测试：

select id,name,depart_name from employee inner join department
on employee.dept_id = department.depart_id;

结果如下所示：

我们可以看到，employee表中没有分配部门（dept_id=0）的雇员以及department表中没有分配雇员的部门，都没有输出在结果中。

LEFT [OUTER] JOIN

这种join也属于比较常见的一种，其中outer关键字可以省略不写。该join会将左表中的所有记录都输出，即使右边中没有与之匹配的结果，我们使用如下的SQL进行测试：

select id,name,depart_name from employee left outer join department
on employee.dept_id = department.depart_id;

我们从输出的结果可以看到，在employee表中，即使雇员没有分配部门，在这种join类型下也会被输出，而相应的右表数据则会直接显示为NULL。

RIGHT [OUTER] JOIN

这种join方式刚好与left相反，会将右边中所有的记录输出，即使没有在左表中匹配到相应的记录，我们使用如下的SQL进行测试：

我们可以从输出结果看到，即使部门没有分配雇员，仍然会输出，相应左表的数据都会显示为NULL。

FULL [OUTER] JOIN

这种join方式就相当于left join和right join的集合体，会将两边所有的数据都进行join，然后输出，包括左右表中没有匹配的记录，我们通过如下SQL进行测试：

select id,name,depart_name from employee full outer join department
on employee.dept_id = department.depart_id;

我们可以看到，无论是没有分配部门的雇员，还是没有分配雇员的部门，都输出在结果集当中，而对应的没有匹配到的列，数据都显示为NULL。

SEMI JOIN

这种join是相对使用比较少的join类型，需要与left/right结合使用。例如，如果我们使用left semi join的话，那就只会返回左表中匹配到的数据（注意这里与left outer join的区别），我们使用如下的SQL进行测试：

select id,name from employee left semi join department
on employee.dept_id = department.depart_id;
select depart_name from employee right semi join department
on employee.dept_id = department.depart_id;

      

left semi join与right semi join的结果，分别如下的左图和右图所示，结合SQL我们可以发现，left semi join其实就是只返回left outer join中，左表的数据，right semi join也是同样的道理。需要注意的是，我们在使用left semi join的时候，无法select右表的列，否则SQL会报错，如下的SQL会直接返回失败：

select id,name,depart_name from employee left semi join department
on employee.dept_id = department.depart_id;

ANTI JOIN

这种join方式与semi-join一样，都是只返回一张表的结果，但是返回的是不匹配的结果，即取反的结果。例如，left anti join返回的是左表中，没有在右表匹配到的记录，这里解释起来比较绕，我们直接看这个例子：

select id,name from employee left anti join department
on employee.dept_id = department.depart_id;

select depart_name from employee right anti join department
on employee.dept_id = department.depart_id;

      

我们可以看到，对于left anti join返回的结果，是对于left semi join的取反结果，即返回的是没有分配部门的雇员。

NATURAL JOIN

由于自然连接一般结果集非常大，所以impala为了避免这种情况，不支持使用natural join，如果我们直接使用如下的SQL：

select id,name,depart_name from employee natural join department
on employee.dept_id = department.depart_id;

会直接返回失败，这是由于impala会将natural当成是表employee的别名。如果说我们确实需要使用自然连接，可以使用corss join，如下所示：

select id,name,depart_name from employee cross join department;

需要注意的是，当我们使用corss join的时候，就不能再加关键字on，否则SQL会直接报错。

参考链接

https://www.cloudera.com/documentation/enterprise/5-16-x/topics/impala_joins.html

https://www.cloudera.com/documentation/enterprise/5-16-x/topics/impala_tutorial.html#tut_cross_join