HIVE多表查询

多表查询

category.txt
c001,家电
c002,服饰
c003,化妆品
c004,零食
c005,奶茶
c006,蔬菜

全外连接

-- 查询两张表的并集 
select * from t_category c full join t_product p  on c.cid = p.cid;
-- 查询张表不相交的 A表有B表没有   B表有A表没有
select  * from  t_category c full outer join t_product p on c.cid = p.cid where c.cid is null
or  p.cid is null;

左半开连接

左半开连接(left semi join) 会返回左表的记录,前提是其记录对于右表满足on语句的判定条件

相当于 inner join后值返回左表的结果	

select  * from  t_category c left semi join t_product p on c.cid = p.cid;

特点
1.JOIN 子句中右边的表只能在 ON 子句中设置过滤条件，在 WHERE 子句、SELECT 子句或其他地方过滤都不行

select  * from  t_category c left semi join t_product p on c.cid = p.cid where p.price>1000;  -- 报错

--正确
select  * from  t_category c left semi join t_product p on c.cid = p.cid and p.price>1000;

2.最后 select 的结果只许出现左表。

-- 报错 不能查询右表信息
select  p.* from  t_category c left semi join t_product p on c.cid = p.cid;

3.遇到右表重复记录，左表会跳过，而 join 则会一直遍历。这就导致右表有重复值得情况下 left semi join 只产生一条，join 会产生多条，也会导致 left semi join 的性能更高。

select  * from  t_category c left semi  join t_product p on c.cid = p.cid ; -- 效率高 去重
select  c.* from  t_category c inner  join t_product p on c.cid = p.cid ;-- 效率低 不去重

join查询注意事项

• 允许使用复杂的连接表达式,支持非等值查询

  select a.* from a left join b on a.id <> b.id;
  

• 同一查询可以连接2个以上的表

  select a.val,b.val,c.val from a join b on a.key = b.key1 join c on c.key = b.key2
  

• 如果每个表在连接子句中使用相同的列,则Hive将多个表上的连接转为单个mr作业

  -- 由于联接中仅涉及b的key1列 因此转换为1个MR作业执行
  select a.val,b.val,c.val from a join b on a.key = b.key1 join c on c.key = b.key1;
  -- 会转换为两个mr作业  第一个mr作业 a与b连接 将结果与c连接到第二个mr作业中
  select a.val,b.val,c.val from a join b on a.key = b.key1 join c on c.key = b.key2;
  

• join时会缓冲表,因此,将大表放置在最后有助于减少reducer阶段缓存数据所需要的内存.