1、Oracle的子查询的概述
- 什么是子查询？
- 子查询是指嵌入在其他sql语句中的SELECT语句，也称之为嵌套查询。
- 可以使用子查询的位置：where、select列表、having、from。
a、在where子句中使用子查询
- 示例：查询出和FORD是相同职位的员工：select * from emp where job = (select job from emp where ename='FORD');
- sql> select * from emp where job = (select job from emp where ename='FORD');
- EMPNO ENAME JOB MGR HIREDATE SAL COMM
- DEPTNO
- 7951 EASON ANALYST 7566 01-12月-17 3000
- 20
- 7788 SCOTT ANALYST 7566 19-4月 -87 3000
- 20
- 7902 FORD ANALYST 7566 03-12月-81 3000
- 20
b、在select子句中使用子查询
- 示例：查询出每个部门的编号、名称、位置和部门人数。示例：select deptno,dname,loc,(select count(empno) from emp where emp.deptno = dept.deptno) count from dept;
- sql> select deptno,(select count(empno) from emp where emp.deptno = dept.deptno) count from dept;
- DEPTNO DNAME LOC COUNT
- 10 ACCOUNTING NEW YORK 3
- 20 RESEARCH DALLAS 6
- 30 SALES CHICAGO 6
- 40 OPERATIONS BOSTON 0
c、在having子句中使用子查询
- 举例：查询员工信息表，按照部门编号进行分组，要求显示员工的部门编号、平均工资，查询条件时平均工资大于30号部门的最高工资。
- sql> select deptno,avg(sal) from emp group by deptno having avg(sal) > (select max(sal) from emp where deptno = 30);
- DEPTNO AVG(SAL)
- 10 2916.66667
d、在from子句中使用子查询
- 把子查询的结果看成一张新的表。示例：查询并显示高于部门平均工资的雇员信息。
- sql> select ename,job,sal from emp,(select deptno,avg(sal) avgsal from emp group by deptno) dept where emp.deptno = dept.deptno and sal > avgsal;
- ENAME JOB SAL
- EASON ANALYST 3000
- ALLEN SALESMAN 1600
- JONES MANAGER 2975
- BLAKE MANAGER 2850
- SCOTT ANALYST 3000
- KING PRESIDENT 5000
- FORD ANALYST 3000
- 已选择7行。
2、Oracle的主查询和子查询
a、一个主查询可以有多个子查询
- 举例：显示职位和7521的职位相同并工资大于7934这个员工工资的员工信息。
- sql> select * from emp where job = (select job from emp where empno = 7521) and sal > (select sal from emp where empno = 7934);
- EMPNO ENAME JOB MGR HIREDATE SAL COMM
- DEPTNO
- 7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300
- 30
- 7844 TURNER SALESMAN 7698 08-9月 -81 1500 0
- 30
b、子查询的执行顺序
- 一般先执行子查询，再执行主查询，但相关子查询例外。
- 举例：查询员工表中小于平均工资的员工信息。
- sql> select empno,ename,sal from emp where sal < (select avg(sal) from emp);
- EMPNO ENAME SAL
- 7369 G_EASON 800
- 7499 ALLEN 1600
- 7521 WARD 1250
- 7654 MARTIN 1250
- 7844 TURNER 1500
- 7876 ADAMS 1100
- 7900 JAMES 950
- 7934 MILLER 1300
- 已选择8行。
c、相关子查询
- 当子查询需要引用主查询的表列时，Oracle会执行相关查询。
- 相关子查询是先执行主查询，在执行子查询。
- 示例：查询工资高于部门平均工资的雇员名、工资和部门号：
- sql> select ename,sal,deptno from emp e where sal > (select avg(sal) from emp where deptno = e.deptno);
- ENAME SAL DEPTNO
- EASON 3000 20
- ALLEN 1600 30
- JONES 2975 20
- BLAKE 2850 30
- SCOTT 3000 20
- KING 5000 10
- FORD 3000 20
- 已选择7行。
d、主查询和子查询可以不是同一张表
- sql> select * from emp where deptno = (select deptno from dept where dname='ACCOUNTING');
- EMPNO ENAME JOB MGR HIREDATE SAL COMM
- DEPTNO
- 7782 CLARK MANAGER 7839 09-6月 -81 2450
- 10
- 7839 KING PRESIDENT 17-11月-81 5000
- 10
- 7934 MILLER CLERK 7782 23-1月 -82 1300
- 10
3、Oracle的子查询：单行子查询
- 子查询的类型：单行子查询和多行子查询。
- 单行子查询：只返回一行数据的子查询语句。
- 使用单行比较操作符：
| 操作符 |
含义 |
| = |
等于 |
| > |
大于 |
| >= |
大于等于 |
| < |
小于 |
| <= |
小于等于 |
| <> |
不等于 |
a、使用单行子查询
- 示例1：显示与JAMES同部门的所有其他的员工姓名、工资、部门号。
- sql> select ename,deptno from emp where deptno = (select deptno from emp where ename='JAMES') AND ename <> 'JAMES';
- ENAME SAL DEPTNO-
- ALLEN 1600 30
- WARD 1250 30
- MARTIN 1250 30
- BLAKE 2850 30
- 示例2：查询大于等于公司平均工资的员工的姓名、职位、工资。
- sql> select ename,sal from emp where sal >= (select avg(sal) from emp);
- ENAME JOB SAL
- EASON ANALYST 3000
- JONES MANAGER 2975
- BLAKE MANAGER 2850
- CLARK MANAGER 2450
- SCOTT ANALYST 3000
- KING PRESIDENT 5000
- FORD ANALYST 3000
- 已选择7行。
- sql> select * from emp where deptno <> (select deptno from dept where dname= 'SALES');
- EMPNO ENAME JOB MGR HIREDATE SAL COMM
- DEPTNO
- 7951 EASON ANALYST 7566 01-12月-17 3000
- 20
- ......
- 7902 FORD ANALYST 7566 03-12月-81 3000
- 20
- 7934 MILLER CLERK 7782 23-1月 -82 1300
- 10
- 已选择9行。
b、非法使用单行子查询
- 示例：select ename,sal from emp where sal = (select max(sal) from emp group by deptno);
- sql> select ename,sal from emp where sal = (select max(sal) from emp group by deptno);
- select ename,sal from emp where sal = (select max(sal) from emp group by deptno)
- 第 1 行出现错误:
- ORA-01427: 单行子查询返回多个行
- select max(sal) from emp group by deptno;sql语句返回多行数据。
- sql> select max(sal) from emp group by deptno;
- MAX(SAL)
- 2850
- 3000
- 5000
4、Oracle的子查询：多行子查询
- 多行子查询是指返回多行数据的子查询语句。
- 使用多行比较操作符：（使用多行子查询时必须使用多行比较操作符。）
| 运算符 |
含义 |
| IN |
等于列表中的任何一个 |
| ALL |
和子查询返回的所有值进行比较 |
| ANY |
和子查询返回的任一值进行比较 |
a、在多行子查询中使用IN操作符
- 示例：查询工作地点在NEW YORK和CHICAGO的部门所对应的员工信息。
- sql> select * from emp where deptno in (select deptno from dept where loc='NEW YORK' or loc = 'CHICAGO');
- EMPNO ENAME JOB MGR HIREDATE SAL COMM
- DEPTNO
- 7934 MILLER CLERK 7782 23-1月 -82 1300
- 10
- ......
- 7698 BLAKE MANAGER 7839 01-5月 -81 2850
- 30
- 7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400
- 30
- 已选择9行。
b、在多行子查询中使用ALL操作符
- 示例：查询高于30号部门所有员工工资的员工名、工资和部门号。
- sql> select ename,deptno from emp where sal > all(select sal from emp where deptno = 30);
- ENAME SAL DEPTNO
- JONES 2975 20
- EASON 3000 20
- FORD 3000 20
- SCOTT 3000 20
- KING 5000 10
c、在多行子查询中使用ANY操作符
- 示例：查询高于10号部门任意一个员工工资的员工名、工资和部门号。
- sql> select ename,deptno from emp where sal > any (select sal from emp where deptno = 10);
- ENAME SAL DEPTNO
- KING 5000 10
- EASON 3000 20
- ......
- ALLEN 1600 30
- TURNER 1500 30
- 已选择9行。
- ```
- # 5、Oracle的子查询需要注意的问题
- ## a、不可以在group by子句中使用子查询。
- - 示例：select avg(sal) from emp group by (select deptno from emp);
- sql> select avg(sal) from emp group by (select deptno from emp);
- select avg(sal) from emp group by (select deptno from emp)
- 第 1 行出现错误:
- ORA-22818: 这里不允许出现子查询表达式
- ## b、在TOP-N分析问题中，需对子查询排序
- - 示例：显示员工信息表中工资最高的前五名员工。
- sql> select rownum,empno,sal from (select * from emp order by sal desc) where rownum <= 5;
- ROWNUM EMPNO ENAME SAL
- 1 7839 KING 5000
- 2 7951 EASON 3000
- 3 7902 FORD 3000
- 4 7788 SCOTT 3000
- 5 7566 JONES 2975
- ## c、单行子查询的空值问题
- - 示例：select ename,from emp where job = (select job from emp wehre ename = 'Ruby');
- sql> select ename,job from emp where job = (select job from emp where ename = 'Ruby');
- 未选定行
- ```
- 如果子查询返回到的是一个空值，那么主查询将不会查询到任何结果。
d、多行子查询的空值问题
- 示例：select * from emp where empno not in (select mgr from emp);
- sql> select mgr from emp;
- MGR
- 7566
- null
- ......
- 7839
- 7566
- 7698
- 已选择15行。
- sql> select * from emp where empno not in (select mgr from emp);
- 未选定行
- sql> select * from emp where empno not in (select mgr from emp where mgr is not null);
- EMPNO ENAME JOB MGR HIREDATE SAL COMM
- DEPTNO
- 7844 TURNER SALESMAN 7698 08-9月 -81 1500 0
- 30
- 7951 EASON ANALYST 7566 01-12月-17 3000
- 20
- ......
- 7934 MILLER CLERK 7782 23-1月 -82 1300
- 10
- 7900 JAMES CLERK 7698 03-12月-81 950
- 30
- 已选择9行。
