Oracle 19C入门到精通之子查询和关联子查询

在执行数据操作（包括查询、添加、修改和删除等）的过程中，如果某个操作需要依赖于另一个SELECT语句的查询结果，那么可以把SELECT语句嵌入该操作语句中，从而形成一个子查询。

1. 什么是子查询

子查询是在SQL语句内的另一条SELECT语句，也被称为内查询或内SELECT语句。在SELECT、INSERT、UPDATE或DELETE命令中允许是一个表达式的地方都可以包含子查询，子查询甚至可以包含在另一个子查询中。

--在emp表中查询部门名称为SALES的员工信息

select empno,ename,job from emp

where deptno=(select deptno from dept where dname='SALES');

在emp表中是不存在dname字段（部门名称），但emp表中存在deptno字段（部门代码）；dname字段、deptno字段存在于dept表中，所以deptno为两张表之间的关联字段。

也可以通过多表关联来实现在emp表中查询部门名称为SALES的员工信息；子查询与多表关联查询能实现一样的查询功能，子查询易读，更容易理解，但多表关联查询效率高于子查询；

select empno,ename,job from emp

join dept on emp.deptno=dept.deptno

where dept.dname = 'SALES';

在执行子查询操作的语句中，子查询也被称为内查询，包含子查询的查询语句也被称为外查询或主查询。

在一般情况下，外查询语句检索一行，子查询语句需要检索一遍数据，然后判断外查询语句的条件是否满足。如果条件满足，则外查询语句将检索到的数据行添加到结果集中；如果条件不满足，则外查询语句继续检索下一行数据。所以子查询相对多表关联查询要慢一些。

在使用子查询时，应注意以下规则：

子查询必须用括号“()”括起来。

子查询中不能包括ORDER BY子句。

子查询允许嵌套多层，但不能超过255层。

在Oracle中，通常把子查询再细化为单行子查询、多行子查询和关联子查询3种。

2. 单行子查询

单行子查询是指返回一行数据的子查询语句。当在WHERE子句中引用单行子查询时，可以使用单行比较运算符（=、>、=、

--查询emp表中既不是最高工资，也不是最低工资的员工信息

select empno,ename,sal from emp

where sal > (select min(sal) from emp)

and sal 

3. 多行子查询

多行子查询是指返回多行数据的子查询语句。当在WHERE子句中使用多行子查询时，必须使用多行比较符(IN、ANY、ALL)。

3.1. 使用IN运算符

当在多行子查询中使用IN运算符时，外查询会尝试与子查询结果中的任何一个结果进行匹配，只要有一个匹配成功，则外查询返回当前检索的记录。

--查询不是研发部门（RESEARCH）的员工信息

select empno,ename,job

from emp where deptno in

(select deptno from dept where dname'RESEARCH');

3.2. 使用ANY运算符

ANY运算符必须与单行操作符结合使用，并且返回行只要匹配子查询的任何一个结果即可。

--查询工资高于部门编号为20的任意一个员工工资的其他部门的员工信息

select deptno,ename,sal from emp where sal > any

(select sal from emp where deptno = 20) and deptno  20;

3.3. 使用ALL运算符

ALL运算符必须与单行运算符结合使用，并且返回行必须匹配所有子查询结果。

--查询工资高于部门编号为30的所有员工工资的员工信息

select deptno,ename,sal from emp where sal > all

(select sal from emp where deptno = 30);

4. 关联子查询

在单行子查询和多行子查询中，内查询和外查询是分开执行的，也就是说，内查询的执行与外查询的执行是没有关系的，外查询仅仅是使用内查询的最终结果。

在一些特殊需求的子查询中，内查询的执行需要借助外查询，而外查询的执行又离不开内查询的执行，这时，内查询和外查询是相互关联的，这种子查询就被称为关联子查询。

--使用关联子查询检索工资高于同职位的平均工资的员工信息

select empno,ename,sal

from emp f

where sal > (select avg(sal) from emp where job = f.job)

order by job;

在上述查询语句中，内层查询使用关联子查询计算每个职位的平均工资，而关联子查询必须知道职位的名称。为此，外层查询就使用f.job字段值为内层查询提供职位名称，以便于计算出某个职位的平均工资。如果外层查询正在检索的数据行的工资高于平均工资，则会对该行的员工信息进行显示；否则不显示。

注意：在执行关联子查询的过程中，必须遍历数据表中的每条记录，因此如果被遍历的数据表中有大量数据记录，则关联子查询的执行速度会比较缓慢。