子查询注意的10个问题

1.子查询语法中的小括号

2.子查询的书写风格

3.可以使用子查询的位置：where，select，having，from后面使用子查询

4.不可以使用子查询的位置：group by后面不可使用子查询

5.强调:from后面的子查询---

6.主查询和子查询可以不是同一张表

7.一般不在子查询中使用排序；但在Top-N分析问题中，必须对子查询排序

8.一般先执行子查询，再执行主查询；但相关子查询例外

9.单行子查询只能使用单行操作符；多行子查询只能使用多行操作符；

10.注意：子查询中是null值问题

1---如果没有小括号，语法是错误的

2----书写风格好，方便阅读

3----

单行子查询---仅仅返回一条记录

多行子查询---返回多行记录，2行及其以上

select empno,ename,sal,(select job from emp where empno=7839) 第四列

from emp;

select deptno,avg(sal)

from emp

group by deptno

having avg(sal) > (select max(sal)

   from emp

   where deptno=30);

-- 这having不能用where替代，有avg

select *

from(select empno,ename,sal from emp);

-- from 中加子查询的，用的很多

-- 括号里面，相当于一个新的结果集

4 -- group by 后不能使用子查询

-- 错误示例

select avg(sal)

from emp

group by (select deptno from emp);

5.from后面的子查询

select *

from (select empno,ename,sal,sal*12 from emp);

--通过12*sal计算出年薪

6.主查询和子查询不是一张表

-- 查询销售部员工

select * 

from emp

where deptno=(select deptno

from dept

where dname='SALES');

-- 多表查询的方式解决同样问题,相对上面，这里数据库访问一次，效率得根据实际数据量模式

select e.*

from emp e,dept d

where e.deptno=d.deptno and d.dname='SALES';

7.一般不在子查询中使用排序；但在Top-N分析问题中，必须对子查询排序

select rownum,empno,ename,sal

from emp

where rownum<3

order by sal desc;

select rownum,empno,ename,sal from emp order by sal desc;

行号永远按照默认的顺序生成

行号只能使用< <=不能使用> >= 

select rownum,empno,ename,sal

from (select * from emp order by sal desc)

where rownum<=3;

8.一般先执行子查询，再执行主查询；但相关子查询例外

-- 查询在所在部门，工资高于平均工资的员工

select empno,ename,sal,(select avg(sal) from emp where deptno=e.deptno) avgsal

from emp e

where sal > (select avg(sal) from emp where deptno=e.deptno);

9.单行子查询只能使用单行操作符

  多行子查询只能使用多行操作符

返回一条记录---单行子查询

返回2条及其以上----多行子查询

单行子查询示例1：

查询员工信息：

1.职位与7566员工一样

2.薪水大于7782的薪水

select *

from emp

where job = (select job from emp where empno=7566) and

      sal > (select sal from emp where empno=7782);

单行子查询示例2：

查询工资最低的员工信息

select *

from emp

where sal = (select min(sal) from emp);

单行查询示例-3：

查询最低工资大于20号部门最低工资的部门号和部门的最低工资

select deptno,min(sal)

from emp

group by deptno

having min(sal) > (select min(sal)

   from emp

where deptno=20);

多行操作符-

in--- 等于列表中的任何一个

any--- 和子查询返回的任意一个值比较

all--- 和子查询返回的所有值比较

多行操作符in：

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

select *

from emp

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

多表查询实现：

select e.*

from emp e,dept d

where e.deptno=d.deptno and (d.dname='SALES' or d.dname='ACCOUNTING');

tips:如若不加括号，按照and，or从前到后次序执行；

多行操作符any

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

select *

from emp

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

select *

from emp

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

多行操作符：all

示例：查询工资比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);

10.子查询中的null问题

单行子查询中的null问题----结果是空值

多行子查询中的null问题

示例：查询不是老板的员工

select *

from emp

where empno not in (select mgr from emp);

-- 因为子查询中有null，导致返回空数据

select *

from emp

where empno not in (select mgr from emp where mgr is not null);

*******************************************************

SQL> select *

  2  from emp

  3  where sal > (select sal

  4             from emp

  5                     where ename='SCOTT');

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM

---------- ---------- --------- ---------- -------------- ---------- ----------

 DEPTNO

