hive------join语法介绍

一、在多表查询的时候，由于表与表之间有关联性，所有hive提供了join的语法，基本类似sql的join语法。主要分为以下五类：
1. 内连接(JOIN)
2. 外链接({LEFT|RIGHT|FULL} [OUTER] JOIN)
3. 半连接(LEFT SEMI JOIN)
4. 笛卡尔连接(CROSS JOIN)
5. 其他连接方式(eg. mapjoin等)
语法格式：
left_table_reference [join type] right_table_ref [join_condition]* ([join type] right_table_ref [join_condition])

二、使用join时的建议和问题
1. 等值连接：hive中的所有连接条件必须为等值连接条件，不支持<>等非等值连接方式。
2. 多表连接：多表连接的时候，一般先进行left semi join，然后再进行join, 再进行外连接。(减少数据量)。
3. join过滤条件，可以将where的过滤条件移动到join的过滤条件中去，这样可以减少网络数据量。
4. join执行顺序都是从左到右，不管是那种join方式，那么一般将大的表放到右边，这样可以节省内存&减少网络传输。
5. mapjoin只适合连接表是小表的情况，是一种空间换时间的解决方案。

三、内连接语法
内连接(JOIN)主要作用是获取连接的两张表全部匹配的数据，如果不给定join_condition的话，会进行笛卡尔乘积。笛卡尔连接(CROSS JOIN)和内连接语法一样，区别在于：笛卡尔连接是对内连接的一种优化。语法格式为:
table_reference [cross] join table_factor [join_condition]

四、外连接语法
外连接的主要作用是保留一部分没有匹配的数据。左外连接(LEFT OUTER JOIN)的结果是包括左表中的所有行，如果左表中的某一个行在右表中不存在，那么则在相关联的结果集中右表的所有选择列值均设置为空值。右外连接(RIGHT OUTER JOIN)就是左外连接的反先连接，将返回右表的所有行，左表进行空值填充。全外连接(FULL OUTER JOIN)返回左表和右表的所有行，关联表中没有匹配值的直接设置为空值。语法格式为:
table_reference {left|right|full} [outer] join table_factor join_condition

五、hive特有的半连接语法，left semi join
半连接(LEFT SEMI JOIN)是hive特有的，hive中不支持in/exists操作，所以hive提供了一个替代方案。需要注意的是，被连接的表(右表)，不能出现在查询列/其他部分(where等)中，只能出现在on字句中。(出现也是无效的)。提出半连接的主要作用其实是提高查询效率，真正来讲的话，hive中可以使用其他连接方式来代替半连接，但是就效率而已的话，还是半连接比较高效。语法格式：
table_reference LEFT SEMI JOIN table_factor join_condition

六、hive特有的mapjoin语法
如果所有被连接的表都是小表，那么可以使用mapjoin,将需要连接的表数据全部读入mapper端内存中。也就是说你使用mapjoin的前提就是你的连接数据比较小，mapjoin需要和其他join方式一起使用，一般情况下使用mapjoin的时候，推荐使用内连接。语法格式为:
select /+ MAPJOIN(table_ref1) / … from table_ref join table_ref1 on ….;

七、join语法实例：

classes表数据：

1,班级12,班级23,班级3

student表数据

11,1,张三12,1,李四13,1,王五21,2,Tom22,2,Gerry23,2,Simon24,2,Jim91,\N,Jeffrey92,\N,Leo93,\N,even94,\N,aaron95,\N,addison

内连接语法：
1.获取学生和班级之间完全匹配的数据。

select students.*,classes.* from classes join students on classes.classid=students.classid;----------select students.*,classes.* from classes cross join students on classes.classid=students.classid;   

外链接语法：
1.获取全部学生的班级信息，如果该学生没有分配班级，那么班级信息显示为null。

select students.*, classes.* from students left join classes on students.classid = classes.classid;

1. 获取全部班级的学生信息，如果某个班级没有学生，那么学生信息显示为null。(作业2)

select * from students right join classes on students.classid=classes.classid;

1. 获取全部信息，如果没有匹配数据的显示null。(作业3)

select * from students full join classes on students.classid=classes.classid;

半连接：
1. 获取学生表中班级id在班级表中的所有学生信息。

sql: select students.* from students where classid in (select distinct classid from classes);

原hql: select students.* from students  join classes on students.classid = classes.classid;

新hql: select students.* from students left semi join classes on students.classid=classes.classid;

mapjoin:

select /*+ mapjoin(classes) */ * from students join classes on students.classid=classes.classid;