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

系列文章目录

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

文章目录

前言

本篇文章讲解的主要内容是：如果有重复数据如何检查出两个表中的差异数据及对应条数、表连接做聚合容易出现重复计算的错误、多表查询空值处理问题、NOT IN的子查询范围不能是空值，否则查询结果为空。
【SQL开发实战技巧】这一系列博主当作复习旧知识来进行写作，毕竟SQL开发在数据分析场景非常重要且基础，面试也会经常问SQL开发和调优经验，相信当我写完这一系列文章，也能再有所收获，未来面对SQL面试也能游刃有余~。

一、如果有重复数据如何检查出两个表中的差异数据及对应条数

有这么一个临时表

with t as(SELECT*FROM emp WHERE deptno !=10UNIONALLSELECT*FROM emp WHERE ename ='SCOTT')select*from t

要求用查询找出T与表emp中不同的数据。

注意：T中员工SCOTT有两行数据，而emp表中只有一条数据。

SQL>with t as(2SELECT*FROM emp WHERE deptno !=103UNIONALL4SELECT*FROM emp WHERE ename ='SCOTT'5)6select rownum,empno,ename from t where ename='SCOTT';

    ROWNUM EMPNO ENAME
---------- ----- ----------17788 SCOTT
         27788 SCOTT

SQL>select rownum,empno,ename from emp where ename='SCOTT';

    ROWNUM EMPNO ENAME
---------- ----- ----------17788 SCOTT

比较两个数据集的不同时，通常用类似下面的

FULL JOIN

语句:

with t as(SELECT*FROM emp
   WHERE deptno !=10UNIONALLSELECT*FROM emp
   WHERE ename ='SCOTT')select t.empno, t.ename, e.empno, e.ename
  from t
  fulljoin emp e
    on(t.empno = e.empno)where t.empno isnullor e.empno isnull;

EMPNO ENAME      EMPNO ENAME
----- ---------- ----- ----------7782 CLARK
                  7839 KING
                  7934 MILLER
                  1001 test

但是这种语句在这个案例中查不到SCOTT的区别。那我们应该怎么才能查到呢？
我们可以先对数据进行处理，增加一列显示相同数据的条数，再进行比较：

with t as(SELECT*FROM emp
   WHERE deptno !=10UNIONALLSELECT*FROM emp
   WHERE ename ='SCOTT')select t.empno, t.ename, e.empno, e.ename
  from(select empno, ename,count(*) cnt from t groupby empno, ename) t
  fulljoin(select empno, ename,count(*) cnt
               from emp
              groupby empno, ename) e
    on(t.empno = e.empno and t.cnt = e.cnt)where t.empno isnullor e.empno isnull;

EMPNO ENAME      EMPNO ENAME
----- ---------- ----- ----------1001 test
                  7782 CLARK
                  7788 SCOTT
                  7839 KING
                  7934 MILLER
 7788 SCOTT            

6rows selected

二、表连接做聚合容易出现重复计算的错误

现在有一张员工级别临时表：

with t as(select'7934'as empno,1as lev from dual 
unionallselect'7934'as empno,2as lev from dual 
unionallselect'7839'as empno,3as lev from dual 
unionallselect'7782'as empno,1as lev from dual 
)

员工的奖金根据lev计算，lev=1的奖金为员工工资的10%,lev=2的奖金为员工工资的20%,lev=3的奖金为员工工资的30%。
现要求返回上述员工（也就是部门10的所有员工）的工资及奖金。
如果你马上想到的是先关联，然后对结果做聚集。那么你可以尝试一下，会发现7934’的工资被重复计算了两次，正确的做法应该是先对T表做聚合然后再关联！正确的SQL放到下面！

with t as(select'7934'as empno,1as lev from dual 
unionallselect'7934'as empno,2as lev from dual 
unionallselect'7839'as empno,3as lev from dual 
unionallselect'7782'as empno,1as lev from dual 
),
t1 as(select empno,sum(casewhen lev =1then0.1when lev =2then0.2when lev =3then0.3end)as levs
  from t
 groupby empno
)select deptno,sum(sal)as sumsal,sum(sal * t1.levs)as sumlevs
  from emp
 innerjoin t1
    on(emp.empno = t1.empno)where deptno =10groupby deptno

