集合操作(union和union all、intersect、minus)

集合操作前后数据集字段个数和字段数据类型要匹配（处于同一数据类型组）

select employee_id,last_name,department_id from employees where department_id=30 or department_id is null;  //employees表部门id是50或空的行
select employee_id,last_name,department_id from employees where department_id in(30,40);  //employees表部门id是30或40的行

 以下三种集合除了union all之外都会去重。number和char类型也会报错，即使有隐式转换也会报错。

一、union和union all取并集

相同点：把前后两个select集合的数据集进行并集操作，组合成一个结果集查询输出，要求联合前后的结果集有相同的输出字段数目，并且对应的字段类型要相同

不同点：union进行并集操作不包括重复行，相当于distinct操作，并对获取的结果进行排序；union all进行并集操作包括重复行，不会对获取的结果进行排序 

1、union并集操作有8行，无重复行

select employee_id,last_name,department_id from employees where department_id=30 or department_id is null
union
select employee_id,last_name,department_id from employees where department_id in(30,40);

2、union all并集操作有14行，有重复行，并且没有按照前面的列进行排序

select employee_id,last_name,department_id from employees where department_id=30 or department_id is null
union all
select employee_id,last_name,department_id from employees where department_id in(30,40);

可以对数据集（两子句）进行并集操作后排序，相当于对整体进行排序

select employee_id,last_name,department_id from employees where department_id=30 or department_id is null
union all
select employee_id,last_name,department_id from employees where department_id in(30,40)
order by employee_id; 

union子句不能出现order by，如下会出现错误

select employee_id,last_name,department_id from employees where department_id=30 or department_id is null  order by employee_id 
union all 
select employee_id,last_name,department_id from employees where department_id in(30,40)  order by employee_id;

但可以先对union子句的子句进行order by，相当于先两个数据集进行排序，再并集，也说明union all不会对数据集进行排序

select * from (select employee_id,last_name,department_id from employees where department_id=30 or department_id is null order by employee_id)
union all
select * from (select employee_id,last_name,department_id from employees where department_id in(30,40) order by employee_id);

 

二、intersect取交集

select employee_id,last_name,department_id from employees where department_id=30 or department_id is null
intersect
select employee_id,last_name,department_id from employees where department_id in(30,40) or department_id is null;  //两个子集都包含null的department_id

有7条数据，包含空值。

 

三、minus取差集

select employee_id,last_name,department_id from employees where department_id=30 or department_id is null
minus
select employee_id,last_name,department_id from employees where department_id in(30,40) or department_id is null;  //小的减大的就会返回空值

select employee_id,last_name,department_id from employees where department_id in(30,40) or department_id is null
minus
select employee_id,last_name,department_id from employees where department_id=30;  //大减小返回差值

 

有空值。