文章目录
前提条件
- 安装好Hive，可参考：openEuler24.03 LTS下安装Hive3
Hive 多表查询案例
JOIN案例
JOIN查询数据准备
-- 创建员工临时表
CREATE TEMPORARY TABLE temp_employees (
emp_id INT,
emp_name STRING,
dept_id INT
);
-- 插入员工数据
INSERT INTO temp_employees VALUES
(1, 'Alice', 10),
(2, 'Bob', 20),
(3, 'Charlie', NULL);
-- 创建部门临时表
CREATE TEMPORARY TABLE temp_departments (
dept_id INT,
dept_name STRING
);
-- 插入部门数据
INSERT INTO temp_departments VALUES
(10, 'HR'),
(20, 'IT'),
(30, 'Finance');
-- 创建项目临时表
CREATE TEMPORARY TABLE temp_projects (
project_id INT,
emp_id INT,
project_name STRING
);
-- 插入项目数据
INSERT INTO temp_projects VALUES
(101, 1, 'Project A'),
(102, 2, 'Project B');
1. 内连接（INNER JOIN）
只返回两个表中匹配的行
-- 1. 内连接（INNER JOIN）
-- 只返回两个表中匹配的行
SELECT e.emp_id, e.emp_name, d.dept_name
FROM temp_employees e
INNER JOIN temp_departments d
ON e.dept_id = d.dept_id;
-- 结果
e.emp_id e.emp_name d.dept_name
1 Alice HR
2 Bob IT
2. 左外连接（LEFT OUTER JOIN）
返回左表的所有行，以及右表中匹配的行，右表无匹配则对应列值为 NULL
-- 2. 左外连接（LEFT OUTER JOIN）
-- 返回左表的所有行，以及右表中匹配的行，右表无匹配则对应列值为 NULL
-- LEFT OUTER JOIN 可以写成 LEFT JOIN
SELECT e.emp_id, e.emp_name, d.dept_name
FROM temp_employees e
LEFT OUTER JOIN temp_departments d
ON e.dept_id = d.dept_id;
-- 结果
e.emp_id e.emp_name d.dept_name
1 Alice HR
2 Bob IT
3 Charlie NULL
-- 另外的写法
SELECT e.emp_id, e.emp_name, d.dept_name
FROM temp_employees e
LEFT JOIN temp_departments d
ON e.dept_id = d.dept_id;
3. 右外连接（RIGHT OUTER JOIN）
返回右表的所有行，以及左表中匹配的行，左表无匹配则对应列值为 NULL
-- 3. 右外连接（RIGHT OUTER JOIN）
-- 返回右表的所有行，以及左表中匹配的行，左表无匹配则对应列值为 NULL
-- RIGHT OUTER JOIN 可以写成 RIGHT JOIN
SELECT e.emp_id, e.emp_name, d.dept_name
FROM temp_employees e
RIGHT OUTER JOIN temp_departments d
ON e.dept_id = d.dept_id;
-- 结果
e.emp_id e.emp_name d.dept_name
1 Alice HR
2 Bob IT
NULL NULL Finance
4. 全外连接（FULL OUTER JOIN）
返回两个表中的所有行，某表无匹配则对应列值为 NULL
-- 4. 全外连接（FULL OUTER JOIN）
-- 返回两个表中的所有行，某表无匹配则对应列值为 NULL
-- FULL OUTER JOIN可以写成FULL JOIN
SELECT e.emp_id, e.emp_name, d.dept_name
FROM temp_employees e
FULL OUTER JOIN temp_departments d
ON e.dept_id = d.dept_id;
-- 结果
e.emp_id e.emp_name d.dept_name
3 Charlie NULL
1 Alice HR
2 Bob IT
NULL NULL Finance
-- 另外的写法
SELECT e.emp_id, e.emp_name, d.dept_name
FROM temp_employees e
FULL JOIN temp_departments d
ON e.dept_id = d.dept_id;
5. 多表连接
连接员工表、部门表和项目表
-- 5. 多表连接
-- 连接员工表、部门表和项目表
SELECT e.emp_id, e.emp_name, d.dept_name, p.project_name
FROM temp_employees e
JOIN temp_departments d ON e.dept_id = d.dept_id
JOIN temp_projects p ON e.emp_id = p.emp_id;
-- 结果
e.emp_id e.emp_name d.dept_name p.project_name
1 Alice HR Project A
2 Bob IT Project B
6. 笛卡尔集（CROSS JOIN）
返回两个表中所有可能的行组合
-- 6. 笛卡尔集（CROSS JOIN）
-- 返回两个表中所有可能的行组合
SELECT e.emp_id, e.emp_name, d.dept_name
FROM temp_employees e
CROSS JOIN temp_departments d;
-- 结果
e.emp_id e.emp_name d.dept_name
1 Alice HR
1 Alice IT
1 Alice Finance
2 Bob HR
2 Bob IT
2 Bob Finance
3 Charlie HR
3 Charlie IT
3 Charlie Finance
-- 另外的写法
SELECT e.emp_id, e.emp_name, d.dept_name
FROM temp_employees e,temp_departments d;
UNION案例
数据准备
-- 创建另一个与 temp_employees 结构相同的临时表 temp_employees2
CREATE TEMPORARY TABLE temp_employees2 (
emp_id INT,
emp_name STRING,
dept_id INT
);
-- 插入数据到 temp_employees2
INSERT INTO temp_employees2 VALUES
(2, 'Bob', 20),
(4, 'David', 10),
(5, 'Eve', 20);
1.UNION
使用 UNION 合并两个表的结果，会去除重复行
-- 使用 UNION 合并两个表的结果，会去除重复行
SELECT emp_id, emp_name, dept_id
FROM temp_employees
UNION
SELECT emp_id, emp_name, dept_id
FROM temp_employees2;
-- 结果
_u1.emp_id _u1.emp_name _u1.dept_id
1 Alice 10
2 Bob 20
3 Charlie NULL
4 David 10
5 Eve 20
2.UNION ALL
使用 UNION ALL 合并两个表的结果，不会去除重复行
-- 使用 UNION ALL 合并两个表的结果，不会去除重复行
SELECT emp_id, emp_name, dept_id
FROM temp_employees
UNION ALL
SELECT emp_id, emp_name, dept_id
FROM temp_employees2;
-- 结果
_u1.emp_id _u1.emp_name _u1.dept_id
1 Alice 10
2 Bob 20
3 Charlie NULL
2 Bob 20
4 David 10
5 Eve 20
完成！enjoy it！