Hive连接的类型

一 INNER JOIN 内连接

所谓内连接其实就是指基于两张表共有的join 字段的值，进行连接，不相等的值不会出现在查询结果

比如用户表和订单表：

SELECT* FROM order o INNER JOIN user u ON o.cid = u.id;

内连接的结果就是：

内连接分为显示和隐式两种：

隐式内连接

SELECT* FROM emp e, dept d WHERE e.deptno = d.deptno;

显示内连接：

SELECT* FROM emp e JOIN dept d ON e.deptno = d.deptno;

 

 

 

二 外连接 OUTER JOIN

外连接分为两种：

2.1左外连接 LEFTOUTER JOIN

根据指定key进行join,左边表的数据全部显示，右边表如果没有对应的数据则显示为空

SELECTu.name,u.address,u.gender,u.id,o.cid,o.id,o.total FROM user u LEFT OUTER JOIN ordero ON u.id = o.cid;

还是以用户表和订单表为列子左外连接的结果就是：

2.2右外连接 RIGHT OUTER JOIN

SELECTu.name,u.address,u.id,o.cid,o.id,o.total FROM user u RIGHT

OUTERJOIN order o ON u.id = o.cid

右外连接的结果就是：

三 FULL JOIN(FULLOUTER JOIN) 全连接或者全外连接

它会返回两张表所有行，没有对应数据显示为NULL

还是以上述列子为例：

SELECTu.name,u.address,u.id,o.cid,o.id,o.total FROM user u FULL

OUTERJOIN order o ON u.id = o.cid

结果如下：

四 交叉连接(笛卡尔积)

交叉连接就是前表每一条记录都和后面的表每一条记录组合，组合结果等前面表的数据条数*后面表的数据条数，比如A表2条记录，B表3条记录，笛卡尔积就是6。

笛卡尔积： 假设集合A=a,b，集合B=0,1,2，则两个集合的笛卡尔积为(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)

那么交叉连接就是笛卡尔积的体现：

隐式的笛卡尔积：

SELECT* FROM user u,order o;

显示的交叉连接：

SELECT* FROM user u CROSS JOIN order o;

如果需要对笛卡尔积进行过滤，我们可以使用ON或者WHERE子句

比如：

SELECT* FROM user u,order o WHERE u.id = o.id；

SELECT* FROM user u,order o WHERE u.id = 1000；

SELECT* FROM user u CROSS JOIN order o ON u.id=o.id WHERE u.id=1000;

Hive中严格模式下，不允许使用笛卡尔积。

 

五SEMIJOIN

SEMIJOIN主要是为了解决类似IN,EXISTS这样的场景，因为SQL常用的IN，EXISTS在Hive中是不支持的。

比如我要在SQL中实现这样的语句：

SELECTe.empno,e.ename,e.job FROM emp e WHERE e.deptno IN  (

SELECTdeptno FROM dept)

那么在Hive中就这样实现：

SELECTe.empno,e.ename,e.job FROM emp e LEFT SEMI JOIN dept d ON (e.deptno =d.deptno);

注意

1在SELECT后面不能接表dept的字段，是不支持的。

2在ON中是不支持OR连接的

hive (hadoop)> SELECT e.empno,e.ename,e.job FROM emp e LEFT SEMIJOIN dept d ON (d.deptno = 10 OR d.deptno = 20);

FAILED: SemanticException [Error 10019]: Line 1:66 OR not supportedin JOIN currently '20'