一、UNION ALL 与空字符串
通过前面博客的案例可以看到，我们多次使用了UNION ALL。UNLON ALL通常用于合并多个数据集。
看下面的语句：

SELECT empno AS 编码, ename AS 名称, nvl(mgr, deptno) AS 上级编码
FROM emp
WHERE empno = 7788
UNION ALL
SELECT deptno AS 编码, dname AS 名称, null AS 上级编码
FROM dept
WHERE deptno = 10;
编码 名称 上级编码
---------- -------------- ----------
7788 SCOTT 7566
10 ACCOUNTING

可以看到，当其中一个数据集列不够时，可以用null来填充该列的值，而空字符串在
Oracle中常常相当于null。

SQL> select '' as a from dual;

A
--------------------------------

null
己选择 1 行。

为什么不说空字符串等价于null呢？看下面的示例：

SELECT empno AS 编码, ename AS 名称, nvl(mgr, deptno) AS 上级编码
FROM emp
WHERE empno = 7788
UNION ALL
SELECT deptno AS 编码, dname AS 名称, '' AS 上级编码
FROM dept
WHERE deptno = 10;
ORA-01790: 表达式必须具有与对应表达式相同的数据类型

可以看到，空字符串本身是varchar2类型，这与null可以是任何类型不同，当然，它们也就不等价。

二、UNION 与 OR(有重复数据的数据集用UNION后得到的数据与预期不一致如何解决)
当在条件里有or时，经常会改写为UNION,例如，我们在表emp中建立下面两个索引。


create index IDX_EMPNO on EMP (EMPNO);
create index IDX_ENAME on EMP (ENAME);

然后执行下面查询：

SQL> SELECT empno, ename
2 FROM emp
3 WHERE empno = 7788
4 OR ename = 'SCOTT';

EMPNO ENAME
----- ----------
7788 SCOTT

如果改写为UNION ALL,则结果就是错的：

SQL> SELECT empno,ename FROM emp WHERE empno=7788 UNION ALL
2 SELECT empno,ename FROM emp WHERE ename='SCOTT';

EMPNO ENAME
----- ----------
7788 SCOTT
7788 SCOTT

因为原语句中用的条件是or,是两个结果的合集而非并集，所以一般改写时需要改为
UNION来去掉重复的数据。

SQL> SELECT empno, ename FROM emp WHERE empno = 7788 UNION
2 SELECT empno , ename FROM emp WHERE ename = 'SCOTT' ;

EMPNO ENAME
----- ----------
7788 SCOTT

这样两个语句分别可以用empno及ename上的索引。
我们对比一下 PLAN。
更改前（为了消除bitmapconvert的影响，先设置参数。）

SQL> alter session set "_b_tree_bitmap_plans" = false;

Session altered


SQL> explain plan for SELECT/*+ OPTIMIZER_FEATURES_ENABLE('11.2.0.4') */ empno, ename FROM emp WHERE empno = 7788 OR ename = 'SCOTT';


Explained


SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 20 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 2 | 20 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMPNO"=7788 OR "ENAME"='SCOTT')

13 rows selected


这时是 FULL TABLE。
更改后的 PLAN：

SQL>
SQL> explain plan for SELECT empno, ename FROM emp WHERE empno = 7788 UNION SELECT empno , ename FROM emp WHERE ename = 'SCOTT' ;

Explained


SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2024585924
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 40 |
| 1 | SORT UNIQUE | | 2 | 40 |
| 2 | UNION-ALL | | | |
| 3 | TABLE ACCESS BY INDEX ROWID | EMP | 1 | 20 |
|* 4 | INDEX UNIQUE SCAN | PK_EMP | 1 | |
| 5 | TABLE ACCESS BY INDEX ROWID BATCHED| EMP | 1 | 20 |
|* 6 | INDEX RANGE SCAN | IDX_ENAME | 1 | |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("EMPNO"=7788)
6 - access("ENAME"='SCOTT')

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Note
-----
- dynamic statistics used: dynamic sampling (level=2)

