Oracle 数据库中，UNION ALL创建视图的使用详解

目录

UNION ALL 的特点

UNION ALL 的作用 

1. 合并结果集

2. 保留重复行

3. 提高性能

UNION ALL 的使用场景

1. 日志或数据拼接

2. 区分数据来源

3. 解决分区表查询

注意事项

 在创建视图中的作用

场景 1：合并多个表的数据到视图

表结构

目标

SQL 实现

解析

场景 2：合并不同类型的数据

表结构

目标

解析

场景 3：合并分区数据

表结构

目标

SQL 实现

场景 4：跨业务系统的数据合并

表结构

目标

SQL 实现

解析

场景 5：多条件动态数据组合

表结构

目标

SQL 实现

解析

总结

在 Oracle 数据库中，UNION ALL 是一种用于将多个查询的结果合并为一个结果集的 SQL 运算符。它的功能是将两个或多个查询的结果 直接合并，包括重复的行。以下是对 UNION ALL 的详细解释

SELECT 列1, 列2, ...
FROM 表1
[WHERE 条件]
UNION ALL
SELECT 列1, 列2, ...
FROM 表2
[WHERE 条件]

UNION ALL 的特点

• 不去重

  • 与 UNION 不同，UNION ALL 不会去除结果集中的重复行，因此效率更高。
  • 如果需要去重，请使用 UNION。

• 支持列数和数据类型匹配

  • 合并的各个查询必须有 相同数量的列，且每一列的数据类型必须兼容。
  • 列的名称可以不同，但顺序和类型必须一致。

• 执行顺序

  • UNION ALL 以查询的书写顺序逐行合并结果，查询的顺序会直接影响最终的结果。

UNION ALL 的作用 

1. 合并结果集

UNION ALL 适合在明确不需要去重的情况下合并多个查询结果，例如：

SELECT 'Apple' AS Fruit FROM DUAL
UNION ALL
SELECT 'Banana' AS Fruit FROM DUAL;

结果：

Fruit
-----
Apple
Banana

2. 保留重复行

如果数据中存在重复行，并且需要完整保留（包括重复行），可以使用 UNION ALL：

SELECT 'Apple' AS Fruit FROM DUAL
UNION ALL
SELECT 'Apple' AS Fruit FROM DUAL;

结果：

Fruit
-----
Apple
Apple

与 UNION 对比：

SELECT 'Apple' AS Fruit FROM DUAL
UNION
SELECT 'Apple' AS Fruit FROM DUAL;

结果:

Fruit
-----
Apple

3. 提高性能

由于 UNION ALL 不需要去重，因此在处理大量数据时比 UNION 性能更高。

SELECT employee_id, salary FROM employees
WHERE department_id = 10
UNION ALL
SELECT employee_id, salary FROM employees
WHERE department_id = 20;

UNION ALL 的使用场景

1. 日志或数据拼接

将多张日志表的记录合并为一个结果集：

SELECT log_id, log_date, log_message FROM log_table_2023
UNION ALL
SELECT log_id, log_date, log_message FROM log_table_2024;

2. 区分数据来源

通过额外的列标识数据来源：

SELECT '2023年数据' AS 来源, log_id, log_message
FROM log_table_2023
UNION ALL
SELECT '2024年数据' AS 来源, log_id, log_message
FROM log_table_2024;

3. 解决分区表查询

分区表的分区查询结果可以用 UNION ALL 合并：

SELECT * FROM sales PARTITION (Q1)
UNION ALL
SELECT * FROM sales PARTITION (Q2);

注意事项

1. 结果集大小
   如果使用 UNION ALL 合并了大量重复行，可能会导致结果集变得非常庞大，占用更多的内存和存储。

2. 列的兼容性
   合并的列需要有相同或兼容的数据类型，例如数值和字符类型不能直接混合。

3. 排序需求
   如果需要对最终结果排序，应在最后的结果集使用 ORDER BY：

SELECT column1 FROM table1
UNION ALL
SELECT column1 FROM table2
ORDER BY column1;

 性能对比：UNION vs UNION ALL

 在创建视图中的作用

在 Oracle SQL 中，UNION ALL 可以在创建复杂视图时起到整合多个数据源的关键作用。通过合并多个查询结果并保留重复数据，UNION ALL 非常适用于需要从多张表、多个分区或不同来源数据中创建整合视图的场景。

以下是一些详细的举例和解析：

场景 1：合并多个表的数据到视图

假设有多个分区表存储了不同年份的销售数据，现在需要创建一个视图，将所有年份的销售数据整合为一个统一的视图。

表结构

每年的销售数据分别存储在以下分区表中：

• sales_2022 (字段: sale_id, product_id, amount, sale_date)
• sales_2023 (字段: sale_id, product_id, amount, sale_date)

目标

创建一个视图，能够统一访问所有年的销售数据。

SQL 实现

CREATE OR REPLACE VIEW all_sales AS
SELECT sale_id, product_id, amount, sale_date, '2022年' AS year_label
FROM sales_2022
UNION ALL
SELECT sale_id, product_id, amount, sale_date, '2023年' AS year_label
FROM sales_2023;

查询视图数据

SELECT * FROM all_sales WHERE product_id = 101;

解析

1. 添加来源标识：
   • 使用 '2022年' AS year_label 和 '2023年' AS year_label 区分数据来源。
