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

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 != 10
3 UNION ALL
4 SELECT* FROM emp WHERE ename = 'SCOTT'
5 )
6 select rownum,empno,ename from t where ename='SCOTT';

ROWNUM EMPNO ENAME
---------- ----- ----------
1 7788 SCOTT
2 7788 SCOTT

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

ROWNUM EMPNO ENAME
---------- ----- ----------
1 7788 SCOTT


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

with t as
(SELECT *
FROM emp
WHERE deptno != 10
UNION ALL
SELECT *
FROM emp
WHERE ename = 'SCOTT')
select t.empno, t.ename, e.empno, e.ename
from t
full join emp e
on (t.empno = e.empno)
where t.empno is null
or e.empno is null;

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


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

with t as
(SELECT *
FROM emp
WHERE deptno != 10
UNION ALL
SELECT *
FROM emp
WHERE ename = 'SCOTT')
select t.empno, t.ename, e.empno, e.ename
from (select empno, ename, count(*) cnt from t group by empno, ename) t
full join (select empno, ename, count(*) cnt
from emp
group by empno, ename) e
on (t.empno = e.empno and t.cnt = e.cnt)
where t.empno is null
or e.empno is null;

EMPNO ENAME EMPNO ENAME
----- ---------- ----- ----------
1001 test
7782 CLARK
7788 SCOTT
7839 KING
7934 MILLER
7788 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 then
0.3end) as levs
from t
group by empno
)
select deptno, sum(sal) as sumsal, sum(sal * t1.levs) as sumlevs
from emp
inner join t1
on (emp.empno = t1.empno)
where deptno = 10
group by deptno


三、多表查询空值处理问题
先看下当前emp表数据：

SQL> select * from emp
2 ;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980-12-17 800.00 20
7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981-4-2 2975.00 20
7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30
7782 CLARK MANAGER 7839 1981-6-9 2450.00 10
7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20
7839 KING PRESIDENT 1981-11-17 5000.00 10
7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30
7876 ADAMS CLERK 7788 1987-5-23 1100.00 20
7900 JAMES CLERK 7698 1981-12-3 950.00 30
7902 FORD ANALYST 7566 1981-12-3 3000.00 20
7934 MILLER CLERK 7782 1982-1-23 1300.00 10
1001 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 30
7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30
7844 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 20
7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981-4-2 2975.00 20
7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30
7782 CLARK MANAGER 7839 1981-6-9 2450.00 10
7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20
7839 KING PRESIDENT 1981-11-17 5000.00 10
7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30
7876 ADAMS CLERK 7788 1987-5-23 1100.00 20
7900 JAMES CLERK 7698 1981-12-3 950.00 30
7902 FORD ANALYST 7566 1981-12-3 3000.00 20
7934 MILLER CLERK 7782 1982-1-23 1300.00 10
1001 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的子查询范围不能是空值，否则查询结果为空这四个案例，是工作中非常容易遇到的场景，也是很容易犯错的地方，博主写出来也给自己一个提醒！
————————————————

免责申明：

本文系转载，版权归原作者所有，如若侵权请联系我们进行删除！

想了解或咨询更多有关袋鼠云大数据产品、行业解决方案、客户案例的朋友，浏览袋鼠云官网：https://www.dtstack.com/?src=bbs
同时，欢迎对大数据开源项目有兴趣的同学加入「袋鼠云开源框架钉钉技术群」，交流最新开源技术信息，群号码：30537511，项目地址：https://github.com/DTStack