Oracle教程FG056-SQL集合操作

1. 集合操作基础

SQL集合操作用于组合两个或多个查询的结果集，包括UNION、UNION ALL、INTERSECT和MINUS。这些操作可以帮助我们更灵活地处理数据，实现复杂的查询需求。更多学习教程www.fgedu.net.cn

集合操作类型：UNION（并集，去重）、UNION ALL（并集，不去重）、INTERSECT（交集）、MINUS（差集）。

— 创建示例表
CREATE TABLE employees_1 (
employee_id NUMBER(6),
first_name VARCHAR2(20),
last_name VARCHAR2(25),
department_id NUMBER(4)
);CREATE TABLE employees_2 (
employee_id NUMBER(6),
first_name VARCHAR2(20),
last_name VARCHAR2(25),
department_id NUMBER(4)
);– 插入数据
INSERT INTO employees_1 VALUES (100, ‘Steven’, ‘King’, 90);INSERT INTO employees_1 VALUES (101, ‘Neena’, ‘Kochhar’, 90);INSERT INTO employees_1 VALUES (102, ‘Lex’, ‘De Haan’, 90);INSERT INTO employees_2 VALUES (102, ‘Lex’, ‘De Haan’, 90);INSERT INTO employees_2 VALUES (103, ‘Alexander’, ‘Hunold’, 60);INSERT INTO employees_2 VALUES (104, ‘Bruce’, ‘Ernst’, 60);SELECT * FROM employees_1;EMPLOYEE_ID EMP_NAME EMP_EMP_EMP_LAST_NAME DEPARTMENT_ID
———– ——————– ————————- ————-
100 Steven King 90
101 Neena Kochhar 90
102 Lex De Haan 90

SELECT * FROM employees_2;EMPLOYEE_ID EMP_NAME EMP_EMP_EMP_LAST_NAME DEPARTMENT_ID
———– ——————– ————————- ————-
102 Lex De Haan 90
103 Alexander Hunold 60
104 Bruce Ernst 60

2. UNION操作

UNION操作返回两个查询结果的并集，自动去除重复行。

— 使用UNION操作
SELECT employee_id, first_name, last_name, department_id
FROM employees_1
UNION
SELECT employee_id, first_name, last_name, department_id
FROM employees_2;EMPLOYEE_ID EMP_NAME EMP_EMP_EMP_LAST_NAME DEPARTMENT_ID
———– ——————– ————————- ————-
100 Steven King 90
101 Neena Kochhar 90
102 Lex De Haan 90
103 Alexander Hunold 60
104 Bruce Ernst 60

3. UNION ALL操作

UNION ALL操作返回两个查询结果的并集，保留重复行。

— 使用UNION ALL操作
SELECT employee_id, first_name, last_name, department_id
FROM employees_1
UNION ALL
SELECT employee_id, first_name, last_name, department_id
FROM employees_2;EMPLOYEE_ID EMP_NAME EMP_EMP_EMP_LAST_NAME DEPARTMENT_ID
———– ——————– ————————- ————-
100 Steven King 90
101 Neena Kochhar 90
102 Lex De Haan 90
102 Lex De Haan 90
103 Alexander Hunold 60
104 Bruce Ernst 60

4. INTERSECT操作

INTERSECT操作返回两个查询结果的交集，即同时存在于两个结果集中的行。

— 使用INTERSECT操作
SELECT employee_id, first_name, last_name, department_id
FROM employees_1
INTERSECT
SELECT employee_id, first_name, last_name, department_id
FROM employees_2;EMPLOYEE_ID EMP_NAME EMP_EMP_EMP_LAST_NAME DEPARTMENT_ID
———– ——————– ————————- ————-
102 Lex De Haan 90

5. MINUS操作

MINUS操作返回第一个查询结果中存在但第二个查询结果中不存在的行。

