Oracle中的交差并集

待我称王封你为后i 2024-03-22 13:04 42阅读 0赞

**目录**

一、并集

二、交集

三、差集

--------------------

## 一、并集 ##

UNION ALL/UNION是一种用于合并两个或多个SELECT语句结果集的操作符。UNION ALL会将两个SELECT语句的结果集按照列的顺序合并，并保留重复的行，而UNION会将两个SELECT语句的结果集按照列的顺序合并，并去除重复的行。

语法如下：

--UNION ALL
    SELECT column1, column2, ... FROM table1
    UNION ALL
    SELECT column1, column2, ... FROM table2;
    --UNION 
    SELECT column1, column2, ... FROM table1
    UNION
    SELECT column1, column2, ... FROM table2;

column1、column2等表示要查询的列名，table1、table2等表示要查询的表名或视图名。

使用UNION ALL/UNION 时，需要注意以下几点：

1. 要合并的SELECT语句中的列数必须相同，且对应的列类型和顺序也必须相同。

2. UNION ALL/UNION会将两个SELECT语句的结果集合并成一个结果集，因此，两个SELECT语句中的列名必须相同，否则会出现错误。

3. UNION ALL会保留重复的行，如果不想保留重复的行，可以使用UNION操作符。

4. UNION ALL可以合并多个SELECT语句的结果集，只需要将每个SELECT语句用UNION ALL/UNION 连接即可。

下面是合并两个表的示例：

--UNION ALL
    SELECT column1, column2, column3 FROM table1
    UNION ALL
    SELECT column1, column2, column3 FROM table2;
    --UNION 
    SELECT column1, column2, column3 FROM table1
    UNION
    SELECT column1, column2, column3 FROM table2;

UNION ALL语句会将table1和table2中的数据按照列的顺序合并成一个结果集，并保留重复的行，UNION 语句是去除重复的行。

比如两个数集：

A=\{1,2,3,4,5\}；B=\{1,3,6,9,4\}；

那么：

A UNION B=\{1,2,3,4,5,6,9\}，去重重复的数据；

A UNION ALL B=\{1,2,3,4,5,1,3,6,9,4\}，包括所有数据，不会去除重复。

> **提示：**
> 
> 在使用多个UNION ALL时最好加括号，因为UNION ALL的优先级比其他操作符（如JOIN、WHERE、GROUP BY等）低。这意味着如果不使用括号，查询语句可能会出现问题，因为Oracle将按照默认的优先级顺序执行操作，而不是按照您想要的顺序执行操作。因此，为了确保查询语句按照您期望的顺序执行，最好需要使用括号来明确指定操作的优先级。

## 二、交集 ##

INTERSECT是一种用于组合两个或多个SELECT语句结果集的操作符。它用于返回两个或多个查询结果集的交集，即返回同时出现在多个结果集中的行。

语法如下：

SELECT column1, column2, ... FROM table1
    INTERSECT
    SELECT column1, column2, ... FROM table2;

其中，SELECT语句可以包含WHERE、GROUP BY、HAVING、ORDER BY等子句，但是两个SELECT语句的列数和数据类型必须相同。

以下是一个INTERSECT的实例，假设有两个表A和B，它们的结构如下：

Table A：
    ID   Name
    1    John
    2    Amy
    3    Tom
    
    Table B：
    ID   Name
    2    Amy
    3    Tom
    4    Jack

现在我们要查询同时存在于表A和表B中的记录，可以使用以下语句：

SELECT ID, Name FROM A
    INTERSECT
    SELECT ID, Name FROM B;

执行结果为：

ID   Name
    2    Amy
    3    Tom

可以看到，INTERSECT操作符返回了同时存在于表A和表B中的记录，即ID为2和3的记录。

还是这两个数集：

A=\{1,2,3,4,5\}；B=\{1,3,6,9,4\}；那么A INTERSECT B=\{1,3,4\}。

> **注意：**
> 
> INTERSECT操作符只返回重复的行，如果两个表的结果集中没有重复的行，则返回空结果集。此外，INTERSECT操作符的优先级较低，如果与其他操作符一起使用，应该使用括号明确优先级。

## 三、差集 ##

MINUS是一个关键字，用于在两个SELECT语句之间执行集合差运算（即从第一个结果集中减去第二个结果集中的所有行）。

语法如下：

SELECT column1, column2, ...
    FROM table1
    MINUS
    SELECT column1, column2, ...
    FROM table2;

其中，SELECT语句中的列名和表名可以根据实际需要进行替换，MINUS关键字必须大写。

使用MINUS时，需要注意以下几点：

