也谈JOIN在MySQL和Hive中的表现

这几天参加面试，碰到了很多join题，特此总结下。

总体上，join在mysql和hive中的使用差别不大，但若细究区别还是有的。

I.首先来看看mysql：

1、笛卡儿积(cross   join) 

在MySQL中，当不指定on的条件时，inner   join(或称join)和cross   join(笛卡儿积)的执行效果一样，都是两个表的乘积。若指定了on的条件，则inner   join的数据就会明显规律化。可以发现，所有的join中，cross   join是最原始最简单的基本join操作，就好像其他的join都是从cross   join上面变化过去的一样。

2、内连接(inner   join)

对于inner   join：

如上便是，sql是：

select  *  from  table1  inner  join  table2  on  table1.A  =  table2.B;

3、左连接(left   join)  

左连接(left   join)也可以称为left  outer  join  ，但推荐使用left  join。

它的sql是：

select  *  from  table1  left  join  table2  on  table1.A  =  table2.B;

 4、右连接(right   join)

右连接(right   join)也可以称为right  outer  join  ，但推荐使用right  join。

sql描述：

select  *  from  table1  right  join  table2  on  table1.A  =  table2.B;

5、去交集的左连接

sql描述：

select  *  from  table1  left  join  table2  on  table1.A  =  table2.B  where  table2.B  is  null;

严格的，若不想列值为null的出现，可以写成如下sql，后面的去交集的右连接同理

select   table1.*   from   table1   left   join   table2   on   table1.A   =   table2.B   where   table2.B   is   null;

6、去交集的右连接

sql描述：

select  *  from  table1  right  join  table2  on  table1.A  = table2.B  where  table1.A  is  null;

7、全连接

在mysql中是没有全连接(full   join)的，可以用union来代替。

sql描述：

select  *  from  table1  left  join  table2  on  table1.A  = table2.B
union
select  *  from  table1  right join  table2  on  table1.A  =  table2.B;

8、全连接去交集

sql描述：

select  *  from  table1  left  join table2  on  table1.A  =  table2.B  where  table2.B  is  null
union
select  *  from  table1  right  join  table2  on  table1.A  =  table2.B  where  table1.A  is  null;

 

II.再看看hive的join：

1、笛卡儿积(cross  join)

返回两个表的笛卡儿积，若数量很大时，将造成灾难，慎用。

hive  sql描述：

select  *  from  table1  cross  join  table2;

2、内连接(join)

在hive中，内连接(join)是最简单的连接操作。

hive  sql描述：

select  *  from  table1  join  table2  on  (table1.A  =  table2.B);

3、左外连接(left  join)

左连接(left  join)也可以称为left  outer  join  ，但推荐使用left  join。

hive  sql描述：

select  *  from  table1  left  join  table2  on  (table1.A  =  table2.B);

4、右外连接(right  join)

右连接(right  join)也可以称为right  outer  join  ，但推荐使用right  join。

hive  sql描述：

select  *  from  table1  right  join  table2  on  (table1.A  =  table2.B);

5、全外连接(full  outer  join)

即就是两个连接表中的所有行在输出中都有对应的行，full  outer  join 也可以写成 full  join。

hive  sql描述：

select  *  from  table1  full  outer  join  table2  on  (table1.A  =  table2.B);

 6、半连接(left  semi  join)

贴出《Hadoop权威指南》上的一段介绍

可以看出半连接主要是想替代in的作用，因为hive不支持in。对于hive的半连接，left semi join以关键字前面的表为主表，两个表对on的条件字段做交集，返回前面表的记录。仔细对比，就会发现hive的left  semi  join  其实和mysql的第5个去交集的左连接效果一样，都实现了条件左连接，并且去右表的返回左表记录。

hive  sql描述：

select  *  from  table1  left  semi  join  table2  on  (table1.A  =  table2.B);

III.总说：

因为hive本身就和mysql很像，所以它的join也比较相似。不同的是hive有半连接，这主要是分布式计算方面的考虑。