在数据仓库和数据分析中，JOIN操作是将多个表的数据关联起来的最核心、最常用的操作之一。Hive作为Hadoop上的数据仓库工具，完美支持SQL的JOIN语法。理解不同类型及其区别，是编写高效、准确Hive查询的基石。
Part01：准备工作
员工表（employees）
-- 建表语句
CREATE TABLE employees (
emp_id INT,
emp_name STRING,
dept_id INT
);
部门表（departments）
-- 建表语句
CREATE TABLE departments (
dept_id INT,
dept_name STRING
);
注意
-
employees表中的dept_id = 50在departments表中不存在。 -
departments表中的dept_id = 30,40在employees表中没有对应的员工。
Part02：JOIN详解
INNER JOIN（内连接）
**※ 定义：**返回两个表中连接键完全匹配的行。即只返回两个表都存在的数据。
**※ 维恩图表示：**两个集合的交集
※ 语法
SELECT ...
FROM table_a a
[INNER] JOIN table_b b
ON a.key = b.key;
-- `INNER` 关键字可以省略
**※ 示例：**查询所有员工及其所属的部门名称
SELECT e.emp_id, e.emp_name, e.dept_id, d.dept_name
FROM employees e
INNER JOIN departments d
ON e.dept_id = d.dept_id;
或者（省略了INNER）
SELECT e.emp_id, e.emp_name, e.dept_id, d.dept_name
FROM employees e
JOIN departments d
ON e.dept_id = d.dept_id;
※ 查询结果
※ 结果分析
-
只返回了
dept_id为10和20的记录，因为只有这两个ID在两个表中同时存在。 -
dept_id = 50的员工和dept_id = 30,40的部门都没有出现。
LEFT JOIN（左外连接）
※ 定义：返回左表的全部记录，以及右表中连接键匹配的记录。如果右表中没有匹配项，则结果集中右表的部分以NULL填充。
※ 维恩图表示：左表的全部。
※ 语法
SELECT ...
FROM table_a a
LEFT [OUTER] JOIN table_b b
ON a.key = b.key;
-- `OUTER` 关键字可以省略
※ **示例：**列出所有员工，并显示其部门名称（即使该员工没有分配部门）。
SELECT e.emp_id, e.emp_name, e.dept_id, d.dept_name
FROM employees e
LEFT JOIN departments d
ON e.dept_id = d.dept_id;
※ 查询结果
※ 结果分析
左表employees的所有记录都被返回。赵六的dept_id=50在右表departments中找不到匹配，所以其dept_name为NULL。
RIGHT JOIN（右外连接）
※ **定义：**与LEFT JOIN相反。返回右表的全部记录，以及左表中连接键匹配的记录。如果左表中没有匹配项，则结果集中左表的部分以NULL填充。
※ **维恩图表示：**右表的全部
※ 语法
SELECT ...
FROM table_a a
RIGHT [OUTER] JOIN table_b b
ON a.key = b.key;
-- `OUTER` 关键字可以省略
※ **示例：**列出所有部门，并显示在该部门工作的员工（即使该部门没有员工）。
SELECT e.emp_id, e.emp_name, e.dept_id, d.dept_name
FROM employees e
RIGHT JOIN departments d
ON e.dept_id = d.dept_id;
※ 查询结果
※ 结果分析
右表departments的所有记录都被返回。技术部和人事部在左表employees中没有匹配的员工，所以对应的员工信息为NULL。
FULL OUTER JOIN（全外连接）
※ **定义：**返回左表和右表中的所有记录。无论是否匹配，对方表中没有匹配的部分都用NULL填充。它是LEFT JOIN和RIGHT JOIN结果的并集。
※ 维恩图表示：两个表的全部。
※ 语法
SELECT ...
FROM table_a a
FULL [OUTER] JOIN table_b b
ON a.key = b.key;
-- `OUTER` 关键字可以省略
※ **示例：**列出所有员工和所有部门的组合
SELECT e.emp_id, e.emp_name, e.dept_id, d.dept_name
FROM employees e
FULL OUTER JOIN departments d
ON e.dept_id = d.dept_id;
※ 查询结果
※ 结果分析
-
匹配的记录（财务部、市场部）正常显示。
-
左表独有的记录（赵六）其右表部分为
NULL。 -
右表独有的记录（技术部、人事部）其左表部分为
NULL。
LEFT SEMI JOIN（左半连接）
※ 定义：这是Hive特有的一种JOIN。它只返回左表的列，并且只返回那些在右表中没有存在匹配键的左表记录。它类似于INNER JOIN，但效率更高，因为它不需要去右表拉数据。
※ 注意：SELECT和WHERE子句中都不能引用右表的字段。
※ 语法
SELECT ...
FROM table_a a
LEFT SEMI JOIN table_b b
ON (a.key = b.key);
**※ 示例：**查询有部门信息的员工（只返回员工表信息）
SELECT *
FROM employees e
LEFT SEMI JOIN departments d
ON (e.dept_id = d.dept_id);
这个查询等价于使用IN的子查询
SELECT *
FROM employees e
WHERE e.dept_id IN (SELECT dept_id FROM departments);
※ 查询结果
※ 结果分析
只返回了左表employees中dept_id在右表departments中存在的记录，并且只包含了左表的列。
CROSS JOIN（笛卡尔积）
※ 定义：返回两个表的笛卡尔积，即左表的每一行与右表的每一行进行组合。如果左表有M行，右表有N行，结果将是M x N行。
※ 语法
SELECT ...
FROM table_a a
CROSS JOIN table_b b;
或者在Hive中，如果连接条件省略，也会产生笛卡尔积（但强烈建议使用CROSS JOIN关键字以明确意图）。
※ 示例
SELECT e.emp_name, d.dept_name
FROM employees e
CROSS JOIN departments d;
查询结果行数：4（employees）* 4（departments）= 16行。
结果将是所有员工名和所有部门名的任意组合。
Part03：总结与对比
Part04：使用建议
- 明确业务需求：首先确定你需要的是所有数据还是一部分数据，这决定了使用哪种JOIN
- 性能考虑：
- 优先使用INNER JOIN或LEFT SEMI JOIN，它们通常更高效。
- 避免使用CROSS JOIN，除非业务确实需要。
- 在多表JOIN时，注意大表和小表的顺序，可以利用Hive的MapJoin优化。
- 空值处理：在使用外连接时，结果中可能会出现大量的NULL，在后续的WHERE或聚合操作中需要小心处理。
