【Oracle数据库】子查询

子查询概述

子查询

SELECT select_listFROM tableWHERE exPR Operator (SELECT select_list FROM table)括号内的查询叫做子查询(内部查询)，先于主查询执行子查询的结果被主查询(外部查询)使用operator包括：=、>、>=、<、<=、<>、IN、ANY、ALL子查询可以嵌于WHERE子句、HAVING子句、FROM子句中
子查询的类型
根据子查询返回的行和列的数量，子查询分为单行子查询、多行子查询和多列子查询
子查询使用指导
子查询要用括号括起来将子查询放在比较运算符的右边对于单行子查询要使用单行运算符对于多行子查询要使用多行运算符
SELECT enameFROM empWHERE sal>(SELECT sal FROM emp WHERE ename='JONES')
单行子查询
只返回一行一列的子查询称为单行子查询单行运算符：
运算符
含义
=
等于
>
大于
>=
大于等于
<
小于
<=
小于等于
<>
不等于
多行子查询
多行子查询
可能返回多条记录的子查询称为多行子查询多行运算符：IN、ANY、ALL
多行操作符：IN
判断是否与子查询的任意一个返回值相同IN的使用
-- 查询是经理的员工姓名、工资SELECT ename,salFROM empWHERE empno IN (SELECT mgr FROM emp)
多行操作符：ANY
表示和子查询的任意一行结果进行比较，有一个满足条件即可< ANY：表示小于子查询结果集中的任意一个，即小于最大值就可以> ANY：表示大于子查询结果集中的任意一个，即大于最小值就可以= ANY：表示等于子查询结果中的任意一个，即等于谁都可以，相当于INANY的使用
-- 查询是经理的员工姓名、工资SELECT ename,salFROM empWHERE empno = ANY (SELECT mgr FROM emp)						-- 查询部门编号不为10，且工资比10部门任意一名员工工资低的员工编号、姓名、职位、工资SELECT empno,ename,job,salFROM empWHERE sal < ANY (SELECT sal                 FROM emp                 WHERE deptno=10)AND deptno <> 10
多行操作符：ALL
表示和子查询的所有行结果进行比较，每一行必须都满足条件< ALL：表示小于子查询结果集中的所有行，即小于最小值> ALL：表示大于子查询结果集中的所有行，即大于最大值= ALL：表示等于子查询结果中的所有行，即等于所有值，通常无意义ALL的使用
-- 查询部门编号不为10，且工资比10部门所有员工工资低的员工编号、姓名、职位、工资SELECT empno,ename,job,salFROM empWHERE sal < ALL (SELECT sal                 FROM emp                 WHERE deptno=10)AND deptno <> 10
多列子查询
可以在一个条件表达式内同时和子查询的多个列进行比较的子查询称为多列子查询多列子查询通常使用IN操作符完成
-- 查询出和1981年入职的任意一个员工的部门和职位完全相同的员工姓名、部门、职位、入职日期，不包括1981年入职员工SELECT ename,deptno,job,hiredateFROM empWHERE (deptno,job) IN (SELECT deptno,job                       FROM emp                       WHERE TO_CHAR(hiredate,'YYYY')='1981')AND TO_CHAR(hiredate,'YYYY')<>'1981'
子查询中空值问题
子查询返回的结果中含有空值会导致主查询没有记录返回所有的条件和空值比较的结果都是空值无论什么时候只要空值有可能成为子查询结果集中的一部分，就不能使用NOT IN运算符
在FROM子句中使用子查询
-- 查询比自己部门平均工资高的员工姓名、工资、部门编号、部门平均工资SELECT ename,sal,e.deptno,avgsalFROM emp e,(SELECT deptno,AVG(sal) avgsal            FROM emp            GROUP BY deptno) sWHERE e.deptno = s.deptnoAND sal>avgsal
分页查询
ROWNUM及其特性
ROWNUM是一个伪列，伪列是使用上类似于表中的列，而实际并没有存储在表中的特殊列ROWNUM的功能是在每次查询时，返回结果集的顺序号，这个顺序号是在记录输出时逐行产生的“SELECT * FROM emp WHERE ROWNNUM>2;”为何查不到任何记录？因为ROWNUM是在记录输出时才逐行产生的，且总是从1开始，所以输出的第一条记录不满足“ROWNUM>2”的条件，被过滤掉；第二条的ROWNUM变成了1，又不满足“ROWNUM>2”的条件，又被过滤掉；以此类推，永远没有满足条件的记录，所以返回为空即查不到任何记录对于ROWNUM只能执行<、<=运算，不能执行>、>=或一个区间运算BETWEEN...AND等因为ROWNUM在记录输出时生成，而ORDER BY子句在最后执行，所以ROWNUM和ORDER BY子句一起使用时，需要注意ROWNUM实际是已经被排了序的ROWNUM
TOP-N查询
TOP-N查询主要是实现表中按照某个列排序，输出最大或最小的N条记录功能TOP-N分析语法：
SELECT [列名],ROWNUMFROM  (SELECT [列名]FROM 表名ORDER BY TOP-N操作的列 ASC|DESC)WHERE ROWNUM<=NASC：查询最小的N条记录DESC：查询最大的N条记录
分页查询
当未指定需要按照某列排序语法：
SELECT b.*FROM (SELECT ROWNUM rn,[列名1,列名2,...列名n]      FROM 表名,[表名2,...表名n]      WHERE [条件表达式 AND ]ROWNUM <= 目标页码*每页记录数) bWHERE rn > (目标页码-1)*每页记录数当未指定需要按照某列排序语法：
SELECT *FROM (SELECT ROWNUM rn,b.*      FROM (SELECT 列名1[,列名2,...列名n]            FROM 表名1[,表名2,...表名n]            [WHERE 子句]            ORDER BY 要排序的列 ASC|DESC) b      WHERE ROWNUM <= 目标页码*每页记录数)WHERE rn > (目标页码-1)*每页记录数