Oracle DB 使用集合运算符
• 描述集合运算符
• 使用集合运算符将多个查询组成一个查询
• 控制返回行的顺序
• 集合运算符：类型和准则
• 本课中使用的表
• UNION和UNION ALL运算符
• INTERSECT运算符
• MINUS运算符
• 匹配SELECT语句
• 在集合运算中使用ORDER BY子句
- 集合运算符
集合运算符用于将两个或更多查询的结果合并成一个结果。包含集合运算符的查询被称为复合查询。
所有集合运算符都具有相同的优先级。如果SQL 语句包含多个集合运算符，在没有使用括号明确地指定其它顺序时，Oracle Server 会从左（上）到右（下）对这些运算符进行计算。在将INTERSECT运算符与其它集合运算符配合使用的查询中，应使用括号明确地指定计算的顺序。
- 集合运算符准则
• SELECT列表中的表达式在数量上必须匹配。
• 第二个查询中每一列的数据类型必须与第一个查询中对应列的数据类型相匹配。
• 可以使用括号更改执行顺序。
• ORDER BY子句只能出现在语句的末尾。
• 查询的SELECT列表中的表达式在数量和数据类型上必须匹配。在WHERE子句中使用UNION、UNION ALL、INTERSECT和MINUS运算符的查询，其SELECT列表中
的列必须具有相同的数量和数据类型。对于复合查询中包含的各个查询，其SELECT列表中各个列的数据类型可能不完全相同。第二个查询中的列必须与第一个查询中的
对应列属于相同的数据类型组（如数字或字符）。
• 可在子查询中使用集合运算符。
• 在将INTERSECT运算符与其它集合运算符配合使用的查询中，应使用括号指定计算的顺序。这可确保符合新出台的SQL 标准，从而为INTERSECT运算符赋予比
其它集合运算符更高的优先级。
- Oracle Server 和集合运算符
• 除非使用UNION ALL运算符，否则会自动删除重复行。
• 第一个查询中的列名将显示在结果中。
• 除非使用UNION ALL运算符，否则默认情况下输出按升序进行排序。
如果查询使用了集合运算符，则Oracle Server 会自动删除重复行，除非使用的是UNION ALL运算符。输出中的列名由第一个SELECT语句中列的列表确定。默认情况下，输出按SELECT子句第一列的升序进行排序。
对于复合查询包含的各个查询，其SELECT列表中的相应表达式在数量和数据类型上必须是相匹配的。如果这些查询选择了字符数据，则会按如下方式确定返回值的数据类型：
• 如果两个查询选择了数据类型为CHAR且长度相等的值，则返回值的数据类型也为CHAR且长度保持不变。如果两个查询选择了数据类型为CHAR但长度不同的值，
则返回值的数据类型为VARCHAR2，长度为较大的CHAR值。
• 如果其中一个或两个查询选择了数据类型为VARCHAR2的值，则返回值的数据类型也为VARCHAR2。
如果这些查询选择了数字数据，则按数字优先级确定返回值的数据类型。如果所有查询选择了数据类型为NUMBER的值，则返回值的数据类型也为NUMBER。
在使用集合运算符的查询中，Oracle Server 不会执行跨数据类型组的隐式转换。因此，如果这些查询的相应表达式解析为字符数据和数字数据，Oracle Server 则会返回错误。
- 本例中使用的表
本例中使用的表包括：
• EMPLOYEES：提供有关所有当前雇员的详细信息
• JOB_HISTORY：记录雇员更换职务时先前职务的开始日期和结束日期、职务标识号及部门的详细信息
[email protected]> desc EMPLOYEES
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
EMPLOYEE_ID NOT NULL NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
EMAIL NOT NULL VARCHAR2(25)
PHONE_NUMBER VARCHAR2(20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)
[email protected]> desc JOB_HISTORY
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
EMPLOYEE_ID NOT NULL NUMBER(6)
START_DATE NOT NULL DATE
END_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
DEPARTMENT_ID NUMBER(4)
EMPLOYEES表，该表中存储了雇员的详细信息，如唯一标识号、电子邮件地址、职务标识号（如ST_CLERK、SA_REP等等）、薪金和经理等等。
一些雇员已经在公司工作了很长时间，已更换为其它职务。因此可以使用JOB_HISTORY表对该信息进行监视。在雇员更换职务时，先前职务的开始日期和结束日期、job_id（如ST_CLERK、SA_REP等等）以及部门的详细信息都记录在JOB_HISTORY表中。
后面几页中将显示EMPLOYEES表和JOB_HISTORY表的结构和数据。
很多情况下，公司中的某些人员会在公司任期内多次担任相同的职务。例如，假定雇员Taylor 在1998 年3 月24 日加入公司。Taylor 在1998 年3 月24 日至1998 年12 月31 日期间担任职务SA_REP，在1999 年1 月1 日至1999 年12 月31 日期间担任职务SA_MAN。此后Taylor 又再次担任职务SA_REP一直到现在。
- UNION运算符
UNION运算符用于返回由任一查询选定的所有行。使用UNION运算符可以返回多个表中的所有行，但不包括重复行。
准则
• 所选列的数量必须相同。
• 所选列的数据类型必须属于相同的数据类型组（如数字或字符）。
• 列名不必相同。
• UNION将对所有选定的列执行操作。
• 在重复项检查过程中不会忽略NULL值。
• 默认情况下，输出按SELECT子句中列的升序进行排序。
- 使用UNION运算符
显示所有雇员的当前职务和先前职务的详细信息。每位雇员仅显示一次。
[email protected]> SELECT employee_id, job_id FROM employees
2 UNION
3 SELECT employee_id, job_id FROM job_history;
EMPLOYEE_ID JOB_ID
----------- ----------
100 AD_PRES
200 AC_ACCOUNT
200 AD_ASST
...
115 rows selected.
UNION运算符会删除所有重复记录。如果出现在EMPLOYEES表和JOB_HISTORY表中的记录是相同的，则这些记录只显示一次。在示例显示的输出中，会看到由于每行中的JOB_ID不同，EMPLOYEE_ID为200 的雇员的记录出现了两次。
请看如下示例：
SELECT employee_id, job_id, department_id FROM employees
2 UNION
3 SELECT employee_id, job_id, department_id FROM job_history;
EMPLOYEE_ID JOB_ID DEPARTMENT_ID
----------- ---------- -------------
100 AD_PRES 90
200 AC_ACCOUNT 90
200 AD_ASST 10
200 AD_ASST 90
...
116 rows selected.
在前面的输出中，雇员200 出现了三次。为什么？请注意雇员200 的DEPARTMENT_ID值。一行的DEPARTMENT_ID为90，另一行为10，第三行为90。由于职务ID 和部门ID 的组合是唯一的，所以雇员200 的每行都是唯一的，因此没有被认为是重复行。请注意，输出按SELECT子句的第一列（在此例中，为EMPLOYEE_ID）的升序进行排序。
- UNION ALL运算符
使用UNION ALL运算符可以返回多个查询中的所有行。
准则
适用于UNION和UNION ALL的准则是相同的，除了下面适用于UNION ALL的两个例外：与UNION不同，默认情况下UNION ALL不会删除重复行，而且不对输出进行排序。
- 使用UNION ALL运算符
显示所有雇员的当前部门和先前部门。
[email protected]> SELECT employee_id, job_id, department_id FROM employees
2 UNION ALL
3 SELECT employee_id, job_id, department_id FROM job_history
4 ORDER BY employee_id;
EMPLOYEE_ID JOB_ID DEPARTMENT_ID
----------- ---------- -------------
100 AD_PRES 90
176 SA_REP 80
176 SA_REP 80
176 SA_MAN 80
177 SA_REP 80
...
117 rows selected.
使用UNION ALL运算符
在此示例中，选择了117 行。在两个表*选择了117 行。UNION ALL运算符不会删除重复行。UNION将返回由任一查询选定的所有不同行。而UNION ALL将返回由任一查询选定的所有行，包括所有重复行。现在使用UNION子句编写该查询：
[email protected]> SELECT employee_id, job_id,department_id FROM employees
2 UNION
3 SELECT employee_id, job_id,department_id FROM job_history
4 ORDER BY employee_id;
EMPLOYEE_ID JOB_ID DEPARTMENT_ID
----------- ---------- -------------
100 AD_PRES 90
176 SA_MAN 80
176 SA_REP 80
177 SA_REP 80
...
116 rows selected.
上面的查询会返回116 行。这是因为此查询删除了后面一行（因为该行是重复行）：
- INTERSECT运算符
使用INTERSECT运算符可以返回多个查询的所有共同行。
准则
• 在查询中使用的所有SELECT语句中，由查询中的SELECT语句选定的列数和列的数据类型必须相同。不过，列名不必相同。
• 使相交的表按反方向排序不会更改结果。
• INTERSECT不会忽略NULL值。
- 使用INTERSECT运算符
显示符合以下条件的雇员的雇员ID 和职务ID：这些雇员的当前职务与以前的职务相同，也就是说这些雇员曾担任过别的职务，但现在又重新担任了以前的同一职务。
[email protected]> SELECT employee_id, job_id FROM employees
2 INTERSECT
3 SELECT employee_id, job_id FROM job_history;
EMPLOYEE_ID JOB_ID
----------- ----------
176 SA_REP
200 AD_ASST
在示例中，查询只返回在两个表的选定列中具有相同值的那些记录。
如果将DEPARTMENT_ID列添加到EMPLOYEES表的SELECT语句，将DEPARTMENT_ID列添加到JOB_HISTORY表的SELECT语句，然后运行此查询，会
产生什么结果？由于引入的其它列的值可能重复，也可能不重复，因此结果可能不同。
示例：
[email protected]> SELECT employee_id, job_id, department_id FROM employees
2 INTERSECT
3 SELECT employee_id, job_id, department_id FROM job_history;
EMPLOYEE_ID JOB_ID DEPARTMENT_ID
----------- ---------- -------------
176 SA_REP 80
由于EMPLOYEES.DEPARTMENT_ID值与JOB_HISTORY.DEPARTMENT_ID值不相同，所以结果中不再包含雇员200。
- MINUS运算符
使用MINUS运算符可以返回由第一个查询选定的但没有出现在第二个查询结果集中的所有不同行（第一个SELECT语句减去第二个SELECT语句）。
注：在查询中使用的所有SELECT语句中，由查询中的SELECT语句选定的列数必须相同，而且列的数据类型必须属于相同的数据类型组。不过，列名不必相同。
- 使用MINUS运算符
显示从未更换过职务的雇员的雇员ID。
SELECT employee_id FROM employees
MINUS
SELECT employee_id FROM job_history;
在示例中，用EMPLOYEES表中的雇员ID 减去JOB_HISTORY表中的雇员ID。
结果集中显示运行减法运算后剩下的雇员，这些雇员由存在于EMPLOYEES表中但并不存在于JOB_HISTORY表中的那些行代表。这些行记录了从未更换过职务的雇员。
- 匹配SELECT语句
• 使用UNION运算符可显示位置ID、部门名称和所在的省/市/自治区。
• 如果某些列不存在于一个表或另一个表中，则必须使用TO_CHAR函数或任何其它转换函数匹配数据类型。
[email protected]> SELECT location_id, department_name "Department", TO_CHAR(NULL) "Warehouse location" FROM departments
2 UNION
3 SELECT location_id, TO_CHAR(NULL) "Department", state_province FROM locations;
LOCATION_ID Department Warehouse location
----------- ------------------------------ -------------------------
1000
1100
1200 Tokyo Prefecture
1300
1400 IT
1400 Texas
由于查询的SELECT列表中的表达式必须在数量上相匹配，因此可以使用伪列和数据类型转换函数来遵循此规则。在示例中，已将名称Warehouse location指定为伪列
标题。在第一个查询中使用了TO_CHAR函数，以匹配由第二个查询检索的state_province列的VARCHAR2数据类型。同样，第二个查询中的TO_CHAR函数
用于匹配由第一个查询检索的department_name列的VARCHAR2数据类型。
- 匹配SELECT语句：示例
使用UNION运算符可显示所有雇员的雇员ID、职务ID 和薪金。
[email protected]> SELECT employee_id, job_id,salary FROM employees
2 UNION
3 SELECT employee_id, job_id,0 FROM job_history;
EMPLOYEE_ID JOB_ID SALARY
----------- ---------- ----------
100 AD_PRES 24000
101 AC_ACCOUNT 0
101 AC_MGR 0
101 AD_VP 17000
匹配SELECT语句：示例
EMPLOYEES和JOB_HISTORY表具有多个共同的列（例如，EMPLOYEE_ID、JOB_ID和DEPARTMENT_ID）。但是，如果知道薪金仅存在于EMPLOYEES表中，而且要在查询中使用UNION运算符来显示雇员ID、职务ID 和薪金，这时又该如何操作？
示例中将EMPLOYEES表和JOB_HISTORY表中的EMPLOYEE_ID列和JOB_ID列相匹配。将文字值0添加到JOB_HISTORY SELECT语句中，可匹配EMPLOYEES SELECT语句中SALARY列的数值。
在显示结果中，对应于JOB_HISTORY表中的一条记录的每个输出行在SALARY列中都包含一个0。
- 在集合运算中使用ORDER BY子句
• ORDER BY子句只能在复合查询的末尾出现一次。
• 这些查询中不能包含多个ORDER BY子句。
• ORDER BY子句仅识别第一个SELECT查询中的列。
• 默认情况下，使用第一个SELECT查询中的第一列按升序对输出进行排序。
ORDER BY子句只能在复合查询中使用一次。如果使用ORDER BY子句，则必须将其放在查询的末尾。ORDER BY子句接受列名或别名。默认情况下，输出按第一个SELECT查询的第一列的升序进行排序。
注：ORDER BY子句不能识别第二个SELECT查询中的列名。为避免列名混淆，通常的做法是对列位置执行ORDER BY。
例如，在下面的语句中，输出将按job_id的升序显示。
SELECT employee_id, job_id,salary FROM employees
UNION
SELECT employee_id, job_id,0 FROM job_history ORDER BY 2;
如果忽略ORDER BY，则默认情况下输出将按employee_id的升序进行排序。不能使用第二个查询中的列对输出进行排序。
转自：https://blog.csdn.net/rlhua/article/details/12883007