三、多表查询空值处理问题

先看下当前emp表数据：

SQL>select*from emp
  2;

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------7369 SMITH      CLERK      79021980-12-17800.00207499 ALLEN      SALESMAN   76981981-2-201600.00300.00307521 WARD       SALESMAN   76981981-2-221250.00500.00307566 JONES      MANAGER    78391981-4-22975.00207654 MARTIN     SALESMAN   76981981-9-281250.001400.00307698 BLAKE      MANAGER    78391981-5-12850.00307782 CLARK      MANAGER    78391981-6-92450.00107788 SCOTT      ANALYST    75661987-4-193000.00207839 KING       PRESIDENT       1981-11-175000.00107844 TURNER     SALESMAN   76981981-9-81500.000.00307876 ADAMS      CLERK      77881987-5-231100.00207900 JAMES      CLERK      76981981-12-3950.00307902 FORD       ANALYST    75661981-12-33000.00207934 MILLER     CLERK      77821982-1-231300.00101001 test                       2021-10-9115rows selected

接下来我有一个需求：让我们查找comm小于1400的员工信息：

SQL>select*from emp where comm <1400;

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------7499 ALLEN      SALESMAN   76981981-2-201600.00300.00307521 WARD       SALESMAN   76981981-2-221250.00500.00307844 TURNER     SALESMAN   76981981-9-81500.000.0030

不对呀，哪些comm是null的员工也得查出来啊，那么我们在查询的时候要注意，如果你不能保证你要比较大小的列(比如comm)，那你写sql的时候要注意，对空值一定要做个转换，正确的写法：

SQL>select*from emp wherecoalesce(comm,0)<1400;

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------7369 SMITH      CLERK      79021980-12-17800.00207499 ALLEN      SALESMAN   76981981-2-201600.00300.00307521 WARD       SALESMAN   76981981-2-221250.00500.00307566 JONES      MANAGER    78391981-4-22975.00207698 BLAKE      MANAGER    78391981-5-12850.00307782 CLARK      MANAGER    78391981-6-92450.00107788 SCOTT      ANALYST    75661987-4-193000.00207839 KING       PRESIDENT       1981-11-175000.00107844 TURNER     SALESMAN   76981981-9-81500.000.00307876 ADAMS      CLERK      77881987-5-231100.00207900 JAMES      CLERK      76981981-12-3950.00307902 FORD       ANALYST    75661981-12-33000.00207934 MILLER     CLERK      77821982-1-231300.00101001 test                       2021-10-9114rows selected

上面我还是使用的

coalesce

，你也可以用

nvl

，前面文章讨论过这俩的区别了。

四、NOT IN的子查询范围不能是空值，否则查询结果为空

先看一个查询：

SQL>SELECT*FROM dept WHERE deptno NOTIN(SELECT emp.deptno FROM emp WHERE emp.deptno  ISNOTNULL);

DEPTNO DNAME          LOC
------ -------------- -------------40 OPERATIONS     BOSTON

如果我们把这个

WHERE emp.deptno IS NOT NULL

去掉呢？

SQL>SELECT*FROM dept WHERE deptno NOTIN(SELECT emp.deptno FROM emp);

DEPTNO DNAME          LOC
------ -------------- -------------SQL>

发现没有查询结果了，这是因为啥？
我们之前文章介绍过，NULL不支持加、减、乘、除、大小比较、相等比较，否则只能为空。
所以这里类比成

SELECT* FROM dept WHERE deptno NOT IN (null)

，那结果肯定是空值了，这里一定要记住，如果你不能保证你的

not in

子查询范围一定不为空，那一定要加上null值过滤条件，否则你的查询结果是错误的！

总结

本章介绍的如果有重复数据如何检查出两个表中的差异数据及对应条数、表连接做聚合容易出现重复计算的错误、多表查询空值处理问题、NOT IN的子查询范围不能是空值，否则查询结果为空这四个案例，是工作中非常容易遇到的场景，也是很容易犯错的地方，博主写出来也给自己一个提醒！