前言
本篇文章讲解的主要内容是：当两个表中有重复数据时，UNION的去重功能被忽略，UNION过程中如何识别展示出来、空值与空字符串的关系以及在UNION ALL中的使用、UNION与OR可以互相改写以及使用中的注意事项。
【SQL开发实战技巧】这一系列博主当作复习旧知识来进行写作，毕竟SQL开发在数据分析场景非常重要且基础，面试也会经常问SQL开发和调优经验，相信当我写完这一系列文章，也能再有所收获，未来面对SQL面试也能游刃有余~。
一、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总结
本篇博客主要是介绍有重复数据集时使用UNION要特别小心！！！不早了，快一点了睡觉！！！困😪😪
