子查询

1、单行和多行子查询

单行子查询：只返回一行数据的子查询语句

select ename,sal,deptno from emp
where deptno=(select deptno from emp where ename='scott')
and ename<>'scott';  // 返回scott同部门的所有其他雇员信息

多行子查询：返回多行数据的子查询语句

IN操作符：处理匹配于子查询任一个值的行

ALL操作符：必须与单行操作符结合使用，匹配于所有子查询的所有结果才行

select ename,sal,deptno from emp where sal>all
(select sal from emp where deptno=40);       //统计高于部门40中所有员工工资的员工信息

ANY操作符：必须与单行操作符结合使用，返回匹配于子查询的任一个结果即可

select ename,sal,deptno from emp where sal>any
(select sal from emp where deptno=40);       //统计高于部门40中任一个员工工资的员工信息

2、多列子查询   返回多个列数据的子查询语句

多列子查询返回单行数据时，where子句可使用单行比较符（<,>,=,>=,<=,<>）

多列子查询返回多行数据时，where子句可使用多行比较符(IN,ALL,ANY)

使用子查询比较多个列数据时，可使用成对比较，也可以使用非成对比较

成对比较（要求多个列的数据必须同时匹配）

select ename,sal,comm,deptno from emp
where (sal,nvl(comm,-1)) in (select sal,nvl(comm,-1)
from emp where deptno=40)                     //显示工资及补助与部门40中员工的工资和补助完全匹配的所有雇员

非成对比较（主SQL中列值能匹配子查询中列的某一个值即可）

select ename,sal,comm,deptno from emp
where sal in (select sal from emp where deptno=40)
  or nvl(comm,-1) in
  (select nvl(comm,-1) from emp where deptno=40)     //显示工资匹配于部门40工资列表，或者补助匹配于部门40补助列表的所有雇员

 

3、相关子查询   子查询引用到主SQL中的表列时，就会执行相关子查询。

普通子查询，只会执行一次

相关子查询，每处理一行主SQL语句的数据都会执行一次相关子查询

使用场景：

select语句中使用相关子查询：

select column1,... from table1 outer where column1 operator   //operator指定比较操作符

(select column1,column2 from table2

 where expr1=outer.expr2);

 

update语句中使用相关子查询：

update table1 alias1 set column=

(select expression from table2 alias2

 where alias1.column=alias2.column);

 

delete语句中使用相关子查询：

delete from table1 alias1 where column operator

(select expression from table2 alias2

 where alias1.column=alias2.column);

 

exists操作符

检测子查询是否存在返回行，若存在则为true，否则false

select ename,sal,deptno from emp e where exists
(select 2 from dept where deptno=e.deptno and loc='NEW YORK')

 

not exists操作符

与exists完全相反

 

4、其他SQL语句中引用子查询

4.1 DDL中使用子查询

 在create table、view中使用子查询

create table new_emp as select * from  emp

4.2 在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;       //显示高于部门平均工资的雇员信息

4.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.4 使用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);    //显示部门工资总额超过所有部门平均工资总额的部门名、工资总额