概述
有这样一个需求,我们需要从ORACLE中查询根据EMPNO查询出存在表EMP表而不存在于DEPT部门表中的数据,如下
emp表:
dept表:
那么它的结果应该是,查询出ID 为1和2的两条数据
完成这个需求的方案有很多种,我们一个一个看
基于MINUS取差集的解决方案
我们知道,ORACLE中表A MINUS 表B 是指指筛选出存在于 表A 而不存在于 表B的数据,
那么
select EMPNO from emp
minus
select EMPNO from dept
就可以筛选出存在于表emp 而不存在于表dept 种的EMPNO了,再使用一个IN就可以查出所有的数据了
完整SQL如下:
SELECT *
FROM emp
WHERE EMPNO IN(
select EMPNO from emp
minus
select EMPNO from dept
)
结果如下,下面两种方案也是一样的输出
基于NOT IN的解决方案
我们知道,
field IN (‘1’,‘2’,‘3’) 是指筛选出存在于查询范围内的数据,
field NOT IN (‘1’,‘2’,‘3’)是筛选出不存在于查询范围内的数据,
当查询范围是一个子查询的时候则,数据库首先会从内层查询中得到查询范围,外层查询再从表中筛选存在或不存在于查询范围内的数据
完整SQL如下:
SELECT * FROM EMP e
WHERE EMPNO NOT IN
(
SELECT EMPNO FROM dept
)
但是需要注意一点的是在 SQL 中,使用子查询,如果内层查询中的值有NULL,而外层查询使用了使用了 谓词IN、NOT IN、OR运算符的时候,查询出的结果将会为Null,即使在外层循环中有内层循环的数据,也会返回NULL
比如
SELECT * FROM EMP e
WHERE EMPNO NOT IN
(
SELECT EMPNO FROM dept
)
假设内层查询结果为
SELECT * FROM EMP e
WHERE EMPNO NOT IN('1','2',null)
由于混入了null,那么查询结果仍会为null,SQL就是这样规定的,不过不用太担心,这个问题是可以解决的,一种是对NULL值进行处理,另一种是不用IN、NOT IN、OR运算符就好了
两种方案我们都给出吧,首先对NULL值进行处理的
SELECT * FROM EMP e
WHERE EMPNO NOT IN
(
SELECT CASE WHEN EMPNO IS NULL THEN '0' ELSE EMPNO END AS EMPNO
FROM dept
)
使用CASE WHEN进行处理的,EMPNO字段为NULL时,返回EMPNO不可能存在的值,这样在外层循环就不会匹配到这个值,那么SQL也能返回正确的数据
另一种是使用EXISTS、NOT EXISTS在逻辑上代替IN、NOT IN,也能完成IN和NOT IN的功能
如下
基于NOT EXISTS取差集的解决方案
我们知道用NOT EXISTS, 表A NOT EXISTS 表B 指查找存在于表A而不存在于表B中的数据
完整SQL如下
SELECT * FROM EMP e
WHERE NOT EXISTS
(
SELECT EMPNO FROM dept d
WHERE e.EMPNO = d.EMPNO
)
对于NOT EXISTS和EXISTS的使用,一般是需要在内层查询中使用连接查询,外层查询中使用NOT EXISTS或EXISTS
为啥呐?因为SQL的执行过程是,首先从外层查询取一条数据和内层查询的所有数据匹配,如果内层查询匹配到了,那么,EXISTS返回 TRUE,NOT EXISTS 返回 FALSE,那么当外层查询使用EXIST,内层查询返回TRUE时,外层查询就就保存这一行,到最后的时候统一输出。NOT EXISTS也是一样的,外层查询使用NOT EXISTS,内层查询返回TRUE时,外层查询就就保存这一行,最后的时候统一输出查询结果集
基于LEFT JOIN的解决方案
我们知道使用左连接,或者右连接,当右表没有左表的数据时,会把右表相关行数据置为空,左表没有右表的数据时,也把左表相关行的数据置为空,那么,我们使用emp 表 左连接 dept 表,那么两表中empno相等的数据,如下
SELECT e.*,d.*
FROM emp e LEFT JOIN dept d ON e.EMPNO = d.EMPNO
我们看到在查询结果中,右表(dept表)没有左表的数据置为了空,那么在加个WHERE d.EMPNO IS NULL条件,不就可以把两表中都存在的数据都去掉了嘛?这样也就只剩下了左表的数据了,完美。
sql如下
SELECT e.*,d.*
FROM emp e LEFT JOIN dept d ON e.EMPNO = d.EMPNO
WHERE d.EMPNO IS NULL
完美,不要的行,根据需要在SELECT中去除就可以了
那么使用RIGHT JOIN是否可以达到同样的目的呐?
SELECT e.*,d.*
FROM emp e RIGHT JOIN dept d ON e.EMPNO = d.EMPNO
我们可以看到,使用RIGHT JOIN的时候,我们以右表为主,左表没有右表的数据时,也把左表相关行的数据置为空,因为ID为1,2的数据在右表中没有,因此就把这两行数据置空了,这样无论如何都找不到存在于emp表而不存在于dept表中的数据了
为此,我们应该根据业务需求去选择左连接或者右连接,选择能完成需求的SQL语句,如果能简单一点的话就更好了
补充一点吧,
COALESCE函数置0,但是报了个错,说ORA-00932: 数据类型不一致: 应为 CHAR, 但却获得 NUMBER,那么可能是COALESCE只能将数据类型为NUMBER的列置为0吧,不同同时我们也知道,COALESCE可以用数据类型为NUMBER的列上,并当它为NULL时置为0
还有一点是,
IN 和 NOT IN 本质上是 OR 运算,where deptno in ( 10,50,null ),相当于,where (deptno=10 or deptno=50 or deptno=null)