子查询其实就是指嵌入到其他语句中的select语句，也称其为嵌套查询。
值得注意的在DDL语句中应用子查询的时候子查询可以使用order by 子句。
但是在DML语句中的where子句，set子句中，子查询是不能使用order by子句的。
简单例子：查询工作和SMITH一样的所有员工的信息(不包含SMITH)
select ename ,sal from emp e where job =
(select job from emp where ename = 'SMITH')
and e.ename <> 'SMITH';
子查询分类
单行子查询
只返回一条记录的子查询 ，就可上述例子一样
--返回工资比SMITH多的员工和工资情况
select ename ,sal from emp where
sal>
(
select sal from emp where ename ='SMITH'
) ;
能够用于当行子查询的操作符有>、<、=、like等等
多行子查询
多行子查询返回的是一个记录组，也就是多条记录，这种情况对于子查询的操作就
应该是in、any、all，exits 等。直接一个实例来看
--查询各部门中员工工资最高的员工信息
select ename ,deptno,sal,job from emp e
where not exists
(
select ename,deptno,job from emp
where deptno = e.deptno
and
sal > e.sal
)
order by deptno
;
多行子查询的一些操作符的使用示例：
--in操作符的使用
--查询员工的职业类型在部门10中有的员工的信息
select ename,job,sal,deptno from emp where job
in
(
select distinct job from emp where deptno =10
)
;
ALL操作符：
--ALL操作符的使用
--查询员工的工资大于30号部门的所有员工的信息
select ename,sal,deptno from emp
where sal > all
(
select sal from emp where deptno=30
)
;
--">all"表示大于最大的，"
ANY操作符：
--ANY操作符的使用
--只要员工的工资存在这种情况：
--即他的工资情况比30部门的其中一个人的工资多就查询出来
select ename,sal,deptno from emp
where sal>any
(
select sal from emp where deptno = 30
)
;
“>ANY”意味着大于最小值，"
多列子查询
指的是子查询语句返回的是多列。
例如查询：某个名叫SMITH的员工，但是由于员工的名字可能出现重复，因此需要结合其工作和部门id进行判断。
--多列子查询实例
select ename ,job ,deptno from emp
where (ename,job,deptno) =
(
select ename, job,deptno from emp
where ename='SMITH'
)
;--这里主要在于说明多列子查询的使用，并不注重其实际意义
● 成对比较
--成对比较
select ename ,sal,comm ,deptno from emp
where (sal,nvl(comm,-1)) in
(
select sal,nvl(comm,-1) from emp
where deptno = 30
);
● 非成对比较
--非成对比较
elect ename ,sal,deptno,comm 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
);
其他子查询
1、相关子查询
是指需要引用主查询表列的子查询 语句。相关子查询是通过exists 来实现的 。
--查询在NEW YORK工作的员工的信息
select ename ,job ,sal ,deptno from emp
where exists
(
select 1 from dept
where dept.deptno = emp.deptno
and dept.loc ='NEW YORK'
);
--相关子查询的执行过程：
相关子查询会引用外部查询的一列或多列，在执行的时候外部查询的每一行被一次一行的
传递给子查询，子查询依次读取外部查询传递过来的每一个值，并将其用到子查询上，直到外部
查询的所有行都用完为止，然后返回查询结果。
2、FROM子句中的子查询
在from子句中使用子查询的时候子查询会被当做一个view对待，需要给子查询指定别名。
--查看比本部门平均工资高的员工信息
select ename,job,sal from emp ,
(
select deptno,avg(sal) as avgsal from emp
group by deptno
) tmp_dept
where emp.deptno=tmp_dept.deptno and sal >tmp_dept.avgsal;
3、DML语句中使用子查询
--DML语句中使用子查询
--insert中
insert into employee (id,name,title,salary)
select emptno，ename ,job,sal from emp;
--update中
update emp set (sal,comm)=
(
select sal,comm from emp
where ename =SMITH ) where job = ( select job from emp where ename =SMITH ) ; --去除销售部门 delete from emp where deptno = ( select deptno from dept where dname =SALES ) ; --DML语句中使用子
where ename ='SMITH'
)
where job =
(
select job from emp where ename ='SMITH'
)
;
--去除销售部门
delete from emp where deptno =
(
select deptno from dept
where dname ='SALES'
);
4、在DDL语句中使用子查询
通过select子查询来实现创建视图的方式来说明其使用方法
需要注意的是在执行下面的sql语句之前，要对scott用户赋予创建视图的权利
SQL>conn / as sysdba
SQL>grant create view to scott;
--创建视图并查询视图
create or replace view dept10 as
select empno,ename ,job,sal,deptno from emp
where deptno = 10 order by empno;
select * from dept10;
