Hive中的連接操作詳解

1. 全連接（FULL JOIN）
  • 作用：返回兩個表的所有記錄，匹配失敗時用NULL填充缺失部分
  • 語法：
SELECT *
FROM table1 
FULL JOIN table2 
  ON table1.key = table2.key
  • 示例：
-- 員工表(emp)和部門表(dept)的全連接
SELECT emp.name, dept.dept_name
FROM emp 
FULL JOIN dept 
  ON emp.dept_id = dept.id
  • 結果特徵：
  • 包含所有員工（即使無部門）
  • 包含所有部門（即使無員工）
  • 缺失值顯示為NULL
2. 半連接（SEMI JOIN）
  • 作用：僅返回左表中存在於右表的記錄（不返回右表字段）
  • 語法：
SELECT *
FROM table1 
LEFT SEMI JOIN table2 
  ON table1.key = table2.key
  • 示例：
-- 獲取有訂單的客户
SELECT cust.*
FROM customers cust 
LEFT SEMI JOIN orders 
  ON cust.id = orders.cust_id
  • 結果特徵：
  • 只返回左表字段
  • 相當於WHERE EXISTS子查詢
  • 右表字段不可訪問
3. 交叉連接（CROSS JOIN）
  • 作用：生成兩個表的笛卡爾積（所有行組合）
  • 語法：
SELECT *
FROM table1 
CROSS JOIN table2
  • 示例：
-- 生成所有產品與地區的組合
SELECT products.name, regions.region_name
FROM products 
CROSS JOIN regions
  • 結果特徵：
  • 結果行數 = $m \times n$（$m$為左錶行數，$n$為右錶行數）
  • 無連接條件
  • 大數據集需謹慎使用（易產生海量數據）

核心對比

連接類型

匹配邏輯

結果特徵

典型應用場景

FULL JOIN

保留所有記錄+NULL填充

包含兩表全部數據

數據完整性分析

SEMI JOIN

僅保留左表匹配記錄

只返回左表字段

存在性驗證（如過濾）

CROSS JOIN

所有行組合（笛卡爾積）

$m \times n$ 行結果

組合生成（如測試用例構建）

性能提示：

  • 優先使用SEMI JOIN替代IN/EXISTS子查詢（Hive優化更好）
  • CROSS JOIN需嚴格控制數據量（可添加WHERE 1=0快速終止）
  • 大表連接建議開啓MapJoin優化：SET hive.auto.convert.join=true;