hive中的join

HIVE 支持通常的SQL JOIN语句,但是只支持等值连接;hive目前还不支持在ON 子句中使用OR；

内连接(INNER JOIN),只有进行连接的两个表中都存在与连接标准相匹配的数据才会被保留下来。

eg：

SELECt A.FIELDS, B.FIELDS FROM TABLE_A A JOIN TBALE_B B ON A.KEY = B.KEY 
--这个例子中，只有A表和B表的交集才会被保留下来；

eg：

SELECt A.FIELDS, B.FIELDS FROM TABLE_A A LEFT OUTER JOIN TBALE_B B ON A.KEY = B.KEY where A.A.FIELD_1 = '001'
--在LEFT OUTER JOIN连接中，join左边表中符合where 子句中的所有记录都会被返回，join右边表中如果没有符合ON 后面连接条件的记录时，那么select中指定右表的列就返回NULL；

对于外连接来说,分区过滤条件不能放置在ON语句中,外连接会忽略掉分区过滤条件,不过对于内连接使用这样的过滤谓词是起作用的;

eg：

--外连接:
select a.user_id,b.city,a.dt from dwd_user a left outer join dwd_user_app_pv b on a.user_id = b.user_id and  a.dt = '20190610' limit 10;
--dt是分区字段 
结果:
47  NULL    20190612
47  NULL    20190610
48  NULL    20190612
48  NULL    20190610
49  NULL    20190612
49  NULL    20190610
50  NULL    20190612
50  NULL    20190610
--内连接
select a.user_id,b.city,a.dt from dwd_user a inner join dwd_user_app_pv b on a.user_id = b.user_id and  a.dt = '20190610' limit 10;
结果:
a.user_id   b.city  a.dt
301 滁州市 20190610
301 滁州市 20190610
301 滁州市 20190610
301 滁州市 20190610
301 滁州市 20190610
302 通化市 20190610
302 通化市 20190610
302 通化市 20190610
302 通化市 20190610
302 通化市 20190610
--结论:on语句中的分区过滤条件外连接中是无效的,在内连接中是有效的;

右外连接会返回右表中所有符合where语句的记录,左表中匹配不上用NULL代替;

eg:

SELECt A.FIELDS, B.FIELDS FROM TABLE_A A RIGHT OUTER JOIN TBALE_B B ON A.KEY = B.KEY where A.A.FIELD_1 = '001'
--在RIGHT OUTER JOIN连接中，join右边表中符合where 子句中的所有记录都会被返回，join左边表中如果没有符合on 后面连接条件的记录时，那么select中指定左表的列就返回NULL；

全外连接( FULL OUTHER JOIN)将会返回所有表中符合where 语句条件的所有记录。如果任一表的指定字段没有符合条件的值的话，那么就使用NULL值代替；

eg：

SELECT A.FIELDS, B.FIELDS FROM TABLE_A A FULL OUTHER JOIN TBALE_B B ON A.KEY = B.KEY where A.A.FIELD_1 = '001'
--在FULL OUTHER JOIN连接中，将会返回所有表中符合where 语句条件的所有记录，如果select 中任一表的指定字段没有符合条件的值的话，那么就使用NULL值代替；

左半开连接（LEFT SEMI JOIN）会返回左表的记录，前提是其记录对于右表满足ON语句中条件；是 IN/EXISTS 子查询的一种更高效的实现；

LEFT SEMI JOIN 的限制是， JOIN 子句中右边的表只能在 ON 子句中设置过滤条件，SELECT 和WHERe 语句不能引用到右边表中的字段；

SEMI JOIN比通常的INNER JOIN更高效,因为对于左表中一条指定的记录,在右表中一旦找到匹配的记录,hive就会立刻停止扫描;

eg：

SELECT a.key, a.value FROM a WHERe a.key in
(SELECt b.key FROM B);
可以被重写为：
SELECt a.key, a.value FROM a LEFT SEMI JOIN b on a.key = b.key