oracle基础知识5_子查询

 

0 概念: 主查询之前的查询，作为结果服务于主查询。

            解决的问题: 不能一步求解

单行子查询: 返回的结果是一条记录

多行子查询: 返回的结果是两条和两条以上的记录

 

1 要注意的问题:

 1. 括号
 2. 合理的书写风格
 3. 可以主查询的where/ select/ from/ having后面放置子查询, 如果是在select后，那么这个查询必须是单行子查询
 4. 不可以在主查询的group by后面放置子查询
 5. 强调from后面的子查询
 6. 主查询和子查询可以不是同一张表，只要子查询返回的结果 主查询可以使用即可
 7. 一般不在子查询使用order by，但在Top-N分析问题中 必须使用order by
 8. 一般先执行子查询，再执行主查询；但相关子查询除外
 9. 单行子查询只能使用单行操作符 多行子查询只能使用多行操作符
 10. 子查询中null

 

2 案例:

 

2.1 在主查询select后跟子查询

select ename,sal,(select job from emp where empno=7839) as jobname   from emp;

 

ENAME	SAL	JOBNAME
Tom_ABCD	6000	PRESIDENT
SMITH	800	PRESIDENT
ALLEN	1600	PRESIDENT
WARD	1250	PRESIDENT

 

 

2.2 在主查询from后跟子查询

select *  from (select ename,sal,sal*12 annlsal from emp)

 

ENAME	SAL	ANNLSAL
Tom_ABCD	6000	72000
SMITH	800	9600
ALLEN	1600	19200
WARD	1250	15000

 

 

 

3 多行子查询用到的操作符:

操作符	含义
IN	等于列表中的任何一个
ANY	和子查询返回的任意一个数值比较
ALL	和子查询返回的所有数值比较

 

查询部门名称是SALES和ACCOUNTING的员工

select *
    from emp
    where deptno in (select deptno from dept where dname='SALES' or dname='ACCOUNTING');

 

查询工资比30号部门任意一个员工高的员工信息

select *
    from emp
    where sal > any(select sal from emp where deptno = 30);

 

查询工资比30号部门所有员工高的员工信息

 select *
    from emp
    where sal > all (select sal from emp where deptno=30);

等同于:

 select *
    from emp
    where sal > (select max(sal) from emp where deptno=30);

 

 

 在员工表中查询本身是领导的员工信息(是领导表示自己主键编码在领导编码mgr中出现)

    from emp
    where empno in (select mgr from emp);

 

 

 在员工表中查询本身不是领导的员工信息(子查询not in中需要排除null)

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
7521	WARD	SALESMAN	7698	22-2月 -81	1250	500	30
7654	MARTIN	SALESMAN	7698	28-9月 -81	1250	1400	30

 

子查询中的Null问题:

 

a) 所有和空值比较的函数都返回一个空值，

b) 只要子查询中包含一个空值，那么请不要使用not in操作，

c) 如果用的话，操作等同于 <>all

 

a not in(100,20,null) --->  等同于   a!= 100 and a!= 20 and a!=all

因此 not in的子查询中要排除空值，否则不会得到任何数据