ORACLE 子查询的写法

概述

在项目中，一些复杂的查询都会用到子查询来查数据，对于刚开始起步写复杂SQL的话，可能会有点力不从心，如果子查询在嵌套NOT EXISTS的话，可能会觉得有点力不从心，那么根据一个案例来了解一下子查询的大体写法把

demo

表中数据

创建视图

create view V
as
select * from emp where deptno != 3
 union all
select * from emp where ename = 'WARD'

select * from V

视图中数据

我们可以看到少了EMPNO为3的这一条数据，那么我们的需求就是，找出存在于表而不存在于视图中的数据，怎么做呐？

那么一个可供参考的思考是从视图和emp表中，找出每一行的数据，通过连接查询找出相同行的数据，再通过NOT EXISTS就可以找出与连接查询结果不匹配的数据了

首先对视图计算出每一行数据出现的次数，那么也就是需要将每个字段名都查询出来，用GROUP进行分组

如下:

select 
v.empno,v.ename,v.job,v.mgr,v.hiredate,v.sal,v.comm,v.deptno,count(*) as cnt
from v
group by empno,ename,job,mgr,hiredate,sal,comm,deptno

再则，我们WHERE比较每一行的数据及其出现的次数，因为我们要对子查询出来的数据进行操作，为此应该将子查询嵌套在FROM中，在外层拼接SELECT和WHERE，

select v.empno,v.ename,v.job,v.mgr,v.hiredate,v.sal,v.comm,v.deptno

from (

select v.empno,v.ename,v.job,v.mgr,v.hiredate,v.sal,v.comm,v.deptno, count(*) as cnt
from v
group by empno,ename,job,mgr,hiredate,sal,comm,deptno
       ) v	

我们知道子查询是指从一个SELECT FROM语句嵌套在另一个SELECT FROM语句中，也就是说我们把内层嵌套查询看成是一张返回了数据的表，而你需要做的是，根据需要把它放在外层查询的SELECT FROM WHERE中

如果要展示就放在SELECT中，如果要再操作的话则放在FROM中，如果返回的数据需要用来过滤外层查询的数据的话，就放在WHERE中

那么也就是说，内层子查询的SELECT必须放在外层SELECT中，如下

SELECT * FROM 
(SELECT * FROM b )v
WHERE

这也就是写子查询时候的基本结构了，按着这个套就行，一般逻辑正确都会返回正确结果的

那么如何比较每一行的数据及其出现的次数呐，我们的话是需要一行一行的去比较emp表和视图的数据，那么，也是需要将每个字段名都查询出来，用GROUP进行分组，然后才能进行一行一行的比较数据，如下

select e.empno,e.ename,e.job,e.mgr,e.hiredate,e.sal,e.comm,e.deptno,count(*) as cnt
from emp e
group by empno,ename,job,mgr,hiredate,sal,comm,deptno

那么该如何找出存在于表而不存在于视图中的数据呐？我们知道在ORACLE中 表1 EXIST 表2，表示 存在于表1也存在于表2中的数据，而表1 NOT EXIST 表2，表示 存在于表1而不存在于表2的数据，这样分析那么外层循环的结构也就出来了

select *
from (
		内层查询
) e   	 	 
where not exists ()

not exists是与一张表进行比较，为此需要用括号括起来，而一行一行数据比较指的是，每个字段进行比较，那么在内层查询，写上需要比较的字段就可以了

select v.empno,v.ename,v.job,v.mgr,v.hiredate,v.sal,v.comm,v.deptno

from (
select v.empno,v.ename,v.job,v.mgr,v.hiredate,v.sal,v.comm,v.deptno, count(*) as cnt
from v
group by empno,ename,job,mgr,hiredate,sal,comm,deptno
       ) v
			 
 where v.empno    = e.empno
   and v.ename    = e.ename
   and v.job      = e.job
   and v.mgr      = e.mgr
   and v.hiredate = e.hiredate
   and v.sal      = e.sal
   and v.deptno   = e.deptno
   and v.cnt      = e.cnt
   and coalesce(v.comm,0) = coalesce(e.comm,0)	 

它指的是，从外层查询取一条数据出来，与内层循环的每一条进行比较，找出哪些行是相同的，而NOT EXISTS则筛选出与连接查询结果不匹配的行

select *
	from (
select e.empno,e.ename,e.job,e.mgr,e.hiredate,
       e.sal,e.comm,e.deptno, count(*) as cnt
  from emp e
 group by empno,ename,job,mgr,hiredate,
          sal,comm,deptno
      ) e
   
			 	 
where not exists (
	
select v.empno,v.ename,v.job,v.mgr,v.hiredate,v.sal,v.comm,v.deptno

from (
select v.empno,v.ename,v.job,v.mgr,v.hiredate,v.sal,v.comm,v.deptno, count(*) as cnt
from v
group by empno,ename,job,mgr,hiredate,sal,comm,deptno
       ) v
			 
 where v.empno    = e.empno
   and v.ename    = e.ename
   and v.job      = e.job
   and v.mgr      = e.mgr
   and v.hiredate = e.hiredate
   and v.sal      = e.sal
   and v.deptno   = e.deptno
   and v.cnt      = e.cnt
   and coalesce(v.comm,0) = coalesce(e.comm,0)	 

)

建表SQL

DROP TABLE "EMP1";
CREATE TABLE "EMP1" (
  "EMPNO" VARCHAR2(255 BYTE) VISIBLE NOT NULL,
  "ENAME" VARCHAR2(255 BYTE) VISIBLE,
  "JOB" VARCHAR2(255 BYTE) VISIBLE,
  "SAL" NUMBER(7,0) VISIBLE,
  "MGR" VARCHAR2(255 BYTE) VISIBLE,
  "HIREDATE" DATE VISIBLE,
  "COMM" NUMBER(7,0) VISIBLE,
  "DEPTNO" VARCHAR2(255 BYTE) VISIBLE
)
LOGGING
NOCOMPRESS
PCTFREE 10
INITRANS 1
STORAGE (
  INITIAL 65536 
  NEXT 1048576 
  MINEXTENTS 1
  MAXEXTENTS 2147483645
  BUFFER_POOL DEFAULT
)
PARALLEL 1
NOCACHE
DISABLE ROW MOVEMENT;