mysql和hive中幾種關聯(lián)(join/union)區(qū)別及說明
更新時間：2025年11月21日 09:50:47 作者：IT小鳥鳥
本文主要介紹了MySQL和Hive中各種連接和合并操作的用法,包括INNER JOIN、LEFT JOIN、RIGHT JOIN、LEFT SEMI JOIN以及UNION和UNION ALL,同時,也提到了Hive在處理連接時的一些特殊規(guī)則和限制
mysql
兩個表如下：
mysql> select * from t1 ; +----+--------+ | id | Name | +----+--------+ | 2 | tim | | 3 | hannah | | 4 | samuel | | 1 | jacob | +----+--------+ mysql> select * from t2 ; +----+--------+ | id | Name | +----+--------+ | 1 | hello | | 2 | jacob | | 3 | hi | | 4 | hannah | +----+--------+
INNER JOIN
Inner join 產(chǎn)生的結(jié)果集，是t1和t2的交集。
mysql> SELECT * FROM t1
-> INNER JOIN t2
-> ON t1.name=t2.name;
+----+--------+----+--------+
| id | Name | id | Name |
+----+--------+----+--------+
| 1 | jacob | 2 | jacob |
| 3 | hannah | 4 | hannah |
+----+--------+----+--------+
JOIN
join和inner join 結(jié)果貌似一致。
join 直接將2表進行笛卡爾積。
mysql> SELECT *
-> FROM t1
-> JOIN t2;
+----+--------+----+--------+
| id | Name | id | Name |
+----+--------+----+--------+
| 1 | jacob | 1 | hello |
| 2 | tim | 1 | hello |
| 3 | hannah | 1 | hello |
| 4 | samuel | 1 | hello |
| 1 | jacob | 2 | jacob |
| 2 | tim | 2 | jacob |
| 3 | hannah | 2 | jacob |
| 4 | samuel | 2 | jacob |
| 1 | jacob | 3 | hi |
| 2 | tim | 3 | hi |
| 3 | hannah | 3 | hi |
| 4 | samuel | 3 | hi |
| 1 | jacob | 4 | hannah |
| 2 | tim | 4 | hannah |
| 3 | hannah | 4 | hannah |
| 4 | samuel | 4 | hannah |
+----+--------+----+--------+
FULL [OUTER] JOIN
實際上，mysql從來都不支持 full outer join，mysql根本就不識別outer關鍵字。
hive才支持
只有如下這一種情況，full join 可以執(zhí)行，加上on條件，就會出現(xiàn)語法報錯。
mysql> SELECT *
-> FROM t1
-> FULL JOIN t2;
+----+--------+----+--------+
| id | Name | id | Name |
+----+--------+----+--------+
| 1 | jacob | 1 | hello |
| 2 | tim | 1 | hello |
| 3 | hannah | 1 | hello |
| 4 | samuel | 1 | hello |
| 1 | jacob | 2 | jacob |
| 2 | tim | 2 | jacob |
| 3 | hannah | 2 | jacob |
| 4 | samuel | 2 | jacob |
| 1 | jacob | 3 | hi |
| 2 | tim | 3 | hi |
| 3 | hannah | 3 | hi |
| 4 | samuel | 3 | hi |
| 1 | jacob | 4 | hannah |
| 2 | tim | 4 | hannah |
| 3 | hannah | 4 | hannah |
| 4 | samuel | 4 | hannah |
+----+--------+----+--------+
LEFT [OUTER] JOIN
Left outer join 產(chǎn)生表 t1 的完全集，而 t2 表中匹配的則有值，沒有匹配的則以null值取代。
mysql> SELECT *
-> FROM t1
-> LEFT JOIN t2
-> ON t1.name=t2.name;
+----+--------+------+--------+
| id | Name | id | Name |
+----+--------+------+--------+
| 1 | jacob | 2 | jacob |
| 2 | tim | NULL | NULL |
| 3 | hannah | 4 | hannah |
| 4 | samuel | NULL | NULL |
+----+--------+------+--------+
RIGHT [OUTER] JOIN
right join 和 left join 類似
mysql> SELECT *
-> FROM t1
-> RIGHT OUTER JOIN t2
-> ON t1.name=t2.name;
+------+--------+----+--------+
| id | Name | id | Name |
+------+--------+----+--------+
| NULL | NULL | 1 | hello |
| 1 | jacob | 2 | jacob |
| NULL | NULL | 3 | hi |
| 3 | hannah | 4 | hannah |
+------+--------+----+--------+
UNION 與 UNION ALL
UNION 操作符用于合并兩個或多個 SELECT 語句的結(jié)果集。
mysql> SELECT name FROM t1
-> UNION
-> SELECT name FROM t2;
+--------+
| name |
+--------+
| jacob |
| tim |
| hannah |
| samuel |
| hello |
| hi |
+--------+
本來有8個名字，但有2個重復，所以去除掉重復的名字，結(jié)果只有6個。
mysql> SELECT id,name FROM t1
-> UNION
-> SELECT id,name FROM t2;
+----+--------+
| id | name |
+----+--------+
| 1 | jacob |
| 2 | tim |
| 3 | hannah |
| 4 | samuel |
| 1 | hello |
| 2 | jacob |
| 3 | hi |
| 4 | hannah |
+----+--------+
id和name作為一個整體，沒有重復的，所以結(jié)果顯示8條。
mysql> SELECT name FROM t1
-> UNION all
-> SELECT name FROM t2;
+--------+
| name |
+--------+
| jacob |
| tim |
| hannah |
| samuel |
| hello |
| jacob |
| hi |
| hannah |
+--------+
union all，將結(jié)果無腦堆在一起。（相當于兩個查詢語句）
注意：UNION 內(nèi)部的 SELECT 語句必須擁有相同數(shù)量的列。列也必須擁有相似的數(shù)據(jù)類型。同時，每條 SELECT 語句中的列的順序必須相同。UNION 只選取記錄，而UNION ALL會列出所有記錄。
hive
hive里面使用join時注意：
1、 只支持等值鏈接；多個條件，支持 and，不支持 or /大于/小于
例如：
SELECT a.* FROM a JOIN b ON (a.id = b.id) SELECT a.* FROM a JOIN b ON (a.id = b.id AND a.department = b.department) 是正確的； 然而： SELECT a.* FROM a JOIN b ON (a.id>b.id)是錯誤的。
2、 可以 join 多于 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的join分成三類：
1、內(nèi)連接 inner join 2、外連接 left outer join right outer join full outer join 3、半連接 left semi join
下面做實驗：
2個表如下：
hive> select * from tablea; OK tablea.id tablea.name 1 huangbo 2 xuzheng 4 wangbaoqiang 6 huangxiaoming 7 fengjie 10 liudehua hive> select * from tableb; OK tableb.id tableb.age 2 20 4 50 7 80 10 22 12 33 15 44
inner join（內(nèi)連接）（把符合兩邊連接條件的數(shù)據(jù)查詢出來）
hive> select * from tablea a inner join tableb b on a.id=b.id; OK a.id a.name b.id b.age 2 xuzheng 2 20 4 wangbaoqiang 4 50 7 fengjie 7 80 10 liudehua 10 22
left join（左連接，等同于 left outer join）
- 1、以左表數(shù)據(jù)為匹配標準，左大右小
- 2、匹配不上的就是 null
- 3、返回的數(shù)據(jù)條數(shù)與左表相同
hive> select * from tablea a left join tableb b on a.id=b.id; OK a.id a.name b.id b.age 1 huangbo NULL NULL 2 xuzheng 2 20 4 wangbaoqiang 4 50 6 huangxiaoming NULL NULL 7 fengjie 7 80 10 liudehua 10 22
right join（右連接，等同于 right outer join）
- 1、以右表數(shù)據(jù)為匹配標準，左小右大
- 2、匹配不上的就是 null
- 3、返回的數(shù)據(jù)條數(shù)與右表相同
hive> select * from tablea a right join tableb b on a.id=b.id; OK a.id a.name b.id b.age 2 xuzheng 2 20 4 wangbaoqiang 4 50 7 fengjie 7 80 10 liudehua 10 22 NULL NULL 12 33 NULL NULL 15 44
left semi join（左半連接）
因為 hive 不支持 in/exists 操作（1.2.1 版本的 hive 支持 in 的操作），所以用該操作實現(xiàn)，并且是 in/exists 的高效實現(xiàn)。
hive> select * from tablea a left semi join tableb b on a.id=b.id; OK a.id a.name 2 xuzheng 4 wangbaoqiang 7 fengjie 10 liudehua
可以修改為 in 的那種寫法：
hive> select * from tablea where a.id in(select b.id from b); OK tablea.id tablea.name 2 xuzheng 4 wangbaoqiang 7 fengjie 10 liudehua
雖然用in 也能實現(xiàn)，但不建議使用。一般用 left semi join 實現(xiàn)。
full outer join（完全外鏈接）
Full outer join 產(chǎn)生A和B的并集。但是需要注意的是，對于沒有匹配的記錄，則會以null做為值。
hive> select * from tablea a full outer join tableb b on a.id=b.id; OK a.id a.name b.id b.age 1 huangbo NULL NULL 2 xuzheng 2 20 4 wangbaoqiang 4 50 6 huangxiaoming NULL NULL 7 fengjie 7 80 10 liudehua 10 22 NULL NULL 12 33 NULL NULL 15 44
總結(jié)
以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。
相關文章
鎖是計算機協(xié)調(diào)多個進程或線程并發(fā)訪問某一資源的機制（避免爭搶）。本文就來通過幾個簡單的實力為大家詳細講講MySQL中的鎖機制吧2023-02-02
這篇文章主要給大家介紹了關于MySQL命令行操作時的編碼問題，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧2021-04-04
這篇文章主要介紹了從入門到精通MySQL聯(lián)合查詢,本文通過實例代碼給大家介紹的非常詳細,需要的朋友可以參考下2025-07-07
這篇文章主要介紹了MySQL如何開啟遠程連接權(quán)限問題,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2024-09-09
本篇文章對MySQL的數(shù)學函數(shù)進行了詳細的介紹。需要的朋友參考下2013-05-05
在MySQL集群中,主庫更新會同步到從庫,但從庫更新不同步到主庫,主從復制能分攤壓力,本文就來介紹一下mysql搭建主從復制的實現(xiàn)步驟,感興趣的可以了解一下2024-11-11
MySQL數(shù)據(jù)庫中varchar類型的數(shù)字比較大小的方法
varchar類型的數(shù)據(jù)是不能直接比較大小的，那么MySQL數(shù)據(jù)庫中varchar類型如何進行數(shù)字比較大小的，本文就詳細的介紹一下2021-11-11
這篇文章主要介紹了MySQL truncate table語句的使用，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧2021-03-03
mysql的InnoDB,支持事務和行級鎖，可以使用行鎖來處理用戶提現(xiàn)等業(yè)務。使用mysql鎖的時候有時候會出現(xiàn)死鎖，要做好死鎖的預防。這篇文章通過實例應用給大家講解2016-12-12
sql四大排名函數(shù)之ROW_NUMBER、RANK、DENSE_RANK、NTILE使用介紹
這篇文章主要介紹了sql四大排名函數(shù)之ROW_NUMBER、RANK、DENSE_RANK、NTILE使用，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧2020-08-08