23 rows selected


可以看到，更改后分别用了两列中的索引。
但在改写时，UNION的去重功能有时会被忽略，从而使数据出现错误，如下面的语句。

SELECT empno, deptno FROM emp WHERE mgr = 7698 ORDER BY 1 ;
EMPNO DEPTNO
----- ------
7499 30
7521 30
7654 30
7844 30
7900 30
SELECT empno, deptno FROM emp WHERE job ='SALESMAN' ORDER BY 1 ;

EMPNO DEPTNO
----- ------
7499 30
7521 30
7654 30
7844 30


两个条件中有4行数据是重复的，使用or连接两个条件将得到5行数据：

SELECT empno, deptno FROM emp WHERE job ='SALESMAN' or mgr=7698 ORDER BY 1 ;

SQL> SELECT empno, deptno FROM emp WHERE job ='SALESMAN' or mgr=7698 ORDER BY 1 ;

EMPNO DEPTNO
----- ------
7499 30
7521 30
7654 30
7844 30
7900 30

而改成union后

SELECT deptno FROM emp WHERE job ='SALESMAN'
union
SELECT deptno FROM emp WHERE mgr=7698 ;
DEPTNO
------
30

只剩下了一行数据，结果显然不对。
以上实验可以看出：

不仅两个数据集间重复的数据会被去重，而且单个数据集里重复的数据也会被去重。
有重复数据的数据集用UNION后得到的数据与预期会不一致。
用UNION ALL来模拟UNION语句的过程，语句如下：

select distinct deptno
from (
SELECT deptno FROM emp WHERE job ='SALESMAN'
union
SELECT deptno FROM emp WHERE mgr=7698
)order by 1;

其实，就是合并->去重->排序这三步，那么对结果的影响也就可想而知了。既然如此，像这种数据还可以用UNION改写吗？答案是肯定的。
我们只需在去重前加入一个可以唯一标识各行的列即可。
例如，在这里可以加入"empno",再利用UNION,效果如下：

SELECT empno,deptno FROM emp WHERE job ='SALESMAN'
union
SELECT empno,deptno FROM emp WHERE mgr=7698
EMPNO DEPTNO
----- ------
7499 30
7521 30
7654 30
7844 30
7900 30

加入唯一列empno后，既保证了正确的去重，又防止了不该发生的去重。在此基础上，再嵌套一层就是想要的结果。

select deptno from (
SELECT empno,deptno FROM emp WHERE job ='SALESMAN'
union
SELECT empno,deptno FROM emp WHERE mgr=7698)
order by 1;

DEPTNO
------
30
30
30
30
30

除了用唯一列、主键列外， 还可 以使用 rowid:

select deptno from (
SELECT rowid,deptno FROM emp WHERE job ='SALESMAN'
union
SELECT rowid,deptno FROM emp WHERE mgr=7698)
order by 1;

如果数据不是取自表，而是取自VIEW或则没有唯一列，那么应怎么处理呢？
我们可以增加rownum来当作唯一列：

with t as (
select rownum as sn,deptno,mgr,job from emp
)
select deptno from (
SELECT sn,deptno FROM t WHERE job ='SALESMAN'
union
SELECT sn,deptno FROM t WHERE mgr=7698)
order by 1;
DEPTNO
------
30
30
30
30
30

————————————————

免责申明：

本文系转载，版权归原作者所有，如若侵权请联系我们进行删除！

想了解或咨询更多有关袋鼠云大数据产品、行业解决方案、客户案例的朋友，浏览袋鼠云官网：https://www.dtstack.com/?src=bbs
同时，欢迎对大数据开源项目有兴趣的同学加入「袋鼠云开源框架钉钉技术群」，交流最新开源技术信息，群号码：30537511，项目地址：https://github.com/DTStack