【SQL开发实战技巧】系列（四）：从执行计划讨论UNION ALL与空字符串&UNION与OR的使用注意事项

本文链接：https://blog.csdn.net/qq_28356739/article/details/128928303

系列文章目录

文章目录

系列文章目录
前言
一、UNION ALL 与空字符串
二、UNION 与 OR(有重复数据的数据集用UNION后得到的数据与预期不一致如何解决)
总结

前言

本篇文章讲解的主要内容是：当两个表中有重复数据时，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