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