Oracle中的交差并集

目录

一、并集

二、交集

三、差集

---

一、并集

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;

运行结果如下： 

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

select ename,sal from emp where sal<2500;

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

①并集

union all：

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

运行结果如下：

 union：

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

运行结果如下： 

②交集

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

运行结果如下： 

③差集

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

运行结果如下： 

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

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

运行结果如下：

 突然就发现了一个问题，为什么差集操作符（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中独有的元素。

注意：

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