Oracle集合运算符详解：UNION和UNION ALL的区别是什么_Oracle

前情提要：本篇博客详细介绍了oracle的sql的集合运算符，包括union、union all、intersect、minus，并且介绍了对应的操作规则和注意事项，并且有使用示例和解析

oracle版本：19c

一、集合运算符的类型和准则

集合操作

集合操作准则

select列表中的表达式必须在个数上匹配。
后续查询中每列的数据类型必须与第一个查询中其对应列的数据类型匹配。
括号可用于更改执行顺序。
order by子句只能出现在语句的最后。

oracle服务器和集合运算符

除union all外，所有行都将自动消除（除union all运算符外其余集合运算符都会自动去重）。
来自第一个查询的列名将出现在结果中（结果的列名是第一个查询写的列名）。
默认情况下，输出以升序排序，但union all除外。

二、union 和 union all 运算符

2.1 union

union操作

union运算符使用示例：

select 'a','b' from dual
union 
select 'a','d' from dual;

-- union会自动去重
select 'a','b' from dual
union 
select 'a','b' from dual;

2.2 union all

union all 使用示例：

-- union all 不会去重
select 'a','b' from dual
union all
select 'a','b' from dual;

三、intersect运算符（相交运算符）

使用示例

select * from employees		-- 员工表中的所有人的信息
intersect
select * from employees where salary > 10000;	-- 员工表中工资大于10000的人的信息
-- 最后相交返回的结果就是工资大于10000的人的信息

四、minus运算符（相减运算符）

使用示例

select * from employees		-- 所有员工的信息
minus 
select * from employees where salary > 10000;	-- 工资大于10000的员工的信息
-- 返回的结果就是所有员工减去工资大于10000的员工，也就是所有工资不大于10000的员工的信息
-- 可见返回了92行信息

-- 需要注意的是使用minus时要用大表-小表，比如上例中所有员工包含了107行信息，是大表，工资大于10000的员工包含了15行信息，是小表
-- 如果将小表写在前面就变成了小表-大表，会造成如下结果
-- 将上例查询内容调换位置
select * from employees where salary > 10000
minus 
select * from employees;
-- 可见查询结果为空，所以使用minus时要注意保证大表-小表

五、匹配select列的数据类型

当一个或另一个表中不存在列时，必须匹配数据类型（使用to_char函数或任何其他转换函数）。

示例

-- 准备两个示例表b和c，可见b的id3列和c的id2列的数据类型不一样
sql> desc b;

name                                      null?    type
----------------------------------------- -------- ----------------------------
id3                                                number
id4                                                number(10)

sql> desc c;

name                                      null?    type
----------------------------------------- -------- ----------------------------
id4                                                number
id2                                                varchar2(10)

-- 直接进行集合运算会报错，因为数据类型不一致
sql> select id4,id3 from b
  2  union
  3  select id4,id2 from c;
select id4,id3 from b
           *
error at line 1:
ora-01790: expression must have same datatype as corresponding expression

-- 使用to_char转换id3的数据类型，可见可以成功进行集合运算
sql> select id4,to_char(id3) as haha from b
  2  union
  3  select id4,id2 from c;

       id4 haha
---------- ----------------------------------------
         3 10
         4 20
        10 100
        20 200

六、在集合操作中使用order by 子句

在复合查询的末尾，order by子句只能出现一次。
组件查询不能有单独的order by子句。
order by子句仅识别第一个select查询的列。
默认情况下，第一个select查询的第一列用于按升序对输出进行排序。

示例

-- order by 只能放在语句的最后，并且只能识别第一个select中查询的列
sql> select id4,to_char(id3) as haha from b
  2  union
  3* select id4,id2 from c order by haha;

       id4 haha
---------- ----------------------------------------
         3 10
        10 100
         4 20
        20 200