— 使用MINUS操作
SELECT employee_id, first_name, last_name, department_id
FROM employees_1
MINUS
SELECT employee_id, first_name, last_name, department_id
FROM employees_2;EMPLOYEE_ID EMP_NAME EMP_EMP_EMP_LAST_NAME DEPARTMENT_ID
———– ——————– ————————- ————-
100 Steven King 90
101 Neena Kochhar 90

— 反向MINUS操作
SELECT employee_id, first_name, last_name, department_id
FROM employees_2
MINUS
SELECT employee_id, first_name, last_name, department_id
FROM employees_1;EMPLOYEE_ID EMP_NAME EMP_EMP_EMP_LAST_NAME DEPARTMENT_ID
———– ——————– ————————- ————-
103 Alexander Hunold 60
104 Bruce Ernst 60

6. 集合操作规则

使用集合操作时需要遵循以下规则：

— 规则1：所有查询必须返回相同数量的列
— 错误示例：列数不同
SELECT employee_id, first_name FROM employees_1
UNION
SELECT employee_id FROM employees_2;– 会报错：ORA-01789: 查询块具有不同数量的结果列

— 规则2：对应列的数据类型必须兼容
— 正确示例：数据类型兼容
SELECT employee_id, first_name, last_name
FROM employees_1
UNION
SELECT employee_id, first_name, last_name
FROM employees_2;– 规则3：可以使用ORDER BY对最终结果排序
SELECT employee_id, first_name, last_name, department_id
FROM employees_1
UNION
SELECT employee_id, first_name, last_name, department_id
FROM employees_2
ORDER BY department_id, employee_id;EMPLOYEE_ID EMP_NAME EMP_EMP_EMP_LAST_NAME DEPARTMENT_ID
———– ——————– ————————- ————-
103 Alexander Hunold 60
104 Bruce Ernst 60
100 Steven King 90
101 Neena Kochhar 90
102 Lex De Haan 90

7. 集合操作性能

集合操作的性能取决于多个因素，包括数据量、索引使用、排序操作等。

— 性能对比：UNION vs UNION ALL
— UNION需要去重，会执行排序操作
SELECT employee_id, first_name, last_name
FROM employees_1
UNION
SELECT employee_id, first_name, last_name
FROM employees_2;– UNION ALL不需要去重，性能更好
SELECT employee_id, first_name, last_name
FROM employees_1
UNION ALL
SELECT employee_id, first_name, last_name
FROM employees_2;– 对于大型表，考虑使用UNION ALL并在必要时手动去重
SELECT DISTINCT employee_id, first_name, last_name
FROM (
SELECT employee_id, first_name, last_name
FROM employees_1
UNION ALL
SELECT employee_id, first_name, last_name
FROM employees_2
);

8. 最佳实践

风哥提示：SQL集合操作的最佳实践：
1. 合理选择集合操作类型，根据是否需要去重选择UNION或UNION ALL
2. 对于大型表，优先使用UNION ALL以提高性能
3. 确保所有查询返回相同数量和类型的列
4. 为集合操作中的列创建适当的索引，提高查询性能
5. 对于复杂的集合操作，考虑使用临时表或物化视图
6. 合理使用ORDER BY子句，对最终结果进行排序
7. 避免在集合操作中使用复杂的表达式，影响性能
8. 定期分析集合操作的性能，优化执行计划
9. 对于需要去重的场景，考虑使用DISTINCT结合UNION ALL
10. 遵循SQL编码规范，保持集合操作的可读性

生产环境建议：在生产环境中，应根据具体需求选择合适的集合操作类型。对于大型表，建议使用UNION ALL以提高性能，并在必要时手动去重。同时，为集合操作中的列创建适当的索引，优化查询性能。

更多视频教程www.fgedu.net.cn

学习交流加群风哥微信: itpux-com

学习交流加群风哥QQ113257174

更多学习教程公众号风哥教程itpux_com

from oracle:www.itpux.com

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html