----------

      7839 KING       PRESIDENT            17-11月-81           5000

        10

子查询：解决不能一步求解的查询

实例：查询比SCOTT的工资高的员工

select *

from emp

where sal > (select sal

                     from emp

                     where ename ='scott');

子查询概述

单行子查询与多行子查询

子查询

比SCOTT的工资高的员工信息

1、查询SCOTT的工资

select sa; from emp where ename='SCOTT';

2、比SCOTT工资高的员工信息

select * from emp where sal>3000;

子查询代码

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

子查询就是解决不能一步查询到的结果，用select嵌套。

select sal from emp where ename='scott';

select * from emp where >3000;

select * from emp 

where sal > (select sal

                     from emp

                     where ename='scott');

子查询概述

子查询使用

子查询类型

两个查询嵌套，把其中一个select当做条件
    select *
    from emp
    where sal > (select sal
                 from emp
                 where ename='SCOTT');

1. 查询工资比scott高的员工的工资;

select * 

from emp

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

>>子查询注意的10个问题

1.子查询语法中的小括号

2.子查询的书写风格

3.可以使用子查询的位置:where,select,having,from

(1) select 必须是单行子查询

select 工号,姓名,工资,(select 职位 from 员工表 where 工号='100') 

from 员工表

(2) having

select 部门号,ags(工资)

from 员工表

group by 部门号

having ags(工资) > (select max(工资)

from 员工表

where 部门号 = 30)

(3)from

select *

from(select 员工号,姓名,工资 

from 员工表)

4.不可以使用子查询的位置:group by

5.强调:from后面的子查询

select *

from (select 员工号,姓名,工资 月薪,工资*12 年薪 from 员工表)

6.主查询和子查询可以不是同一张表

--子查询

select *

from 员工表

where 部门号 = (select 部门号 

from 部门表 

where 部门名称 = '销售部')

--多表查询

select t1.* 

from 员工表 t1,部门表 t2

where t1.部门号 = t2.部门号 

  and t2.部门名称 = '销售部'

7.一般不在子查询中,使用排序;但在Top-N分析问题中,必须对子查询排序

**rownum 行号,伪列

*行号永远按照默认的顺序生成

*行号只能使用 <,<=;不能使用 >,>=

select rownum,员工号,姓名,工资 

from 员工表

(1)top-n 前三名

select rownum,员工号,姓名,工资

from(select *

from 员工表

order by 工资 desc)

where rownum <= 3

8.一般先执行子查询,再执行主查询;但相关子查询例外

(1)**相关子查询 (子查询使用了主查询字段)

select 员工号,姓名,工资,

(select avg(工资) from 员工表 where 部门号 = t1.部门号) 部门平均工资

from 员工表 t1

where 工资 > (select avg(工资)

  from 员工表 

  where 部门号 = t1.部门号)

    --

select 员工号,姓名,工资

from 员工表 t1

where 工资 > (select 部门号,avg(工资)

  from 员工表 

  group by 部门号

  having 部门号 = t1.部门号)

--

select t1.员工号,t1.姓名,t1.工资,t2.部门平均工资

from 员工表 t1,

(select 部门号,avg(工资) 部门平均工资

from 员工表

group by 部门号) t2

where t1.部门号 = t2.部门号

  and t1.工资 > t2.部门平均工资

9.单行子查询只能使用单行操作符;多行子查询只能使用多行操作符

多行操作符 in/any/all

--in

--any

select *

from 员工表

where 工资 > any(select 工资

     from 员工表

     where 部门号 = 30)

--等价

select *

from 员工表

where 工资 > (select min(工资)

  from 员工表

  where 部门号 = 30)   

--all

select *

from 员工表

where 工资 > all(select 工资

     from 员工表

     where 部门号 = 30)

--等价

select *

from 员工表

where 工资 > (select max(工资)

  from 员工表

  where 部门号 = 30)   

10.注意:子查询中是null值得问题

单行子查询null值问题

多行子查询null值问题

**not in

a not in (10,20,null)

a != 10 and a != 20 and a != null

all

select *

from 员工表

where 员工号 not in (select 老板号

from 员工表

where 老板号 not null)

子查询实例

例：查询工资比Scott高的员工 

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