Oracle-SET运算符/查询的并交差

1.union/union all

1.必须具有对应的列数以及数据类型

2.默认按照第一列从小到大的顺序排列

3.union 去重且排序；

4.union all 不去重且不拍序。

--employees01 1-70,33-80;employees01 3-90,33-80
【employee01有70号部门1人，80号部门3人，一下类似】

--查找去重之后两表的并集-union-37
/*
select employee_id,department_id
from employees01
union
select employee_id,department_id
from employees02
*/
--查找去重之前两表的并集-union-70
/*
select employee_id,department_id
from employees01
union all
select employee_id,department_id
from employees02
*/

2.intersect

--取二者的交集-intersect-33-80
/*
select employee_id,department_id
from employees01
intersect
select employee_id,department_id
from employees02
*/

3.minus

前表-二者共有的

--取二者的差集-minus-1-70
/*
select employee_id,department_id
from employees01
minus
select employee_id,department_id
from employees02

*/

4.使用相对位置进行排序

--查询10,50，,20号部门的job_id,department_id 并且 department_id按10,50,20的顺序排列
/*
select job_id,department_id,1 from employees where department_id = 10
union
select job_id,2 from employees where department_id = 50
union
select job_id,3 from employees where department_id = 20
order by 3 asc

*/
--column a_dummy noprin; 不打印该别名对应的列

--查询所有员工的last_name,department_name
/*
select last_name,to_char(null) from employees
union
select to_char(null),department_name from departments
*/

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!