Oracle子查询

子查询是指嵌入在其他SQL语句中的SELECT语句，也称为嵌套查询。（在编程时应该尽量少使用嵌套子查询的技术，因为使用表连接时，查询的性能可能会更高。）

1、子查询的类型

子查询有一下两种基本类型：

• 单行子查询：不向外部的SQL语句返回结果，或者只返回一行，单行子查询的一种特殊情况是精确包含一行，这种子查询称为标量子查询；
• 多行子查询：向外部的SQL语句返回一行或多行；

另外，子查询还有3种子类型，它们可以返回一行或多行。

• 多列子查询：向外部SQL语句返回多列；
• 关联子查询：引用外部SQL语句中的一列或多列。这种子查询之所以被称为关联子查询，是因为它们通过相同的列与外部的SQL语句相关联；
• 嵌套子查询：位于另外一个子查询中。子查询最多可以嵌套255层。

2、编写单行子查询

2.1、在WHERE子句中使用子查询

子查询可以放到另一个查询的WHERE子句中。

select column1,column2 from table_name1 where column3 = (select column3 from table_name1 where column4 = 10);

2.2、使用其他单行操作符

上面的例子在WHERE子句中使用了等于操作符（=）。在单行子查询中，也可以使用其他比较操作符，例如>、<、>=、<=、<>。

例如：

select column1,column2 from table_name1 where column3 < (select avg(column3) from table_name2);

2.3、在HAVING子句中使用子查询

HAVING子句的作用是对行组进行过滤，在外部查询的HAVING子句中也可以使用子查询，这样就可以基于子查询返回的结果对行组进行过滤。例如：

select column1,column2 from table_name1 group by column3 
having avg(column4) < (select max(avg(column4)) from table_name2 group by column3);

2.4、在FROM子句中使用子查询

当在FROM子句中使用子查询时，该子句会被作为视图对待，因此也被称为内嵌视图，当在FROM子句中使用子查询时，必须要给子查询指定别名。例如：

select emp1.column1,emp1.cloumn2 from table_name1 emp1,
(select emp2.column3 from table_name2 emp2) dept
where emp1.column3 = dept.column3;

2.3、常见查询错误

1. 单行子查询最多返回一行：如果子查询返回多行，就会出现错误。如下：

   select ename,asl,job from emp 
   where empno = (select empno from emp where deptno = 10); 
   
   (SELECT empno FROM emp
   *  )
   第2行出现错误：
   ORA-01427 :单行子查询返回多个行
   
   

    

2. 子查询不能包含ORDER BY 子句：子查询中不能包含ORDER BY子句。相反，任何排序都必须在外部查询中完成。如下：

   select empno,ename,sal from emp
   where sal > (select avg(sal) from emp order by avg(sal) desc);
   
   (select avg(sal) from emp order by avg(sal) desc)
   
   第2行出现错误：
   ORA-00907：缺失右括号

    

3、编写多行子查询

多行子查询是指返回多行数据的子查询语句。当在WHERE子句中使用多行子查询时，必须要使用多行比较符（IN、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. 在多行子查询中使用ANY操作符：ANY操作符必须与单行操作符结合使用，并且返回行只需匹配于子查询的任一个值即可。如下：

   select ename,sal,deptno from emp where sal > any 
   (select sal from emp where deptno = 20);

    

3. 在多行子查询中使用ALL操作符：ALL操作符必须与单行操作符结合使用，并且返回行必须要匹配于所有查询结果。如下：

   select ename,sal,deptno from emp where sal > all
   (select sal from emp where deptno =30);

    

4、编写多列子查询

单行子查询是指子查询只返回单列单行数据，多行子查询是指子查询返回单列多行数据。而多列子查询则是值返回多列数据的子查询语句。如下：

select deptno,ename,sal from emp
where (deptno,sal) in (select deptno,min(sal) from emp
group by deptno);

5、编写关联子查询

关联子查询会引用外部SQL语句中的一列或多列。这种子查询之所以被称为关联子查询，是因为它们通过相同的列与外部的SQL语句关联。

5.1、使用关联子查询

select empno,deptno,ename,sal from emp outer 
where sal > (select avg(sal) from emp inner where inner.deptno = outer.deptno);

5.2、在关联子查询中使用EXISTS和NOT EXISTS

EXISTS操作符用于检查子查询返回行的存在性。NOT EXISTS执行的操作在逻辑上刚好与EXISITS相反。

1. 在关联子查询中使用EXISTS：

   select ename,job,sal,deptno from emp
   where exists 
   (select deptno from dept where dept.deptno = emp.deptno and dept.loc = 'NEW');

   （由于EXISTS只是查询子查询返回的行的存在性，因此子查询不必返回一列，可以只返回一个常量值，这样可以提高查询的性能。例如：

   select ename,job,sal,deptno from emp 
   where exists
   (select 1 from dept where dept.deptno = emp.deptno and dept.loc = 'NEW');

   ）

2. 在关联子查询中使用NOT EXISTS：

   select ename,job,sal,deptno from emp
   where not exists 
   (select 1 from dept where dept.deptno = emp.deptno and dept.loc = 'NEW');

    

6、编写嵌套子查询

在子查询内部可以嵌套其他子查询，嵌套层次最多为255。例如：

select deptno,avg(sal) from emp 
group by deptno
having avg(sal) >
(select max(avg(sal)) from emp 
where deptno in 
(select deptno from dept
where deptno > 10)
group by deptno);

7、编写包含子查询的UPDATE和DELETE语句

1. 编写子查询的UPDATE语句：当在UPDATE语句中使用子查询时，即可以在WHERE子句中引用子查询（返回未知条件值），也可以在SET子句中使用子查询（修改列数据）。例如：

   update emp set (sal,comn) = 
   (select sal,comn from emp where ename = 'TOM')
   where job = 
   (select job from emp where ename = 'TOM');

    

2. 编写子查询的DELETE语句：在DELETE语句中使用子查询时，可以在WHERE子句中引用子查询返回未知条件值。例如;

   delete from emp 
   where deptno = 
   (select detpno from dept where dname = 'TOM');