2. 不去重：
   • 因为不同年份的数据不重叠，无需去重，UNION ALL 比 UNION 更高效。
3. 动态扩展：
   • 可以通过追加新的 SELECT 块来合并后续年份的数据，而不影响现有视图。

场景 2：合并不同类型的数据

假设公司有两种类型的员工记录：

1. 正式员工存储在表 full_time_employees 中。
2. 合同工存储在表 contract_employees 中。

表结构

• full_time_employees (字段: employee_id, name, salary, hire_date, job_type 固定为 '正式员工')
• contract_employees (字段: employee_id, name, salary, hire_date, job_type 固定为 '合同工')

目标

创建一个统一的视图，显示所有员工的数据，包括其身份类型。

SQL 实现

CREATE OR REPLACE VIEW all_employees AS
SELECT employee_id, name, salary, hire_date, '正式员工' AS job_type
FROM full_time_employees
UNION ALL
SELECT employee_id, name, salary, hire_date, '合同工' AS job_type
FROM contract_employees;

查询视图数据

SELECT * FROM all_employees WHERE salary > 5000 ORDER BY hire_date;

解析

1. 不同来源的数据整合：
   • 数据源表结构类似，UNION ALL 能轻松整合不同表的数据。
2. 字段类型兼容：
   • 列数和数据类型需一致，例如两个表中的 salary 均为数值类型。
3. 性能优越：
   • 使用 UNION ALL 避免去重操作，显著提升查询速度。

场景 3：合并分区数据

假设有一个大型分区表 sales 按季度存储数据（PARTITION BY），需要创建视图合并其中的 Q1 和 Q2 数据。

表结构

• 分区表 sales (字段: sale_id, region, amount, sale_date)
  • 分区：Q1、Q2、Q3、Q4

目标

创建视图只合并第一季度和第二季度的数据。

SQL 实现

CREATE OR REPLACE VIEW sales_h1 AS
SELECT * FROM sales PARTITION (Q1)
UNION ALL
SELECT * FROM sales PARTITION (Q2);

 查询视图数据

SELECT region, SUM(amount) AS total_sales
FROM sales_h1
GROUP BY region;

 解析

1. 分区查询的整合：
   • 使用 UNION ALL 按分区提取数据，避免全表扫描。
2. 优化性能：
   • 分区表与 UNION ALL 配合能够高效处理特定数据的整合。

场景 4：跨业务系统的数据合并

假设一家企业的 HR 和财务系统分别存储员工的基本信息和薪资信息，现在需要创建一个视图整合这两部分数据。

表结构

• HR 系统：hr_employees (字段: employee_id, name, department, position)
• 财务系统：finance_employees (字段: employee_id, name, salary, pay_date)

目标

创建视图合并员工的所有信息，保留重复行以便后续分析。

SQL 实现

CREATE OR REPLACE VIEW unified_employees AS
SELECT employee_id, name, department, position, NULL AS salary, NULL AS pay_date
FROM hr_employees
UNION ALL
SELECT employee_id, name, NULL AS department, NULL AS position, salary, pay_date
FROM finance_employees;

查询视图数据 

SELECT employee_id, name, MAX(salary) AS max_salary
FROM unified_employees
WHERE name IS NOT NULL
GROUP BY employee_id, name;

解析

1. 字段对齐：
   • 两个表字段不完全一致，使用 NULL 补齐不存在的列。
2. 数据整合：
   • 利用 UNION ALL 合并不同系统中的数据，方便后续在统一视图中分析。

场景 5：多条件动态数据组合

假设需要将客户的订单数据按照地区和时间分别进行合并，提供给不同的业务部门使用。

表结构

• orders (字段: order_id, customer_id, region, order_date, amount)

目标

创建视图，分别合并北方和南方地区的数据，并按年份标识来源。

SQL 实现

CREATE OR REPLACE VIEW regional_orders AS
SELECT order_id, customer_id, region, order_date, amount, '北方' AS region_label
FROM orders
WHERE region IN ('北方')
UNION ALL
SELECT order_id, customer_id, region, order_date, amount, '南方' AS region_label
FROM orders
WHERE region IN ('南方');

查询视图数据

SELECT region_label, SUM(amount) AS total_sales
FROM regional_orders
WHERE order_date >= TO_DATE('2024-01-01', 'YYYY-MM-DD')
GROUP BY region_label;

解析

1. 动态整合：
   • 按条件动态分区和标记数据来源，方便后续业务使用。
2. 聚合分析：
   • 在视图基础上进行统计分析，减少重复查询复杂性。

总结

在复杂视图的创建中，UNION ALL 的作用可以总结为以下几点：

1. 高效整合多数据源：适合合并大规模数据且无需去重的场景。
2. 动态扩展性：轻松添加新的查询来源而不破坏视图。
3. 保留数据完整性：不会丢失重复行，适合需要完整记录的分析场景。
4. 优化性能：避免去重操作，处理速度快，特别是对大表或分区表的整合。

在设计复杂视图时，根据实际业务需求选择是否使用 UNION ALL，结合字段对齐、动态标识等方式，使视图更灵活高效！

UNION ALL 是 Oracle SQL 中非常实用的工具，尤其在以下情况下效果最佳：

• 不需要去重时。
• 数据量较大且对性能要求高时。
• 需要保留重复数据时。

如需去重，请改用 UNION。