SQL—子查询

子查询

时间：20190228
作者：chj

子查询语法

• 子查询（内查询）在主查询（外查询）之前执行。
• 主查询使用子查询结果。
• 子查询是嵌入在另一个SELECT语句子句中的SELECT语句。可以使用子查询从简单语句构建功能强大的语句。当需要从表中选择具有依赖于表本身的数据的条件的行时，它们可能非常有用。

使用子查询注意事项

• 子查询要包还在括号内
• 将子查询放在比较条件的右侧增强可读性 （ 子查询可以出现在比较运算符的两侧）
• 单行操作符对应单行子查询，多行操作符对应多行子查询

WHERE 子句中使用子查询

例：查询公司内，比“SCOTT”工资高的员工信息

select ename,sal
from emp
where sal>(select sal from emp
where ename='SCOTT');

ENAME             SAL
---------- ----------
KING             5000

FROM 子句中使用子查询

例：查看员工姓名、工资、部门编号和部门平均工资

select a.ename,a.sal,a.deptno,b.salavg
from emp a,
(select deptno,avg(sal) salavg
from emp
group by deptno) b
where a.deptno = b.deptno;

ENAME             SAL     DEPTNO     SALAVG
---------- ---------- ---------- ----------
SMITH             800         20       2175
ALLEN            1600         30 1566.66667
WARD             1250         30 1566.66667
JONES            2975         20       2175
MARTIN           1250         30 1566.66667
BLAKE            2850         30 1566.66667
CLARK            2450         10 2916.66667
SCOTT            3000         20       2175
KING             5000         10 2916.66667
TURNER           1500         30 1566.66667
ADAMS            1100         20       2175
JAMES             950         30 1566.66667
FORD             3000         20       2175
MILLER           1300         10 2916.66667

SELECT 子查询

select e.empno,e.ename,e.deptno, 
 (select d.dname from dept d where e.deptno = d.deptno) 
 from emp e;

单行子查询

• 只返回一行
• 使用单行比较操作符

操作符	含义
=	等于
>	大于
>=	大于等于
<	小于
<=	小于等于
<>	不等于

子查询中的空值问题

例：查询公司不是领导的员工
错误写法：因为mgr内有空值

select distinct e.ename,e.job
from emp e,emp m
where e.empno not in (select mgr from emp);

no rows selected

正确写法：

select distinct e.ename,e.job
from emp e,emp m
where e.empno not in (select nvl(mgr,0) from emp);

ENAME      JOB
---------- ---------
WARD       SALESMAN
SMITH      CLERK
TURNER     SALESMAN
JAMES      CLERK
MARTIN     SALESMAN
ADAMS      CLERK
ALLEN      SALESMAN
MILLER     CLERK

多行子查询

• 返回多行
• 使用多行比较操作符

操作符	含义
in	等于列表中的某一个值
any	与列表中的任意值比较
all	与列表中的所有值相比较

any：使用方法
例：查询出员工工资比任意一个部门的平均工资高的员工信息

select ename,sal,deptno
from emp
where sal > any (select avg(sal) from emp
group by deptno);

ENAME             SAL     DEPTNO
---------- ---------- ----------
KING             5000         10
FORD             3000         20
SCOTT            3000         20
JONES            2975         20
BLAKE            2850         30
CLARK            2450         10
ALLEN            1600         30

all：使用方法
例：查询出员工工资比所有部门的平均工资高的员工信息

select ename,sal,deptno
from emp
where sal > all (select avg(sal) from emp
group by deptno);

ENAME             SAL     DEPTNO
---------- ---------- ----------
JONES            2975         20
SCOTT            3000         20
FORD             3000         20
KING             5000         10

结果集分页

• rownum 只能用“<”,不能使用“>”和"="。
  例：
  查看全部信息

select rownum,a.*
from (select rownum rn,ename,sal
from emp) a;

    ROWNUM         RN ENAME             SAL
---------- ---------- ---------- ----------
         1          1 SMITH             800
         2          2 ALLEN            1600
         3          3 WARD             1250
         4          4 JONES            2975
         5          5 MARTIN           1250
         6          6 BLAKE            2850
         7          7 CLARK            2450
         8          8 SCOTT            3000
         9          9 KING             5000
        10         10 TURNER           1500
        11         11 ADAMS            1100
        12         12 JAMES             950
        13         13 FORD             3000
        14         14 MILLER           1300

只看前5行

select rownum,a.*
from (select rownum rn,ename,sal
from emp) a
where rownum<6;

    ROWNUM         RN ENAME             SAL
---------- ---------- ---------- ----------
         1          1 SMITH             800
         2          2 ALLEN            1600
         3          3 WARD             1250
         4          4 JONES            2975
         5          5 MARTIN           1250

只看6-10行，错误写法

select rownum,a.*
from (select rownum rn,ename,sal
      from emp) a
where rownum between 6 and 10;

no rows selected

只看6-10行，正确写法

select rownum,a.*
from (select rownum rn,ename,sal
        from emp) a
where a.rn between 6 and 10;

    ROWNUM         RN ENAME             SAL
---------- ---------- ---------- ----------
         1          6 BLAKE            2850
         2          7 CLARK            2450
         3          8 SCOTT            3000
         4          9 KING             5000
         5         10 TURNER           1500