oracle子查询和集合查询

子查询

子查询的作用：查询条件未知的事物

查询条件已知的问题：例如：查询工资为800的员工信息
查询条件未知的问题：例如：查询工资为20号部门平均工资的员工信息
一个条件未知的问题，可以分解为多个条件已知的问题

查询工资比WARD高的员工信息
第一：查询WARD的工资?
      select sal from emp where ename = 'WARD';

第二：查询工资比1250高的员工信息？
      select * from emp where sal > 1250;

子查询：
        select *
    from emp
    where sal > (
        select sal
        from emp
        where ename = 'WARD'
    );

查询部门名为'SALES'的员工信息（方式一：子查询）

第一：查询部门名为'SALES'的编号？
      select deptno from dept where dname = 'SALES';
第二：查询部门号为30的员工信息？
      select * from emp where deptno = 30;
子查询：
      select *
      from emp
      where deptno = (
      select deptno
      from dept
      where dname = 'SALES'
      );

子查询细节：
1）子查询与父查询可以针对同一张表
2）子查询与父查询可以针对不同张表
3) 子查询与父查询在传统参数时，数量要相同
4) 子查询与父查询在传统参数时，类型要相同
5) 子查询与父查询在传统参数时，含义要相同

查询部门名为'SALES'的员工信息（方式二：多表查询）
select emp.*
from dept,emp
where (dept.deptno=emp.deptno) and (dept.dname='SALES');

查询每个员工编号,姓名，部门名,工资等级（三表查询，这三张表并无外健关联）
select e.empno,e.ename,d.dname,s.grade
from emp e,dept d,salgrade s
where (e.deptno=d.deptno) and (e.sal between s.losal and s.hisal);

查询工资最低的员工信息（单行子查询，使用=号）
第一：查询出工资最低是多少？
      select min(sal) from emp;
第二：查询工资为800的员工信息？
      select * from emp where sal = 800;
子查询：
      select *
      from emp
      where sal = (
            select min(sal)
            from emp
          );

查询部门名为'ACCOUNTING'或'SALES'的员工信息（多行子查询，使用in关键字）    
第一：查询部门名为'ACCOUNTING'或'SALES'的部门编号？
      select deptno from dept where dname in ('ACCOUNTING','SALES');
第二：查询部门号为10或30号的员工信息？
      select * from emp where deptno in (10,30);
子查询：
      select *
      from emp
      where deptno in (
               select deptno
               from dept
                       where dname in ('ACCOUNTING','SALES')
                      );

查询工资比20号部门【任意any】一个员工工资【低<】的员工信息（多行子查询，使用any关键字）
第一：查询20号部门的所有工资？
      select sal from emp where deptno = 20;
第二：查询工资比(800,2975,3000,1100,3000)任意一个低的员工信息？
      select * from emp where sal < any (800,2975,3000,1100,3000);   
在oracle看来，<any就等于<集合中最大的那个值
子查询：
      select *
      from emp
      where sal <any (
            select sal
            from emp
            where deptno = 20
              );

查询工资比30号部门【所有all】员工【低<】的员工信息（多行子查询，使用all关键字）

第一：查询出30部门所有员工的工资？    
      select sal from emp where deptno = 30;
第二：查询工资比(1600,1250,1250,2850,1500,950)中所有的工资都低的员工信息？
      select * from emp where sal <all (1600,1250,1250,2850,1500,950);
子查询：
      select *
      from emp
      where sal <all (
            select sal
            from emp
            where deptno = 30
              );

注意：
单行函数：输入一个参数，输出一个结果
多行函数：扫描多个参数，输出一个结果

单行子查询：子查询只会返回一个结果，例如：800，父查询用=/<>/>=/<=这些符号来比较
多行子查询：子查询会返回多于一个结果，例如：30,20，父查询用in/any/all这些符号来比较

当多表查询，子查询同时能解决问题时，按如下优先方案选择：

多表查询-->子查询
注意：上述结果不是说多表查询可以替代子查询，某些情况下，只能用子查询解决，例如：oracle分页

集合查询

使用并集运算，查询20号部门或30号部门的员工信息
select * from emp where deptno = 20
union
select * from emp where deptno = 30;
注意：
union：二个集合中，如果都有相同的，取其一
union all：二个集合中，如果都有相同的，都取

使用交集运算[intersect]，查询工资在1000-2000和1500-2500之间的员工信息（方式一）
select * from emp where sal between 1000 and 2000
intersect
select * from emp where sal between 1500 and 2500;

用where行过滤，查询工资在1000-2000和1500-2500之间的员工信息（方式二）
select *
from emp
where (sal between 1000 and 2000) and (sal between 1500 and 2500);

使用差集运算[minus]，查询工资在1000-2000，但不在1500-2500之间的员工信息（方式一）
select * from emp where sal between 1000 and 2000
minus
select * from emp where sal between 1500 and 2500;

使用where行过滤，查询工资在1000-2000，但不在1500-2500之间的员工信息（方式二）
select *
from emp
where (sal between 1000 and 2000) and (sal not between 1500 and 2500);

集合查询的细节：
1）集合操作时，必须确保集合列数是相等
select empno,ename,sal,comm from emp where deptno = 20
union
select empno,ename,sal from emp where deptno = 30;错

2）集合操作时，必须确保集合列类型对应相同
select empno,ename,sal,comm from emp where deptno = 20
union
select empno,ename,sal,hiredate from emp where deptno = 30;错

3）A union B union C = C union B union A
select * from emp where deptno = 10
union
select * from emp where deptno = 20
union
select * from emp where deptno = 30;

4）当多个集合操作时，结果的列名由第一个集合列名决定
select empno "编号",ename "姓名",sal "薪水" from emp where deptno = 20
union
select empno,ename,sal from emp where deptno = 10;

当多表查询，子查询，集合查询都能完成同样任务时，按如下优化方案选择：
多表查询->子查询->集合查询