Oracle子查询大全

                                                 子查询

一、单行和多行子查询
1.单行子查询
单行子查询是指返回一行数据的子查询语句。当在where子句中引用单行子查询时，可以使用单行比较符(=,<,>,<=,>=,<>)。
SELECT ename,sal,deptno FROM emp where deptno=
(SELECT deptno FROM emp where ename='SCOTT') and ename<>'SCOTT';
2.多行子查询
多行子查询是指返回多行数据的子查询语句。当在where子句中使用多行子查询时，必须使用多行符(in,all,any)。
注意，all和any操作符不能单独使用，而只能与单行比较符(=,<,>,<=,>=,<>)结合使用。
in:匹配于子查询结果的任一个值即可
all:必须符合子查询结果的所有值
any:只要符合子查询结果的任一个值即可
(1)在多行子查询中使用in操作符
在多行子查询中使用in操作符时，会处理匹配于子查询任一个值的行。
SELECT ename,job,sal,deptno FROM emp where job in
(SELECT distinct job FROM emp where deptno=10);
(2)在多行子查询中使用all操作符
all操作符必须与单行操作符结合使用，并且返回行必须匹配于所有子查询结果。
SELECT ename,sal,deptno FROM emp where sal>all
(SELECT sal FROM emp where deptno=30);
(3)在多行子查询中使用any操作符
any操作符必须与单行操作符结合使用，并且返回行只要匹配子查询的任一个结果即可
SELECT ename,sal,deptno FROM emp where sal>any
(SELECT sal FROM emp where deptno=30);
二、多列子查询
多列子查询是指返回多个列数据的子查询语句。当多列子查询返回单行数据时，在where子句中可以使用单行比较符;
当多例子查询返回多行数据时，在where子句中必须使用多行比较符(in,any,all)。
在进行子查询之前需要将scott用户中自带的emp表修改一下，以便更好的说明成对比较和费成对比较之间的区别
UPDATE emp SET sal=1500,comm=300 where ename='CLARK';
UPDATE emp SET sal=1600,comm=300 where ename='SCOTT';
1.成对比较示例
当执行成对比较时，以为要求多个列的数据必须同时匹配，所以必须使用多列子查询实现。
SELECT ename,sal,comm,deptno FROM emp
where (sal,nvl(comm,-1)) in (SELECT sal,nvl(comm,-1)
FROM emp where deptno=30);
2.非成对比较示例
当使用非成对比较时，应该使用多个多行子查询实现。
SELECT ename,sal,comm,deptno FROM emp
where sal in(SELECT sal FROM emp where deptno=30)
and nvl(comm,-1) in (SELECT nvl(comm,-1) FROM emp where deptno=30);
三、相关子查询
当子查询引用到主SQL语句的表列时，Oracle会执行相关子查询，其中主SQL语句可能是select,update或者delete语句。
对于普通子查询来说，子查询只会执行一次;而对于相关子查询来说，每处理一行主SQL语句的数据都会执行一次相关子查询。
1.在SELECT语句中使用相关子查询
为了在读取每行数据时与关联表数据进行比较，可以在SELECT语句中使用相关子查询。
SELECT ename,sal,deptno FROM emp outer where sal>
(SELECT avg(sal) FROM emp where deptno=outer.deptno);
2.在UPDATE语句中使用相关子查询
当基于其他表的相关数据更新当前表数据时，可以在UPDATE语句中使用相关子查询。
ALTER TABLE emp ADD dept_name varchar2(20);
UPDATE emp e SET dept_name=
(SELECT dname FROM dept d where d.deptno=e.deptno);
3.在DELETE语句中使用相关子查询
当基于其他表的相关数据删除当前表的数据时，可以在DELETE语句中使用相关子查询
DELETE FROM emp e where empno=
(SELECT empno FROM emp_copy where empno=e.empno);
4.使用EXISTS操作符
EXISTS操作符用于检测子查询是否存在返回行，存在则返回TRUE，不存在则返回FALSE。
SELECT ename,sal,deptno FROM emp e where exists
(SELECT 1 FROM dept where deptno=e.deptno and loc='NEW YORK');
5.使用NOT EXISTS操作符
NOT EXISTS操作符用于检测子查询是否存在返回行。如果子查询返回行，则条件值为FALSE，不返回则为TRUE。
SELECT ename,sal,deptno FROM emp e where NOT EXISTS
(SELECT 1 FROM dept where deptno=e.deptno and loc='NEW YORK');
四、在其他SQL语句中引用子查询
1.在DDL语句中使用子查询
①在CREATE TABLE语句中使用子查询
通过在CREATE TABLE语句中使用子查询，可以在建立新表的同时复制表数据。
CREATE TABLE new_emp(id,name,sal,job,deptno) AS
SELECT empno,ename,sal,job,deptno FROM emp;
②在CREATE VIEW 语句中使用子查询
当建立视图时，必须指定视图所对应的子查询语句。
CREATE OR REPLACE VIEW dept_10 AS
SELECT empno,ename,job,sal,deptno FROM emp
where deptno=10 order by empno;
③在CREATE MATERIALIZED VIEW 语句中使用子查询
当建立实体化视图时，必须指定实体化视图所对应的查询语句。
CREATE MATERIALIZED VIEW summary_emp AS
SELECT deptno,job,avg(sal) avgsal,sum(sal) sumsal
FROM emp group by cube(deptno,job);
2.在FROM子句中使用子查询
当在FROM子句中使用子查询时，该子查询会被作为视图对待，因此也称为内嵌视图。
注意：当在FROM子句中使用子查询时，必须给子查询指定别名。
SELECT ename,job,sal FROM emp,
(SELECT deptno,avg(sal) avgsal FROM emp
GROUP BY deptno) dept
where emp.deptno=dept.deptno and sal>dept.avgsal;
3.标量子查询表达式
标量子查询是指每行只返回一个列值的子查询，标量子查询的表达式的值是子查询选择列表项的值。
如果子查询返回0行，则标量子查询表达式的值为NULL；如果子查询返回超过1行，则会显示错误信息。
标量子查询适用于以下情况：
DECODE和CASE的条件部分和表达式部分。
除GROUP BY子句之外的所有SELECT其他字句。
在UPDATE语句的SET和WHERE子句中比较操作符的左边。
SELECT ename,sal,deptno FROM emp e order by
(SELECT dname FROM dept d where e.deptno=d.deptno);
4.使用with子句重用子查询
如果在复杂查询中多次引用相同的查询块，那么可以考虑使用WITH子句。
通过使用WITH子句，可以为子查询定义一个名称，然后可以在主语中多次引用该子查询。
WITH
dept_sum AS(
SELECT d.dname,sum(e.sal) total FROM dept d,emp e
WHERE d.deptno=e.deptno GROUP BY d.dname),
dept_avg_sum AS(
SELECT sum(total)/count(*) avg_sum FROM dept_sum)
SELECT dname,total FROM dept_sum
WHERE total>(SELECT avg_sum FROM dept_avg_sum);