一 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'