作者：IvanCodes
日期：2025年5月16日
專欄：Hive教程
在數據分析的江湖中，數據往往分散在不同的“門派”（表）之中。要洞察數據間的深層聯系，就需要JOIN這把利器，將相關聯的數據串聯起來。Hive SQL 提供了多種 JOIN語法，如同六脈神劍，各有精妙之處。掌握它們，能讓你在數據整合時游刃有余。
思維導圖
準備工作：創建示例表
為了演示各種 JOIN，我們先創建兩張簡單的表：employees (員工表) 和 departments (部門表)。
-- 員工表
CREATE TABLE employees (
emp_id INT,
emp_name STRING,
dept_id INT
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS TEXTFILE;-- 部門表
CREATE TABLE departments (
dept_id INT,
dept_name STRING
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS TEXTFILE;-- 插入數據
INSERT INTO employees VALUES
(1, '張三', 101),
(2, '李四', 102),
(3, '王五', 101),
(4, '趙六', 103),
(5, '孫七', NULL);INSERT INTO departments VALUES
(101, '技術部'),
(102, '市場部'),
(104, '行政部');
Hive JOIN 六大語法詳解
1. INNER JOIN (內連接，或簡寫為 JOIN)
- 核心思想：只返回兩張表中連接條件匹配的行。如果某行在一張表中找不到在另一張表中與之匹配的行，則該行不會出現在結果中。
- 通用語法：
SELECT table1.col1, table1.col2, table2.col_other
FROM table1
INNER JOIN table2
ON table1.join_column = table2.join_column;
- 代碼示例：查詢所有有明確部門歸屬的員工及其部門名稱。
SELECT e.emp_name, d.dept_name
FROM employees e
INNER JOIN departments d
ON e.dept_id = d.dept_id;
- 預期輸出：
張三 技術部
李四 市場部
王五 技術部
2. LEFT OUTER JOIN (左外連接，或簡寫為 LEFT JOIN)
- 核心思想：返回左表中所有的行，以及右表中與左表連接條件匹配的行。如果右表中沒有匹配的行，則右表的列值顯示為
NULL。 - 通用語法：
SELECT table1.col1, table1.col2, table2.col_other
FROM table1
LEFT OUTER JOIN table2
ON table1.join_column = table2.join_column;
- 代碼示例：查詢所有員工，并顯示他們的部門名稱（如果存在）。
SELECT e.emp_name, e.dept_id AS emp_dept_id, d.dept_name
FROM employees e
LEFT JOIN departments d
ON e.dept_id = d.dept_id;
- 預期輸出：
張三 101 技術部
李四 102 市場部
王五 101 技術部
趙六 103 NULL
孫七 NULL NULL
3. RIGHT OUTER JOIN (右外連接，或簡寫為 RIGHT JOIN)
- 核心思想：與 LEFT JOIN 相反。返回右表中所有的行，以及左表中與右表連接條件匹配的行。如果左表中沒有匹配的行，則左表的列值顯示為
NULL。 - 通用語法：
SELECT table1.col1, table2.col_other1, table2.col_other2
FROM table1
RIGHT OUTER JOIN table2
ON table1.join_column = table2.join_column;
- 代碼示例：查詢所有部門，并顯示部門下的員工姓名（如果存在）。
SELECT e.emp_name, d.dept_name, d.dept_id AS dep_dept_id
FROM employees e
RIGHT JOIN departments d
ON e.dept_id = d.dept_id;
- 預期輸出：
張三 技術部 101
李四 市場部 102
王五 技術部 101
NULL 行政部 104
4. FULL OUTER JOIN (全外連接，或簡寫為 FULL JOIN)
- 核心思想：返回左表和右表中所有的行。當某行在另一張表中沒有匹配時，該表對應的列值顯示為
NULL。 - 通用語法：
SELECT table1.col1, table2.col_other
FROM table1
FULL OUTER JOIN table2
ON table1.join_column = table2.join_column;
- 代碼示例：查詢所有員工和所有部門的完整信息。
SELECT e.emp_name, e.dept_id AS emp_dept_id, d.dept_name, d.dept_id AS dep_dept_id
FROM employees e
FULL JOIN departments d
ON e.dept_id = d.dept_id;
- 預期輸出：
張三 101 技術部 101
李四 102 市場部 102
王五 101 技術部 101
趙六 103 NULL NULL
孫七 NULL NULL NULL
NULL NULL 行政部 104
5. LEFT SEMI JOIN (左半連接)
- 核心思想：這是 Hive 特有的一種 JOIN。它只返回左表中那些在右表中存在匹配記錄的行。關鍵在于，結果集中不包含右表的任何列。它更像是一個存在性檢查 (類似于 SQL 中的
EXISTS子查詢)。 - 通用語法：
SELECT table1.col1, table1.col2
FROM table1
LEFT SEMI JOIN table2
ON table1.join_column = table2.join_column;
- 代碼示例：查詢所有在部門表中確實存在對應部門的員工信息。
SELECT e.emp_id, e.emp_name, e.dept_id
FROM employees e
LEFT SEMI JOIN departments d
ON e.dept_id = d.dept_id;
- 預期輸出：
1 張三 101
2 李四 102
3 王五 101
6. CROSS JOIN (交叉連接，笛卡爾積)
- 核心思想：返回左表中的每一行與右表中的每一行的所有可能組合。結果集的行數是左表行數乘以右表行數。通常不使用 ON 子句（或者使用
ON 1=1這種恒為真的條件）。 - 通用語法：
SELECT table1.col1, table2.col_other
FROM table1
CROSS JOIN table2;
- 代碼示例：顯示員工和部門的所有可能組合（通常在實際業務中要謹慎使用）。
SELECT e.emp_name, d.dept_name
FROM employees e
CROSS JOIN departments d;
- 預期輸出： (員工表5行 * 部門表3行 = 15行，部分示例)
張三 技術部
張三 市場部
張三 行政部
李四 技術部
李四 市場部
李四 行政部
...
- 注意：CROSS JOIN 非常容易產生巨大的結果集，消耗大量資源，務必謹慎使用。
練習題
假設我們有如上創建的 employees 和 departments 表。
- 找出所有在“技術部”工作的員工姓名。
- 列出所有部門的名稱，以及該部門的員工數量（如果某部門沒有員工，數量顯示為0）。
- 找出所有沒有分配到任何有效部門的員工姓名（即員工表中的dept_id在部門表中不存在，或者員工的dept_id為NULL）。
- 列出所有員工的姓名，以及他們所在部門的名稱。對于沒有部門的員工孫七，部門名稱應顯示為 “未分配”；對于部門ID存在但部門表中無對應名稱的趙六，部門名稱應顯示為 “未知部門”。
- 使用 LEFT SEMI JOIN，找出所有部門ID為101的員工信息。
- 解釋 INNER JOIN 和 LEFT OUTER JOIN 在處理不匹配數據時的主要區別。
- 如果
employees表有100行，departments表有5行，那么CROSS JOIN會產生多少行結果？ - 找出所有既有員工，其部門也在部門表中存在的員工姓名和部門名稱。（提示：思考多種JOIN方式）
- 使用 FULL OUTER JOIN，然后篩選出只存在于員工表（在部門表無匹配）或只存在于部門表（在員工表無匹配）的記錄。請描述如何篩選。
- 查詢所有部門ID (dept_id)，以及這些部門的名稱。如果一個部門ID只存在于員工表中，也需要列出這個ID，但部門名稱顯示為NULL。
練習題答案
- 找出所有在“技術部”工作的員工姓名。
SELECT e.emp_name
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
WHERE d.dept_name = '技術部';
- 列出所有部門的名稱，以及該部門的員工數量（如果某部門沒有員工，數量顯示為0）。
SELECT d.dept_name, COUNT(e.emp_id) AS employee_count
FROM departments d
LEFT JOIN employees e ON d.dept_id = e.dept_id
GROUP BY d.dept_id, d.dept_name;
- 找出所有沒有分配到任何有效部門的員工姓名（即員工表中的dept_id在部門表中不存在，或者員工的dept_id為NULL）。
SELECT e.emp_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id
WHERE d.dept_id IS NULL;
- 列出所有員工的姓名，以及他們所在部門的名稱。對于沒有部門的員工孫七，部門名稱應顯示為 “未分配”；對于部門ID存在但部門表中無對應名稱的趙六，部門名稱應顯示為 “未知部門”。
SELECT
e.emp_name,
CASE
WHEN e.dept_id IS NULL THEN '未分配'
WHEN d.dept_name IS NULL THEN '未知部門'
ELSE d.dept_name
END AS department_status
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id;
- 使用 LEFT SEMI JOIN，找出所有部門ID為101的員工信息。
SELECT e.emp_id, e.emp_name, e.dept_id
FROM employees e
LEFT SEMI JOIN departments d ON e.dept_id = d.dept_id AND e.dept_id = 101;
-
解釋 INNER JOIN 和 LEFT OUTER JOIN 在處理不匹配數據時的主要區別。
INNER JOIN 只保留兩邊表中都能通過連接條件找到匹配的行。如果左表的一行在右表中沒有匹配，或者右表的一行在左表中沒有匹配，這些行都會被丟棄。
LEFT OUTER JOIN 會保留左表的所有行。如果左表的某行在右表中找到了匹配，則合并兩邊的列；如果在右表中找不到匹配，則右表對應的列將填充為NULL，但左表的行仍然會出現在結果中。 -
如果
employees表有100行，departments表有5行，那么CROSS JOIN會產生多少行結果？
100 * 5 = 500 行。 -
找出所有既有員工，其部門也在部門表中存在的員工姓名和部門名稱。（提示：思考多種JOIN方式）
SELECT e.emp_name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;
- 使用 FULL OUTER JOIN，然后篩選出只存在于員工表（在部門表無匹配）或只存在于部門表（在員工表無匹配）的記錄。請描述如何篩選。
篩選條件是：當employees.emp_id IS NULL(表示這條記錄只在departments表中有) 或者departments.dept_id IS NULL(表示這條記錄只在employees表中有，且連接失敗)。
SELECT e.emp_name, e.dept_id AS emp_dept_id, d.dept_name, d.dept_id AS dep_dept_id
FROM employees e
FULL OUTER JOIN departments d ON e.dept_id = d.dept_id
WHERE e.emp_id IS NULL OR d.dept_id IS NULL;
- 查詢所有部門ID (dept_id)，以及這些部門的名稱。如果一個部門ID只存在于員工表中，也需要列出這個ID，但部門名稱顯示為NULL。
SELECT DISTINCT e.dept_id AS emp_dept_id_distinct, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id;
利用C11模擬偽閉包實現連接的安全回收)
