系列文章目录

【SQL开发实战技巧】系列（一）:关于SQL不得不说的那些事

【SQL开发实战技巧】系列（二）：简单单表查询

【SQL开发实战技巧】系列（三）：SQL排序的那些事

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

【SQL开发实战技巧】系列（五）：从执行计划看IN、EXISTS 和 INNER JOIN效率，我们要分场景不要死记网上结论

【SQL开发实战技巧】系列（六）：从执行计划看NOT IN、NOT EXISTS 和 LEFT JOIN效率，记住内外关联条件不要乱放

【SQL开发实战技巧】系列（七）：从有重复数据前提下如何比较出两个表中的差异数据及对应条数聊起

【SQL开发实战技巧】系列（八）：聊聊如何插入数据时比约束更灵活的限制数据插入以及怎么一个insert语句同时插入多张表

【SQL开发实战技巧】系列（九）：一个update误把其他列数据更新成空了？Merge改写update！给你五种删除重复数据的写法！

【SQL开发实战技巧】系列（十）：从拆分字符串、替换字符串以及统计字符串出现次数说起

【SQL开发实战技巧】系列（十一）：拿几个案例讲讲translate|regexp_replace|listagg|wmsys.wm_concat|substr|regexp_substr常用函数

【SQL开发实战技巧】系列（十二）：三问(如何对字符串字母去重后按字母顺序排列字符串？如何识别哪些字符串中包含数字？如何将分隔数据转换为多值IN列表?)

【SQL开发实战技巧】系列（十三）：讨论一下常用聚集函数&通过执行计划看sum()over()对员工工资进行累加

【SQL开发实战技巧】系列（十四）：计算消费后的余额&计算银行流水累计和&计算各部门工资排名前三位的员工

【SQL开发实战技巧】系列（十五）：查找最值所在行数据信息及快速计算总和百之max/min() keep() over()、fisrt_value、last_value、ratio_to_report

【SQL开发实战技巧】系列（十六）：数据仓库中时间类型操作（初级）日、月、年、时、分、秒之差及时间间隔计算

【SQL开发实战技巧】系列（十七）：数据仓库中时间类型操作（初级）确定两个日期之间的工作天数、计算—年中周内各日期出现次数、确定当前记录和下一条记录之间相差的天数

【SQL开发实战技巧】系列（十八）：数据仓库中时间类型操作（进阶）INTERVAL、EXTRACT以及如何确定一年是否为闰年及周的计算

【SQL开发实战技巧】系列（十九）：数据仓库中时间类型操作（进阶）如何一个SQL打印当月或一年的日历？如何确定某月内第一个和最后—个周内某天的日期？

【SQL开发实战技巧】系列（二十）：数据仓库中时间类型操作（进阶）获取季度开始结束时间以及如何统计非连续性时间的数据

【SQL开发实战技巧】系列（二十一）：数据仓库中时间类型操作（进阶）识别重叠的日期范围，按指定10分钟时间间隔汇总数据

【SQL开发实战技巧】系列（二十二）：数仓报表场景☞ 从分析函数效率一定快吗聊一聊结果集分页和隔行抽样实现方式

【SQL开发实战技巧】系列（二十三）：数仓报表场景☞ 如何对数据排列组合去重以及通过如何找到包含最大值和最小值的记录这个问题再次用执行计划给你证明分析函数性能不一定高

【SQL开发实战技巧】系列（二十四）：数仓报表场景☞通过案例执行计划详解”行转列”,”列转行”是如何实现的

【SQL开发实战技巧】系列（二十五）：数仓报表场景☞结果集中的重复数据只显示一次以及计算部门薪资差异高效的写法以及如何对数据进行快速分组

【SQL开发实战技巧】系列（二十六）：数仓报表场景☞聊聊ROLLUP、UNION ALL是如何分别做分组合计的以及如何识别哪些行是做汇总的结果行

文章目录

• 系列文章目录
• 前言
• 一、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
    

    ---

    总结

    本篇博客主要是介绍有重复数据集时使用UNION要特别小心！！！不早了，快一点了睡觉！！！困😪😪

     
     祁东网站设计公司  南昌网站建设方案咨询  学校网站设计论文  网站设计属于什么专业  网站构建的基本流程  江门网站建设策划