系列文章目录

【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列表?)

文章目录

• 系列文章目录
• 前言
• • 一、如果有重复数据如何检查出两个表中的差异数据及对应条数
  • 二、表连接做聚合容易出现重复计算的错误
  • 三、多表查询空值处理问题
  • 四、NOT IN的子查询范围不能是空值，否则查询结果为空
• 总结

前言

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

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

有这么一个临时表

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

要求用查询找出T与表emp中不同的数据。
注意：T中员工SCOTT有两行数据，而emp表中只有一条数据。

SQL> with t as (2  SELECT* FROM emp WHERE deptno != 103  UNION ALL4  SELECT* FROM emp WHERE ename = 'SCOTT'5  )6  select rownum,empno,ename from t where ename='SCOTT';ROWNUM EMPNO ENAME
---------- ----- ----------1  7788 SCOTT2  7788 SCOTTSQL> select rownum,empno,ename from emp where ename='SCOTT';ROWNUM EMPNO ENAME
---------- ----- ----------1  7788 SCOTT

比较两个数据集的不同时，通常用类似下面的FULL JOIN语句:

with t as(SELECT *FROM empWHERE deptno != 10UNION ALLSELECT *FROM empWHERE ename = 'SCOTT')
select t.empno, t.ename, e.empno, e.enamefrom tfull join emp eon (t.empno = e.empno)where t.empno is nullor e.empno is null;EMPNO ENAME      EMPNO ENAME
----- ---------- ----- ----------7782 CLARK7839 KING7934 MILLER1001 test

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

with t as(SELECT *FROM empWHERE deptno != 10UNION ALLSELECT *FROM empWHERE ename = 'SCOTT')
select t.empno, t.ename, e.empno, e.enamefrom (select empno, ename, count(*) cnt from t group by empno, ename) tfull join (select empno, ename, count(*) cntfrom empgroup by empno, ename) eon (t.empno = e.empno and t.cnt = e.cnt)where t.empno is nullor e.empno is null;EMPNO ENAME      EMPNO ENAME
----- ---------- ----- ----------1001 test7782 CLARK7788 SCOTT7839 KING7934 MILLER7788 SCOTT            6 rows selected

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

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

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

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

with t as (
select '7934' as empno,1 as lev from dual 
union all
select '7934' as empno,2 as lev from dual 
union all
select '7839' as empno,3 as lev from dual 
union all
select '7782' as empno,1 as lev from dual 
),
t1 as (
select empno,sum(case when lev = 1 then 0.1 when lev = 2 then 0.2 when lev = 3 then0.3end) as levsfrom tgroup by empno
)
select deptno, sum(sal) as sumsal, sum(sal * t1.levs) as sumlevsfrom empinner join t1on (emp.empno = t1.empno)where deptno = 10group by deptno

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

先看下当前emp表数据：

SQL> select * from emp2  ;EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------7369 SMITH      CLERK      7902 1980-12-17     800.00               207499 ALLEN      SALESMAN   7698 1981-2-20     1600.00    300.00     307521 WARD       SALESMAN   7698 1981-2-22     1250.00    500.00     307566 JONES      MANAGER    7839 1981-4-2      2975.00               207654 MARTIN     SALESMAN   7698 1981-9-28     1250.00   1400.00     307698 BLAKE      MANAGER    7839 1981-5-1      2850.00               307782 CLARK      MANAGER    7839 1981-6-9      2450.00               107788 SCOTT      ANALYST    7566 1987-4-19     3000.00               207839 KING       PRESIDENT       1981-11-17    5000.00               107844 TURNER     SALESMAN   7698 1981-9-8      1500.00      0.00     307876 ADAMS      CLERK      7788 1987-5-23     1100.00               207900 JAMES      CLERK      7698 1981-12-3      950.00               307902 FORD       ANALYST    7566 1981-12-3     3000.00               207934 MILLER     CLERK      7782 1982-1-23     1300.00               101001 test                       2021-10-9 1                     15 rows selected

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

SQL> select * from emp where comm < 1400;EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------7499 ALLEN      SALESMAN   7698 1981-2-20     1600.00    300.00     307521 WARD       SALESMAN   7698 1981-2-22     1250.00    500.00     307844 TURNER     SALESMAN   7698 1981-9-8      1500.00      0.00     30

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

SQL> select * from emp where coalesce(comm,0) < 1400;EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------7369 SMITH      CLERK      7902 1980-12-17     800.00               207499 ALLEN      SALESMAN   7698 1981-2-20     1600.00    300.00     307521 WARD       SALESMAN   7698 1981-2-22     1250.00    500.00     307566 JONES      MANAGER    7839 1981-4-2      2975.00               207698 BLAKE      MANAGER    7839 1981-5-1      2850.00               307782 CLARK      MANAGER    7839 1981-6-9      2450.00               107788 SCOTT      ANALYST    7566 1987-4-19     3000.00               207839 KING       PRESIDENT       1981-11-17    5000.00               107844 TURNER     SALESMAN   7698 1981-9-8      1500.00      0.00     307876 ADAMS      CLERK      7788 1987-5-23     1100.00               207900 JAMES      CLERK      7698 1981-12-3      950.00               307902 FORD       ANALYST    7566 1981-12-3     3000.00               207934 MILLER     CLERK      7782 1982-1-23     1300.00               101001 test                       2021-10-9 1                     14 rows selected

上面我还是使用的coalesce，你也可以用nvl，前面文章讨论过这俩的区别了。

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

先看一个查询：

SQL> SELECT* FROM dept WHERE deptno NOT IN (SELECT emp.deptno FROM emp WHERE emp.deptno  IS NOT NULL);DEPTNO DNAME          LOC
------ -------------- -------------40 OPERATIONS     BOSTON

如果我们把这个WHERE emp.deptno IS NOT NULL去掉呢？

SQL> SELECT* FROM dept WHERE deptno NOT IN (SELECT emp.deptno FROM emp);DEPTNO DNAME          LOC
------ -------------- -------------SQL> 

发现没有查询结果了，这是因为啥？
我们之前文章介绍过，NULL不支持加、减、乘、除、大小比较、相等比较，否则只能为空。
所以这里类比成SELECT* FROM dept WHERE deptno NOT IN (null)，那结果肯定是空值了，这里一定要记住，如果你不能保证你的not in子查询范围一定不为空，那一定要加上null值过滤条件，否则你的查询结果是错误的！

总结

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