Hive 多表查询案例

文章目录

- 前提条件
- Hive 多表查询案例
- - JOIN案例
  - - JOIN查询数据准备
    - 1. 内连接（INNER JOIN）
    - 2. 左外连接（LEFT OUTER JOIN）
    - 3. 右外连接（RIGHT OUTER JOIN）
    - 4. 全外连接（FULL OUTER JOIN）
    - 5. 多表连接
    - 6. 笛卡尔集（CROSS JOIN）
  - UNION案例
  - - 数据准备
    - 1.UNION
    - 2.UNION ALL

前提条件

安装好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