Oracle专题8之Oracle的子查询

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, dname, loc, (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、主查询和子查询可以不是同一张表

主查询和子查询可以查询的不是同一张表。

示例：查询部门名称时ACCOUNTING的员工信息。

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, sal, 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, job, 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行。

示例3：查询部门名称不是‘SAVE’的员工。

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, job, sal from emp where sal = (select max(sal) from emp group by deptno);

SQL> select ename, job, sal from emp where sal = (select max(sal) from emp group by deptno);
select ename, job, 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, sal, 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, sal, 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, ename, 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, job, 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行。

网站栏目：Oracle专题8之Oracle的子查询
链接分享：http://chengdu.cdxwcx.cn/article/ghjjsd.html

甜橘子，专注成都网站制作网站设计与营销型网站建设与优化

首页

网站建设

网站制作案例

解决方案

网站设计报价

网站制作动态

关于我们

联系我们

成都网站建设设计将想法与焦点和您一起共享

Oracle专题8之Oracle的子查询

1、Oracle的子查询的概述

a、在where子句中使用子查询

b、在select子句中使用子查询

c、在having子句中使用子查询

d、在from子句中使用子查询

2、Oracle的主查询和子查询

a、一个主查询可以有多个子查询

b、子查询的执行顺序

c、相关子查询

d、主查询和子查询可以不是同一张表

3、Oracle的子查询：单行子查询

a、使用单行子查询

b、非法使用单行子查询

4、Oracle的子查询：多行子查询

a、在多行子查询中使用IN操作符

b、在多行子查询中使用ALL操作符

c、在多行子查询中使用ANY操作符

5、Oracle的子查询需要注意的问题

a、不可以在group by子句中使用子查询。

b、在TOP-N分析问题中，需对子查询排序

c、单行子查询的空值问题

d、多行子查询的空值问题

其他资讯

bootstrap框架有什么作用

Oracle12c使用SQL*Plus来创建与移动应用程序Seeds

Wex5开发之微信支付的示例分析

C#中Math.Round的作用是什么

Java继承的概念

甜橘子，专注成都网站制作网站设计与营销型网站建设与优化

成都网站建设设计 将想法与焦点和您一起共享

Oracle专题8之Oracle的子查询

1、Oracle的子查询的概述

a、在where子句中使用子查询

b、在select子句中使用子查询

c、在having子句中使用子查询

d、在from子句中使用子查询

2、Oracle的主查询和子查询

a、一个主查询可以有多个子查询

b、子查询的执行顺序

c、相关子查询

d、主查询和子查询可以不是同一张表

3、Oracle的子查询：单行子查询

a、使用单行子查询

b、非法使用单行子查询

4、Oracle的子查询：多行子查询

a、在多行子查询中使用IN操作符

b、在多行子查询中使用ALL操作符

c、在多行子查询中使用ANY操作符

5、Oracle的子查询需要注意的问题

a、不可以在group by子句中使用子查询。

b、在TOP-N分析问题中，需对子查询排序

c、单行子查询的空值问题

d、多行子查询的空值问题

其他资讯

bootstrap框架有什么作用

Oracle12c使用SQL*Plus来创建与移动应用程序Seeds

Wex5开发之微信支付的示例分析

C#中Math.Round的作用是什么

Java继承的概念

成都网站建设设计将想法与焦点和您一起共享