前言
本篇文章讲解的主要内容是：如果有重复数据如何检查出两个表中的差异数据及对应条数、表连接做聚合容易出现重复计算的错误、多表查询空值处理问题、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 != 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的子查询范围不能是空值，否则查询结果为空这四个案例，是工作中非常容易遇到的场景，也是很容易犯错的地方，博主写出来也给自己一个提醒！