1. 两个SELECT语句中的列数和数据类型必须相同。

2. MINUS操作符只能用于查询结果集，不能用于表之间的操作。

3. 如果第一个SELECT语句中的结果集包含NULL值，则这些行不会被减去。

4. 如果第二个SELECT语句中的结果集包含重复行，则这些行只会被减去一次。

5. 如果第一个SELECT语句中的结果集为空，则MINUS操作返回空结果集。

6. MINUS操作符也可以用EXCEPT代替，两者的作用相同。

以下是一个使用MINUS的实例：

假设我们有两个表，一个是学生表（student），另一个是成绩表（score）。学生表包含学生ID和姓名，成绩表包含学生ID和分数。我们想要找出所有没有成绩的学生。

可以使用以下SQL语句来实现：

SELECT student_id, student_name
    FROM student
    MINUS
    SELECT student_id, score
    FROM score;

该语句将返回学生表中所有没有匹配到成绩表的学生ID和姓名。

需要注意：如果成绩表中存在重复的学生ID，则这些学生只会被减去一次。如果学生表中存在重复的学生ID，则这些学生也只会出现一次在结果集中。如果成绩表中存在NULL值，则这些行不会被减去。

已知两个集合C和D，如下：

C：员工表工资高于2000的员工姓名和工资;

select ename,sal from emp where sal>2000;

运行结果如下：

![d11ad07d1d78476c9c5d249200b0a2fb.png][]

D: 员工表工资低于2500的员工姓名和工资

select ename,sal from emp where sal<2500;

![a2d8c8d034cb4f9f8aa7b931323862f0.png][]

分别求出集合C和D的并集、交集、差集；

①并集

union all：

select ename,sal from emp where sal>2000
    UNION all--不去重
    select ename,sal from emp where sal<2500;

运行结果如下：

![d231a5ebbbac487c8ac1a460cd680f15.png][]

union：

select ename,sal from emp where sal>2000
    UNION --去重
    select ename,sal from emp where sal<2500;

运行结果如下：

![5233c937f8024d429ebcd5faeaf4846a.png][]

②交集

select ename,sal from emp where sal>2000
    INTERSECT
    select ename,sal from emp where sal<2500;

运行结果如下：

![1fada7f5a4464d738fa2fe38744aa37b.png][]

③差集

select ename,sal from emp where sal>2000
    MINUS
    select ename,sal from emp where sal<2500;

运行结果如下：

![a48e8195e60d43eea4f1924439e4ab42.png][]

对集合C和D互换位置再看下结果

select ename,sal from emp where sal<2500
    MINUS
    select ename,sal from emp where sal>2000;

运行结果如下：

![4f70443bcca24df4a534520f936374f5.png][]

***突然就发现了一个问题，为什么差集操作符（MINUS）的两个集合互换位置后结果不一样？***

解析：

在Oracle中，差集操作是非对称的。也就是说，A MINUS B 的结果并不等于 B MINUS A 的结果。

这是因为差集操作是基于集合的，而集合是没有顺序的。因此，当我们对两个集合执行差集操作时，结果取决于它们的元素。如果元素在两个集合中的位置不同，则两个集合互换位置后结果不一样。

举个例子：

假设集合A包含\{1, 2, 3\}，集合B包含\{2, 3, 4\}，则A MINUS B的结果为\{1\}，B MINUS A的结果为\{4\}。这是因为A MINUS B表示从集合A中减去集合B中的元素，所以1是A中独有的元素，而4是B中独有的元素；而B MINUS A表示从集合B中减去集合A中的元素，所以4是B中独有的元素，而1是A中独有的元素。

> **注意：**
> 
> 在使用差集操作时，需要仔细考虑两个集合的元素顺序，以确保得到正确的结果。

[d11ad07d1d78476c9c5d249200b0a2fb.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/148c3fa660794f0195a857baecd399fe.png
[a2d8c8d034cb4f9f8aa7b931323862f0.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/8def2b3fec664b8bbf27a6996a76c192.png
[d231a5ebbbac487c8ac1a460cd680f15.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/bdc2d7aa0dc04b6a9e25d4fa78d8769b.png
[5233c937f8024d429ebcd5faeaf4846a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/a04f4147283d42c1b0a21bed70752637.png
[1fada7f5a4464d738fa2fe38744aa37b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/a6ea0310a649426880e560575619b38c.png
[a48e8195e60d43eea4f1924439e4ab42.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/57c5611bd82d4bec84783296f6cf9c07.png
[4f70443bcca24df4a534520f936374f5.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/b0b9e84ec5b24b479bf1f14e66307b9f.png