mysql和hive中幾種關(guān)聯(lián)(join/union)區(qū)別及說(shuō)明
更新時(shí)間：2025年11月21日 09:50:47 作者：IT小鳥(niǎo)鳥(niǎo)
本文主要介紹了MySQL和Hive中各種連接和合并操作的用法,包括INNER JOIN、LEFT JOIN、RIGHT JOIN、LEFT SEMI JOIN以及UNION和UNION ALL,同時(shí),也提到了Hive在處理連接時(shí)的一些特殊規(guī)則和限制
mysql
兩個(gè)表如下：
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表進(jìn)行笛卡爾積。
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
實(shí)際上，mysql從來(lái)都不支持 full outer join，mysql根本就不識(shí)別outer關(guān)鍵字。
hive才支持
只有如下這一種情況，full join 可以執(zhí)行，加上on條件，就會(huì)出現(xiàn)語(yǔ)法報(bào)錯(cuò)。
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 表中匹配的則有值，沒(méi)有匹配的則以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 操作符用于合并兩個(gè)或多個(gè) SELECT 語(yǔ)句的結(jié)果集。
mysql> SELECT name FROM t1
-> UNION
-> SELECT name FROM t2;
+--------+
| name |
+--------+
| jacob |
| tim |
| hannah |
| samuel |
| hello |
| hi |
+--------+
本來(lái)有8個(gè)名字，但有2個(gè)重復(fù)，所以去除掉重復(fù)的名字，結(jié)果只有6個(gè)。
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作為一個(gè)整體，沒(méi)有重復(fù)的，所以結(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é)果無(wú)腦堆在一起。（相當(dāng)于兩個(gè)查詢語(yǔ)句）
注意：UNION 內(nèi)部的 SELECT 語(yǔ)句必須擁有相同數(shù)量的列。列也必須擁有相似的數(shù)據(jù)類型。同時(shí)，每條 SELECT 語(yǔ)句中的列的順序必須相同。UNION 只選取記錄，而UNION ALL會(huì)列出所有記錄。
hive
hive里面使用join時(shí)注意：
1、 只支持等值鏈接；多個(gè)條件，支持 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)是錯(cuò)誤的。
2、 可以 join 多于 2 個(gè)表
例如：
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
下面做實(shí)驗(yàn)：
2個(gè)表如下：
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ù)查詢出來(lái)）
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ù)為匹配標(biāo)準(zhǔn)，左大右小
- 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ù)為匹配標(biāo)準(zhǔn)，左小右大
- 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（左半連接）
因?yàn)?hive 不支持 in/exists 操作（1.2.1 版本的 hive 支持 in 的操作），所以用該操作實(shí)現(xiàn)，并且是 in/exists 的高效實(shí)現(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 的那種寫(xiě)法：
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 也能實(shí)現(xiàn)，但不建議使用。一般用 left semi join 實(shí)現(xiàn)。
full outer join（完全外鏈接）
Full outer join 產(chǎn)生A和B的并集。但是需要注意的是，對(duì)于沒(méi)有匹配的記錄，則會(huì)以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é)
以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。
相關(guān)文章
Mysql TIMESTAMPDIFF函數(shù)示例詳解
這篇文章主要介紹了Mysql TIMESTAMPDIFF函數(shù)示例詳解,TIMESTAMPDIFF函數(shù)返回datetime_expr2 - datetime_expr1的結(jié)果，其中datetime_expr1和datetime_expr2可以是DATE或DATETIME類型值，本文給大家詳細(xì)講解，需要的朋友可以參考下2023-03-03
數(shù)據(jù)庫(kù)的用戶帳號(hào)管理基礎(chǔ)知識(shí)
數(shù)據(jù)庫(kù)的用戶帳號(hào)管理基礎(chǔ)知識(shí)...2006-11-11
這篇文章主要介紹了mysql全文模糊搜索MATCH AGAINST方法示例，小編覺(jué)得挺不錯(cuò)的，現(xiàn)在分享給大家，也給大家做個(gè)參考。一起跟隨小編過(guò)來(lái)看看吧2018-11-11
這篇文章主要介紹了MySQL實(shí)現(xiàn)分布式鎖，文章圍繞主題展開(kāi)詳細(xì)的內(nèi)容介紹，具有一定的參考價(jià)值，需要的小伙伴可以參考一下2022-08-08
Mysql語(yǔ)句快速?gòu)?fù)習(xí)教程(全)
這篇文章主要介紹了Mysql語(yǔ)句快速?gòu)?fù)習(xí)教程(全)的相關(guān)資料,需要的朋友可以參考下2016-04-04
解析MySQL中存儲(chǔ)時(shí)間日期類型的選擇問(wèn)題
這篇文章主要介紹了解析MySQL中存儲(chǔ)時(shí)間日期類型的選擇問(wèn)題，具有一定參考價(jià)值，需要的朋友可以了解。2017-10-10
在本篇文章里小編給大家整理的是關(guān)于mysql聲明游標(biāo)的方法和實(shí)例內(nèi)容，需要的朋友們可以參考下。2020-09-09
MySql學(xué)習(xí)筆記之事務(wù)隔離級(jí)別詳解
這篇文章主要給大家介紹了關(guān)于MySql學(xué)習(xí)筆記之事務(wù)隔離級(jí)別的相關(guān)資料，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2021-05-05
MySQL優(yōu)化子查詢的實(shí)現(xiàn)示例
本文主要介紹了MySQL子查詢優(yōu)化經(jīng)驗(yàn),指出子查詢易因嵌套深、索引失效、臨時(shí)表排序等問(wèn)題導(dǎo)致性能問(wèn)題,具有一定的參考價(jià)值,感興趣的可以了解一下2025-09-09
Mysql實(shí)現(xiàn)水平分庫(kù)的示例代碼
本文主要介紹了Mysql實(shí)現(xiàn)水平分庫(kù)的示例代碼，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2023-06-06